德瑞克：SQL Server 學習筆記: [SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR VALUE

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,