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
於

沒有留言:

張貼留言