德瑞克：SQL Server 學習筆記

2018-01-05

[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR VALUE

延續前文：[SQL Server] Parameter Sniffing: sp_recompile, WITH RECOMPILE

預設 Stored Procedure 是採取 reuse 重複使用 Execution Plan 的方式來節省 Recompile 耗用的系統資源。

但 "先建續用" 的 Reuse Execution Plan 機制，卻會有誤用問題，使得執行效能更糟，耗用更多的系統資源!

除了，再執行 Recompile 或停用 Reuse Execution Plan 功能，還能做什麼？

試試 OPTION OPTIMIZE FOR VALUE

使用特定值來建立 Reuse 的 Execution Plan，節省 recompile 所耗用的系統資源。
採取 80/20法則，評估與指定變數值，讓多數 SQL 查詢式使用到 Good Execution Plan。
減輕因 "先建續用" 的 Reuse Execution Plan 機制，所產生的效能不佳問題。

使用 OPTION OPTIMIZE FOR VALUE

Feature

用於指示 query optimizer 在執行 Optimizing Queries 查詢最佳化時，使用特定的值來作為 local variable 的值。
OPTIMIZE FOR 可以抵銷 query optimizer 的預設 parameter detection behavior。
只有在 query optimization 期間才使用這些值， query execution 期間則不使用這些值。
SQL 陳述式層級的 Hints。

Advantage

Use a particular value to to create a reusable Execution Plan, and save overhead of recompilation.
使用特定值來建立 Reuse 的 Execution Plan，節省 recompile 所耗用的系統資源。
減輕因 "先用續用" 的 Reuse Execution Plan 機制，所產生的效能問題。

Guideline

可採取 80/20法則，讓程式設計師事先自訂常用的變數值來編譯為最佳化 Execution Plan。
評估與指定變數值，讓多數 SQL 查詢式使用到 Good Execution Plan。

Compare Match and Not match Execution Plan

以 Query Hint: OPTION (OPTIMIZE FOR (@StateProvinceID=9)) 為例:

若 @StateProvinceID= 32

Not match Execution Plan, Bad
Logical Reads: 6，但卻有可能因故誤用，而耗用到 218，差距 36.33 倍。
CPU Time(sec): 0.000448000，但卻有可能因故誤用，而耗用到 0.004405000，差距 9.83 倍。

若 @StateProvinceID= 9

Match Execution Plan, Good
使用特定值來建立 Reuse 的 Execution Plan，節省 recompile 所耗用的系統資源。

語法 OPTION OPTIMIZE FOR VALUE

OPTIMIZE FOR 可以抵銷 query optimizer 的預設 parameter detection behavior。
當您建立 Plan Guide(計畫指南)時，也可以使用 OPTIMIZE FOR。

OPTION OPTIMIZE FOR ( @variable_name = literal_constant)

指示查詢最佳化工具在查詢進行編譯和最佳化時，使用特定的本機變數值。
只有在查詢最佳化期間，才使用這個值，在查詢執行期間，不使用這個值。

@variable_name

這是查詢所用之本機變數的名稱
您可以指派這個本機變數的值來搭配使用 OPTIMIZE FOR 查詢提示。

iteral_constant

是要指派的 literal(常值) 常數值 @variable_name OPTIMIZE FOR 查詢提示搭配使用。
literal_constant 用只在查詢最佳化期間，而不是值 @variable_name 查詢執行期間。
literal_constant 可以是任何 SQL Server可以表示為常值常數的系統資料類型。
資料型別 literal_constant 必須是隱含地轉換成資料類型， @variable_name 查詢中的參考。

只有在 query optimization 期間才使用這些值， query execution 期間則不使用這些值。

Parameter Sniffing: OPTION OPTIMIZE FOR VALUE

01. 建立 Stored Procedure: up_Parameter_OPTION_OPTIMIZE_FOR_VALUE

使用 OPTION (OPTIMIZE FOR (@StateProvinceID=9))

-- figure 01_Create_Proc_OPTION OPTIMIZE FOR VALUE

02. 執行 Stored Procedure，但輸入 Parameter: @StateProvinceID= 32

Input Paramerer: StateProvinceID= 32
Use OPTION (OPTIMIZE FOR (@StateProvinceID=9));

-- figure 02_Test_StateProvinceID_32

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

-- figure 03_Get last performance statistics

Summary

@StateProvinceID = 32

Bad Execution Plan - Not match
return: 1 row, Index Scan:

[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]

LastElapsedTime(sec): 0.013304000
LastCPUTime(sec)]: 0.004405000
Last_logical_reads: 218

