德瑞克：SQL Server 學習筆記: 2018/02

2018-02-26

[SQL Server] Fix: File activation failure. Msg 1813 Could not open new database. CREATE DATABASE is aborted.

FOR ATTACH_FORCE_REBUILD_LOG

undocumented。
自動重建 Transaction log file 檔案，與附加資料庫作業。
但有 Data Loss issue。

正常關閉 SQL Server 時，是可以使用 FOR ATTACH 或 FOR ATTACH_REBUILD_LOG 方式，附加回該資料庫與自動重建 Transaction log file。

若因意外中斷 SQL Server Service，資料庫非正常關閉。

可以嘗試使用 FOR ATTACH_FORCE_REBUILD_LOG 來自動重建 Transaction log file 檔案，與附加資料庫作業。
但必須面對 Data Loss issue!

縮小 Transaction log file ???

恣意用 Task Manager(工作管理員) 結束 SQL Server Service。
砍掉 Transaction log file 檔案。
再用 FOR ATTACH_FORCE_REBUILD_LOG 來自動重建 Transaction log file 檔案，與附加資料庫作業。
Data Loss issue 伴隨而來...

Fix: File activation failure. Msg 1813 Could not open new database. CREATE DATABASE is aborted.

Demo environment:

SQL Server 2017 Enterprise Edition.

01. 建立新資料庫: ForceRebuild。

-- figure 01_CREATE DATABASE

02. 建立 2 張資料表：tb_G1_Data, tb_G2_Data。

-- figure 02_CREATE TABLE

03. 各新增 1 筆資料列。

「Autocommit Transactions(自動認可交易)」，自動認可模式是預設的交易管理模式。
Autocommit mode is the default transaction management mode。

-- figure 03_Each table has one record

04. 使用「Explicit Transactions(外顯交易)」。

新增 3 筆資料列。
但是不執行 Commit Transaction，Without commit the transaction。

-- figure 11_Explicit transaction_Insert 3 rows

05. 執行 SHUTDOWN WITH NOWAIT，立即停止 SQL Server。

模擬意外中斷 SQL Server Service。
或是，用 Task Manager 中斷 SQL Server Service。

WITH NOWAIT 引數

不會對每個資料庫執行CHECKPOINT，立即關閉 SQL Server。
在嘗試終止所有使用者處理序之後，結束 SQL Server。
當重新啟動伺服器時，會執行未完成的交易之回復作業。

-- figure 12_SHUTDOWN WITH NOWAIT_without performing checkpoint

06. 模擬錯誤：

更改 Transaction log file 檔案名稱。
將該資料庫搬移到新的資料夾。

-- figure 13_ReName_Log_Move_DB_To_New_Location

07. 重新啟動 SQL Server。

08. 觀察資料庫狀態： RECOVERY_PENDING。

因為資料庫的實體檔案已經不存在原來的路徑。

-- figure 14_DB_State_RECOVERY_PENDING

09. 刪除受損的資料庫: ForceRebuild。

-- figure 15_DROP DATABASE

10. 使用 FOR ATTACH，嘗試附加回該資料庫。

FOR ATTACH 需要下列項目：

所有資料檔案 (MDF 和 NDF) 都必須是可用的。
如果存在多個記錄檔，它們必須全部都是可用的。
如果讀取/寫入資料庫有目前無法使用的單一記錄檔，且在進行附加作業之前，資料庫因為沒有使用者或開啟的交易而關閉，則 FOR ATTACH 會自動重建記錄檔並更新主要檔案。
反之，如果是唯讀資料庫，則會因為無法更新主要檔案而無法重建記錄。
因此，當您所附加的唯讀資料庫之記錄無法使用時，您必須在 FOR ATTACH 子句中提供記錄檔或檔案。
資料庫快照集中無法指定 FOR ATTACH。

使用 FOR ATTACH 失敗，因為 SQL Server 是使用非正常的方式來關閉資料庫。

File activation failure. The physical file name "C:\TSQLDB\ForceRebuild_log.ldf" may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, 
no checkpoint occurred to the database, or the database was read-only. 

This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'ForceRebuild'. CREATE DATABASE is aborted.

-- figure 21_FOR ATTACH_ERROR_Msg 1813

