延續前文：[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
04. 設定 Input Parameter: @StateProvinceID= 9，執行此 Stored Procedure。
@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 OPTION (RECOMPILE)
Lab 2: Query hints - OPTION (RECOMPILE) - multiple statements
01. 建立 Stored Procedure: up_Parameter_OPTION_RECOMPILE_MultiSQL
@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。
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。
| ExecutionCount | PlanRecompile |
| 2 | 1 |
| 2 | 3 |
-- figure 42_ExecutionCount_PlanRecompile_Get last performance statistics
09. 設定 Input Parameter: @StateProvinceID= 119，執行此 Stored Procedure。
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。
| ExecutionCount | PlanRecompile |
| 3 | 1 |
| 3 | 4 |
-- figure 52_ExecutionCount_PlanRecompile
Compare: without use OPTION (RECOMPILE)
觀察 效能 STATISTICS 統計資訊: CPU and Disk IO, etc.
Compare: without use OPTION (RECOMPILE)
觀察 效能 STATISTICS 統計資訊: CPU and Disk IO, etc.
- without use OPTION (RECOMPILE)
- 沒有用 OPTION (RECOMPILE)
| ExecutionCount | PlanRecompile |
| 3 | 1 |
| 3 | 1 |
-- 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
沒有留言:
張貼留言