搜尋本站文章

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

Use OPTION (RECOMPILE)

  •  Recompile 指定的 SQL Statement。
  • 可使用到 Local Variable 的值。
  • Statement-level recompilation behavior
    • Only statement-level recompilation, instead of the whole stored procedure.
  • Use the values of any local variables 





Lab 2: Query hints - OPTION (RECOMPILE) - multiple statements



01. 建立 Stored Procedure: up_Parameter_OPTION_RECOMPILE_MultiSQL

  • 包含 2 段的 SQL Statement。
  • 僅 1 段,使用 Query hints - OPTION (RECOMPILE)。


-- figure 31_Create Stored Procedure_up_Parameter_OPTION_RECOMPILE_MultiSQL




02. 設定 Input Parameter: @StateProvinceID= 32,執行此 Stored Procedure。
  • 1st 執行
  • Input Parameter: StateProvinceID= 32
  • Use Query hints - OPTION (RECOMPILE)
  • 有 2 段 SQL Statement,所以有 2 份 Execution Plan.
  • 僅 Recompile 指定的 SQL Statement。
  • 可使用到 Local Variable 的值。

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



03. 觀察 效能 STATISTICS  統計資訊: CPU and Disk IO, etc.
  • 有 2 段 SQL Statement,所以有 2 份 Execution Plan.
-- figure 33_Plan_Cache_Get last performance statistics



04. 觀察 Execution Plan
  • 僅 Recompile 指定的 SQL Statement。
  • 可使用到 Local Variable 的值。
  • 有 2 段 SQL Statement,所以有 2 份 Execution Plan.

-- figure 34_Review_Execution_Plan



05. 觀察 Execution Plan

  • Seek Predicates
  • ConstValue = 32

-- figure 35_Seek_Predicates_ConstValue




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

  • 僅 Recompile 指定的 SQL statement。

ExecutionCount PlanRecompile
1 1
1 2


-- figure 36_ExecutionCount_PlanRecompile_Get last performance statistics




07. 設定 Input Parameter: @StateProvinceID= 9,執行此 Stored Procedure。
  • 2nd 執行
  • Input Parameter: StateProvinceID= 9
  • Use Query hints - OPTION (RECOMPILE)
  • 有 2 段 SQL Statement,所以有 2 份 Execution Plan.
  • 僅 Recompile 指定的 SQL Statement。
  • 可使用到 Local Variable 的值。

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




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

  • 僅 Recompile 指定的 SQL Statement。

ExecutionCountPlanRecompile
21
23

-- figure 42_ExecutionCount_PlanRecompile_Get last performance statistics



09. 設定 Input Parameter: @StateProvinceID= 119,執行此 Stored Procedure。
  • 3rd 執行
  • Input Parameter: StateProvinceID= 119
  • Use Query hints - OPTION (RECOMPILE)
  • 有 2 段 SQL Statement,所以有 2 份 Execution Plan.
  • 僅 Recompile 指定的 SQL Statement。
  • 可使用到 Local Variable 的值。

-- figure 51_Use OPTION (RECOMPILE)_StateProvinceID= 119



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

  •  Recompile 指定的 SQL Statement。

ExecutionCountPlanRecompile
31
34




-- figure 52_ExecutionCount_PlanRecompile





Compare: without use OPTION (RECOMPILE)



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

  • without use OPTION (RECOMPILE)
  • 沒有用  OPTION (RECOMPILE)

ExecutionCountPlanRecompile
31
31



-- figure 61_ExecutionCount_PlanRecompile_Without_OPTION (RECOMPILE)




Sample Code

20180203_Parameter_Sniffing_OPTION_RECOMPILE



References

[SQL Server] Ad hoc Query: Local Variable and Without Local Variable