11. 使用 FOR ATTACH_REBUILD_LOG，嘗試附加回該資料庫。

FOR ATTACH_REBUILD_LOG 需要下列項目：

正常關閉資料庫。
所有資料檔案 (MDF 和 NDF) 都必須是可用的。
這項作業會中斷記錄備份鏈結。建議您在作業完成之後執行完整的資料庫備份。
一般而言，如果您要將一個含有大型記錄的讀/寫資料庫複製到其他伺服器，而該伺服器中，因為資料庫副本大部分用在讀取作業或只用在讀取作業，
所以所需的記錄空間比原始資料庫少，在這種情況下，通常就會使用 FOR ATTACH_REBUILD_LOG。
資料庫快照集中無法指定 FOR ATTACH_REBUILD_LOG。

使用 FOR ATTACH_REBUILD_LOG 失敗，因為 SQL Server 是使用非正常的方式來關閉資料庫。

-- figure 22_FOR ATTACH_REBUILD_LOG_ERROR_Msg 1813

use FOR ATTACH_FORCE_REBUILD_LOG

01. 使用 undocumented FOR ATTACH_FORCE_REBUILD_LOG ，嘗試附加回該資料庫。

FOR ATTACH_FORCE_REBUILD_LOG

undocumented
成功地重建 Transaction log file 檔案，完成附加資料庫作業。

File activation failure. The physical file name "C:\TSQLDB\ForceRebuild_log.ldf" may be incorrect.

New log file 'C:\SQLData\ForceRebuild_log.ldf' was created.

-- figure 31_undocumented FOR ATTACH_FORCE_REBUILD_LOG_ReBuild

02. 檢視 SQL Server Error Log：

New log file 'C:\SQLData\ForceRebuild_log.ldf' was created.
Parallel redo is started for database 'ForceRebuild' with worker pool size [2].
Parallel redo is shutdown for database 'ForceRebuild' with worker pool size [2].

-- figure 32_SQL Server Error Log

03. 查詢資料表：Data loss issue

原先 Autocommit Transactions 所新增 1 筆，都遺失不見了!

-- figure 33_Data_loss

使用 SQL Server 中文版本

檔案啟用錯誤。實體檔案名稱 "C:\TSQLDB\ForceRebuild_log.ldf" 可能不正確。
無法重建記錄，因為關閉資料庫時仍有開啟的交易/使用者、資料庫未發生檢查點，或資料庫是唯讀的。

如果手動刪除交易記錄檔，或因硬體或環境失敗而遺失交易記錄檔，就可能發生這種錯誤。
訊息 1813，層級 16，狀態 2，行 19
無法開啟新資料庫 'ForceRebuild'。CREATE DATABASE 已經中止。

01. FOR ATTACH

-- figure 11_FOR ATTACH_錯誤_訊息 1813

02. FOR ATTACH_REBUILD_LOG

-- figure 12_FOR ATTACH_REBUILD_LOG_錯誤_訊息 1813

03. FOR ATTACH_FORCE_REBUILD_LOG

檔案啟用錯誤。實體檔案名稱 "C:\TSQLDB\ForceRebuild_log.ldf" 可能不正確。
已建立新記錄檔 'C:\SQLData\ForceRebuild_log.ldf'。

-- figure 13_undocumented FOR ATTACH_FORCE_REBUILD_LOG

Sample Code

20180226_FOR_ATTACH_FORCE_REBUILD_LOG

https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

References

Database Detach and Attach (SQL Server)
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-detach-and-attach-sql-server