Changing Parameter Values @StateProvinceID = 9

04. 執行 Stored Procedure，輸入 Parameter: @StateProvinceID= 9

-- figure 11_Test_StateProvinceID_9

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

-- figure 12_Get last performance statistics

Summary

@StateProvinceID = 9

Good Execution Plan - Match
return: 4564 row, Index Scan:

[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]

LastElapsedTime(sec): 0.074935000
LastCPUTime(sec)]: 0.004415000
Last_logical_reads: 218

Good Execution Plan

(1) StateProvinceID=32,

return: 1 row,
Index Seek: [IX_Address_StateProvinceID]
LastCPUTime(sec): 0.000448000
Last_logical_reads: 6

(2) StateProvinceID=9,

return: 4564 rows,
Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
Suggestion: Missing index
LastCPUTime(sec): 0.004337000
Last_logical_reads: 218

(3) StateProvinceID=41,

return: 1 row,
Index Seek: [IX_Address_StateProvinceID]
LastCPUTime(sec): 0.000666000
Last_logical_reads: 7

Sample Code

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

參考資料

[SQL Server] Parameter Sniffing: Pros and Cons, 參數探測
http://sharedderrick.blogspot.tw/2017/12/sql-server-parameter-sniffing-pros-and.html

[SQL Server] Parameter Sniffing: sp_recompile, WITH RECOMPILE
http://sharedderrick.blogspot.tw/2018/01/sql-server-parameter-sniffing.html

2018-01-03

[SQL Server] Parameter Sniffing: sp_recompile, WITH RECOMPILE

延續前文：[SQL Server] Parameter Sniffing: Pros and Cons, 參數探測

遇到 Stored Procedure 執行速度忽快忽慢，該怎麼辦?
除了，重新啟動 SQL Server，還能做什麼？

依據，是否要 Reuse Execution Plan，可以分成 2 類來討論：

手動執行 Recompile、停用 Reuse Execution Plan 功能。
有 4 種方式來達成。

A. Manual recompile: 手動執行 Recompile

01. sp_recompile: stored procedure, trigger, or user-defined function

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

02. sp_recompile: table or view

sp_recompile: Recompile a particular table
要等到下次執行 stored procedure，才將 Recompile 此 stored procedure。
使用到此 Table 的各項 SQL 物件：stored procedure, trigger, or user-defined function，都將 Recompile 。
與 sp_recompile: stored procedure, trigger, or user-defined function
比較起來，不先移除該 stored procedure 的 Execution Plan。

03. WITH RECOMPILE Option with EXECUTE statement

執行 stored procedure，搭配 WITH RECOMPILE

不方便。還需要帶入 User Parameter。

執行當下 Recompile 此 stored procedure。

B. Disable Reuse Execution Plan: 停用 Reuse Execution Plan 功能

04. WITH RECOMPILE Option with CREATE PROCEDURE

WITH RECOMPILE: recompile the stored procedures every time
每次都執行 Recompile SQL 陳述式，並且執行 Parameter Sniffing，每次都產生可能更有效率的 Execution Plan。

附註

為何會說：... 可能更有效率的 Execution Plan ?

這是因為，假若 Statistics 統計值不正確，也可能會影響到 Query Optimizer 所判斷要使用的 Index。

Lab 1: sp_recompile: Recompile a particular stored procedure

sp_recompile: stored procedure, trigger, or user-defined function

先由 Plan Cache 移除該 stored procedure 的 Execution Plan。
類似於 DBCC FREEPROCCACHE 由 Plan Cache 中移除指定的 Execution Plan

Good Execution Plan

StateProvinceID=32, return: 1 row,

Index Seek: [IX_Address_StateProvinceID]

StateProvinceID=9, return: 4564 rows,

Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]

StateProvinceID=41, return: 1 row,

Index Seek: [IX_Address_StateProvinceID]

01. 第 1 次執行 StateProvinceID = 32，並執行 10 次

系統 compile 此 Execution Plan，使用 @StateProvinceID= 32。

