搜尋本站文章

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