CREATE DATABASE (SQL Server Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-sql-server-transact-sql

交易記錄檔(transaction log)意外損毀，僅剩下資料檔案可用為例。錯誤訊息：訊息 1813，層級 16，狀態 2 ... 檔案啟動錯誤。實體檔案名稱 "*.ldf" 可能不正確。
http://sharedderrick.blogspot.tw/2010/02/transaction-log-1813-16-2-ldf.html

2018-02-11

[SQL Server] Compare "WITH RECOMPILE / sp_recompile" with "OPTION RECOMPILE"

延續前文：[SQL Server] Parameter Sniffing: OPTION (RECOMPILE)

別再誤以為：只要使用 sp_recompile 或 WITH RECOMPILE 就可以解決 Parameter Sniffing 問題。

在 Stored Procedure 內使用 Local Variables 或 OPTIMIZE FOR UNKNOWN 時：

就算是 sp_recompile 或 WITH RECOMPILE 也無法解決 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用到效能差的 Bad Execution Plan!

若因使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用到效能差的 Bad Execution Plan。

可以採用以下方式來因應，例如：

使用 OPTION (RECOMPILE)

僅 Recompile 指定的 SQL Statement，可使用到 Local Variable 的值。

移除使用 Local Variables
改用 Literal Values (常值)
改用 Dynamic SQL

許多朋友對以下問題，也是一知半解：

以 Stored Procedure 為例，"sp_recompile 或 WITH RECOMPILE" 與 "OPTION (RECOMPILE)" 兩者的差別在哪裡呢？

	Level	Description
sp_recompile or WITH RECOMPILE	Stored Procedure Level	Recompile 整個 Stored Procedure 但無法評估到 Local Variable 的值
OPTION (RECOMPILE)	Statement Level	僅 Recompile 指定的 SQL Statement 可使用到 Local Variable 的值

	Level	Description
sp_recompile or WITH RECOMPILE	Stored Procedure Level	Recompile whole stored procedure. Can't use the values of local variables.
OPTION (RECOMPILE)	Statement Level	Only statement-level recompilation, instead of the whole stored procedure. Use the values of any local variables.

1. Local Variable and "OPTION RECOMPILE" have used simultaneously

若因使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用到效能差的 Bad Execution Plan。

除了，移除使用 Local Variables，或是改用 Literal Values (常值)、Dynamic SQL 等，還有什麼方法呢？

可以使用 OPTION (RECOMPILE)

僅 Recompile 指定的 SQL Statement，可使用到 Local Variable 的值。

而 sp_recompile 與 WITH RECOMPILE，是無法解除 "Local Variables / OPTIMIZE FOR UNKNOWN" 所帶來的副作用!

01. 建立 Stored Procedure: up_Parameter_Local_Variable_OPTION_RECOMPILE

使用 Local Variable
使用 OPTION (RECOMPILE) - 僅 Recompile 指定的 SQL Statement，可使用到 Local Variable 的值

-- figure 01_Local Variable and OPTION RECOMPILE have used simultaneously

02. 設定 Input Parameter: @StateProvinceID= 32，執行此 Stored Procedure。

使用 Local Variable
使用 OPTION (RECOMPILE) - 僅 Recompile 指定的 SQL Statement，可使用到 Local Variable 的值
return 1 row, Index Seek: [IX_Address_StateProvinceID]
Good execution plan

-- figure 11_StateProvinceID_32

03. 觀察由 Plan Cache 內所記錄的 Execution Plan

1st: 將 Input Parameter 指定給 Local Variable
2nd: 使用 OPTION (RECOMPILE) - 僅 Recompile 指定的 SQL Statement，可使用到 Local Variable 的值

-- figure 12_Plan_Cache_Execution_Plan

04. 觀察效能 STATISTICS 統計資訊: CPU and Disk IO, etc.

-- figure 13_ExecutionCount_PlanRecompile

05. 若僅使用 Local Variable。觀察效能 STATISTICS 統計資訊: CPU and Disk IO, etc.

-- figure 14_Only_Use_Local Variable

06. 比較 Only use Local Variable vs. Local Variable and "OPTION RECOMPILE"

-- figure 21_ExecutionCount_PlanRecompile

若 Only use Local Variable，將由原本 LastLogicalReads: 4 ，大幅增加耗用到 218，差距： 54.5 倍。
這皆因 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用了 Bad Execution Plan。

2. Local Variable and "sp_recompile" have used simultaneously: invalid, like used Local Variable

請留意：
sp_recompile 與 WITH RECOMPILE，是無法解除 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用到效能差的 Bad Execution Plan!

01. 對有使用 Local Variables 的 Stored Procedure，執行 sp_recompile。

sp_recompile: Recompile a particular stored procedure
就先由 Plan Cache 移除該 stored procedure 的 Execution Plan
類似於 DBCC FREEPROCCACHE 由 Plan Cache 中移除指定的 Execution Plan

若因 Local Variables 用到效能差的 Bad Execution Plan，則 sp_recompile 是無法解除的!

應該使用 Index Seek，卻因 Local Variables 而誤用為 Index Scan。

-- figure 31_Local Variable and sp_recompile

02. 若使用 Local Variable。觀察效能 STATISTICS 統計資訊: CPU and Disk IO, etc.

即便使用了 sp_recompile，卻仍是使用到效能差的 Bad Execution Plan。
應該使用 Index Seek，卻因 Local Variables 而誤用為 Index Scan。
由原本 LastLogicalReads: 4 ，大幅增加耗用到 218，差距： 54.5 倍。
這皆因 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用效能差的 Bad Execution Plan。

-- figure 32_StateProvinceID_32

3. Local Variable and "EXEC ... WITH RECOMPILE" have used simultaneously: invalid, like used Local Variable

若因 Local Variables 用到效能差的 Bad Execution Plan，則 WITH RECOMPILE 是無法解除的!

01. 為了測試目的，先執行:

-- Clearing all plans from the plan cache
DBCC FREEPROCCACHE;

02. 對有使用 Local Variables 的 Stored Procedure，執行 10 次 Stored Procedure，並使用 WITH RECOMPILE。

執行 stored procedure，搭配 WITH RECOMPILE
不方便。還需要帶入 User Parameter。
執行當下 Recompile 此 stored procedure

若因 Local Variables 用到效能差的 Bad Execution Plan，則 WITH RECOMPILE 是無法解除的!
應該使用 Index Seek，卻因 Local Variables 而誤用為 Index Scan。

-- figure 41_Local Variable and EXEC WITH RECOMPILE

03. 觀察效能 STATISTICS 統計資訊: CPU, Disk IO

Without the Execution Plan
沒有存放 Execution Plan

即便使用了 WITH RECOMPILE，卻仍是使用到效能差的 Bad Execution Plan。
應該使用 Index Seek，卻因 Local Variables 而誤用為 Index Scan。
由原本 Logical Reads: 4 ，大幅增加耗用到 218，差距： 54.5 倍。
這皆因 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用效能差的 Bad Execution Plan。

-- figure 43_ExecutionCount_PlanRecompile

-- figure 44_Logical Reads: 218

4. Local Variable and "WITH RECOMPILE" have used simultaneously: invalid, like used Local Variable

若因 Local Variables 用到效能差的 Bad Execution Plan，則 WITH RECOMPILE 是無法解除的!

01. 為了測試目的，先執行:

-- Clearing all plans from the plan cache
DBCC FREEPROCCACHE;

02. 建立 Stored Procedure: up_Parameter_Local_Variable_WITH_RECOMPILE：

使用 Local Variable
使用 WITH_RECOMPILE

WITH RECOMPILE: recompile the stored procedures every time

停用 Reuse Execution Plan 功能
每次都執行 Recompile SQL 陳述式，並且執行 Parameter Sniffing。

-- figure 51_CREATE PROCEDURE dbo.up_Parameter_Local_Variable_WITH_RECOMPILE

03. 執行 10 次。

即便使用了 WITH RECOMPILE，卻仍是使用到效能差的 Bad Execution Plan。
應該使用 Index Seek，卻因 Local Variables 而誤用為 Index Scan。
由原本 LastLogicalReads: 4 ，大幅增加耗用到 218，差距： 54.5 倍。
這皆因 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用效能差的 Bad Execution Plan。

-- figure 52_Local Variable and WITH RECOMPILE

04. 觀察效能 STATISTICS 統計資訊: CPU, Disk IO

Without the Execution Plan
沒有存放 Execution Plan

-- figure 53_ExecutionCount_PlanRecompile

-- figure 54_Logical Reads: 218

Sample Code

20180211_Parameter_Sniffing_OPTION_RECOMPILE
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

References

[SQL Server] Parameter Sniffing: OPTION (RECOMPILE)
http://sharedderrick.blogspot.tw/2018/02/sql-server-parameter-sniffing-option.html

查詢效能不佳的疑難排解：基數估計
https://technet.microsoft.com/zh-tw/library/ms181034(v=sql.105).aspx

低查詢效能的疑難排解：常數摺疊和基數估計期間的運算式評估
https://technet.microsoft.com/zh-tw/library/ms175933(v=sql.105).aspx

2018-02-03

[SQL Server] Parameter Sniffing: OPTION (RECOMPILE)

延續前文：[SQL Server] Ad hoc Query: Local Variable and Without Local Variable

在 Stored Procedure 內，包含多段 SQL Statement，每段 SQL Statement 會有自己的 Execution Plan。

若僅某段 SQL Statement 有 Parameter Sniffing 問題時，是否可以僅 Recompile 指定的 SQL Statement ?

使用 Query hints - OPTION (RECOMPILE)，就是僅 Recompile 指定的 SQL Statement。
無須 Recompile 整個 Stored Procedure，節省 Recompile 所耗用的資源。

使用 Query hints - OPTION (RECOMPILE)

僅 Recompile 指定的 Statement。
可使用到 Local Variable 的值。

OPTION (RECOMPILE)

捨棄在執行查詢之後所產生的 Execution Plan，強制 Query Optimizer 在下次執行相同的查詢時，Recompile Execution Plan。
當 Recompile Execution Plan 時，RECOMPILE 查詢提示會使用查詢中任何 Local Variable 目前的值。如果查詢在 Stored Procedure 內，就會將目前的值傳給任何 Parameter。

當不必編譯整個 Stored Procedure，只需要 Recompile Stored Procedure 內的 subset of queries (部分查詢)時，OPTION (RECOMPILE) 是非常有用的替代方案。
如果 Stored Procedure 中的特定查詢固定使用非典型或暫存值，則可在這些查詢中使用 OPTION (RECOMPILE) 查詢提示來改善 Stored Procedure 效能。

由於只會 Recompile 使用查詢提示的查詢，而非完整 Stored Procedure，因此是執行 Statement-level 的 Recompile 行為。
除了使用 Stored Procedure 目前的 Parameter 值之外，RECOMPILE 查詢提示也會在您 Recompile 陳述式時，使用 Stored Procedure 內任何 Local Variable 的值。
OPTION (RECOMPILE) 功能是 Statement-level 的 Recompile 。當 Recompile 此 Stored Procedure 時，僅 Recompile 指定的 SQL Statement，而不是完整 Stored Procedure。

Parameter Sniffing: OPTION (RECOMPILE)

Lab 1: Query hints - OPTION (RECOMPILE)

01. 建立 Stored Procedure: up_Parameter_OPTION_RECOMPILE

使用 Query hints - OPTION (RECOMPILE)
僅 Recompile 指定的 SQL Statement。
可使用到 Local Variable 的值。

-- figure 01_Create Stored Procedure_up_Parameter_OPTION_RECOMPILE

02. 設定 Input Parameter: @StateProvinceID= 32，執行此 Stored Procedure。

Input Parameter: StateProvinceID= 32
Use Query hints - OPTION (RECOMPILE)
僅 Recompile 指定的 SQL Statement。
可使用到 Local Variable 的值。

-- figure 11_Use OPTION (RECOMPILE)_StateProvinceID= 32

03. 觀察效能 STATISTICS 統計資訊: CPU and Disk IO, etc.

-- figure 12_Get last performance statistics

Summary

@StateProvinceID= 32

Use Query hints - OPTION (RECOMPILE)
Efficient execution plan
return 1 row
Index Seek: [IX_Address_StateProvinceID]
ExecutionCount: 1
PlanRecompile: 2

04. 設定 Input Parameter: @StateProvinceID= 9，執行此 Stored Procedure。

Input Parameter: StateProvinceID= 9
Use Query hints - OPTION (RECOMPILE)
僅 Recompile 指定的 SQL Statement。
可使用到 Local Variable 的值。

-- figure 21_Use OPTION (RECOMPILE)_StateProvinceID= 9

05. 觀察效能 STATISTICS 統計資訊: CPU and Disk IO, etc.

-- figure 22_Get last performance statistics

Summary

@StateProvinceID= 9

Use Query hints - OPTION (RECOMPILE)
Efficient execution plan
Suggestion: Missing index
return: 4564 rows
Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
ExecutionCount: 2
PlanRecompile: 3