-- 01_Run 10 times_First time use StateProvinceID = 32

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

Good Execution Plan
return: 1 row, Index Seek: [IX_Address_StateProvinceID]
last_logical_reads: 6
[ExecutionCount]: 10
[PlanRecompile]: 1

-- 02_Get last performance statistics for cached query

03. sp_recompile Recompile the particular stored procedure

對 stored procedure 執行 sp_recompile。
Object 'dbo.up_Parameter_Sniffing' was successfully marked for recompilation.

-- 03_sp_recompile Recompile the particular stored procedure

04. Have removed the Execution Plan of the particular Stored Procedure from the plan cache.

已經移除此 Stored Procedure 的 Execution Plan.

-- 04_Have removed the Execution Plan from the plan cache

05. 執行 StateProvinceID = 9

Plan cache 已經沒有 up_Parameter_Sniffing 的 Execution Plan。
系統 compile 此 Execution Plan，使用 @StateProvinceID= 9。

-- 11_2nd Changing Parameter Values StateProvinceID = 9

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

Good Execution Plan
return: 4564 rows, Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
Efficient execution plan
Suggestion: Missing index
Last_logical_reads: 218
[ExecutionCount]: 1
[PlanRecompile]: 1

-- 12_Get last performance statistics for cached query

Lab 2: sp_recompile: Recompile a particular table

01. sp_recompile: Recompile a particular table

sp_recompile: Recompile 指定的 table
如果物件是 table 或 view，要等到下次執行 stored procedure，才將 Recompile 此 stored procedure。
使用到此 Table 的各項 SQL 物件：stored procedure, trigger, or user-defined function，都將 Recompile 。

-- 21_sp_recompile Recompile the table

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

該 stored procedure 的 Execution Plan 仍然存在。
要等到下次執行 stored procedure，才將 Recompile 此 stored procedure。

-- 22_Get last performance statistics for cached query

03. 執行 StateProvinceID = 32，並執行 5 次

系統 compile 此 Execution Plan，使用 @StateProvinceID= 32。
要等到下次執行 stored procedure，才將 Recompile 此 stored procedure。

-- 31_Run 5 times

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

Good Execution Plan
return: 1 row, Index Seek: [IX_Address_StateProvinceID]
last_logical_reads: 6
[ExecutionCount]: 6
[PlanRecompile]: 2

先前基底是 [ExecutionCount]: 1 與 [PlanRecompile]: 1。
已知 sp_recompile: Recompile 指定的 table，等到執行 stored procedure時，才將 Recompile 此 stored procedure。

再度觀察，已經變更： [ExecutionCount]: 6 與 [PlanRecompile]: 2。

-- 32_Get last performance statistics for cached query

Remove All Cache

-- 41_Remove_Cache

Lab 3: WITH RECOMPILE Option with EXECUTE statement

01. WITH RECOMPILE Option with EXECUTE statement。執行 StateProvinceID = 32。

執行 stored procedure，搭配 WITH RECOMPILE

不方便。還需要帶入 User Parameter。

執行當下 Recompile 此 stored procedure

觀察 Execution Plan

Good Execution Plan
return: 1 row, Index Seek: [IX_Address_StateProvinceID]

-- 42_WITH RECOMPILE Option with EXECUTE statement

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

Without the Execution Plan
沒有存放 Execution Plan

-- 43_Without the Execution Plan

03. WITH RECOMPILE Option with EXECUTE statement。執行 StateProvinceID = 9。

執行 stored procedure，搭配 WITH RECOMPILE

不方便。還需要帶入 User Parameter。

執行當下 Recompile 此 stored procedure

觀察 Execution Plan

Good Execution Plan
return: 4564 rows, Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]

-- 44_WITH RECOMPILE Option with EXECUTE statement

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

Without the Execution Plan
沒有存放 Execution Plan

-- 45_Without the Execution Plan

05. 刻意不輸入 user parameter，發生執行錯誤：

Error: only WITH RECOMPILE but without user Parameter
Msg 201, Level 16, State 4, Procedure dbo.up_Parameter_Sniffing, Line 0 [Batch Start Line 151]
Procedure or function 'up_Parameter_Sniffing' expects parameter '@StateProvinceID', which was not supplied.

-- 46_Error WITH RECOMPILE but without user Parameter