2018-01-07

[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN


延續前文：[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR VALUE


已知道 "OPTIMIZE FOR VALUE" 是：

  • 指示 Query Optimizer 使用 事先自訂 的 特定值 來建立 Reuse 的 Execution Plan，節省 recompile 所耗用的系統資源。
  • 減輕因 "先建續用" 的 Reuse Execution Plan 機制，所產生的效能不佳問題。

UNKNOWN 一詞，在 Oxford Dictionaries 解釋：

  • NOUN: An unknown person or thing.
  • ADJECTIVE: Not known or familiar.

那 "OPTIMIZE FOR UNKNOWN" 是什麼？


OPTIMIZE FOR UNKNOWN


OPTIMIZE FOR UNKNOWN 是

  • 指示 Query Optimizer 使用 Statistical Data  (而非 Initial Value)，來取代 Query Optimization 階段的  Local Variable 之值。 這包含：使用 Forced Parameterization (強制參數化) 所建立的參數。
  • Query Optimizer 不使用 Parameter Sniffing，不使用 Histogram(長條圖)，僅使用 Density Vector(密度向量) 來執行評估 Cardinality Estimation(CE, 基數預估)。
  • Query Optimizer 使用 Cardinality Estimation，來產生 最佳化的 Execution Plan。
    • 但 OPTIMIZE FOR UNKNOWN 卻可能讓 Query Optimizer 產生效能不佳的 Execution Plan。
  • 建立 Reuse 的 Execution Plan，節省 recompile 所耗用的系統資源。

"UNKNOWN" 字面上的意義容易理解，但 "OPTIMIZE FOR UNKNOWN" 實際運作的方式，是需要進一步討論的。


 但使用 OPTIMIZE FOR UNKNOWN 建立 Reuse Execution Plan 是個好方法嗎？

  • 有提高執行效能？
  • 如何評估 Index 呢？


 Compare

以 OPTIMIZE FOR UNKNOWN 為例:


若 @StateProvinceID= 32


  • Not match Execution Plan, Bad
  • 理想是 Logical Reads: 6，但卻因故誤用，而耗用到 218，差距 36.33 倍。
  • 原本是 CPU Time(sec): 0.000448000，但卻因故誤用，而耗用到 0.001645000，差距 3.67 倍。


若 @StateProvinceID= 9


  • 算是 Match Execution Plan
  • 但該有的 Missing Index 建議 卻不見了!


將後續文章進一步討論 OPTIMIZE FOR UNKNOWN。




OPTIMIZE FOR VALUE/UNKNOWN

總括來說，使用 "OPTIMIZE FOR VALUE/UNKNOWN" 是

  • 指示 Query Optimizer 在執行 Compile 與 Query Optimization 期間，使用 "OPTIMIZE FOR  VALUE/UNKNOWN"  抵銷 Query Optimizer 預設的 Parameter Detection 行為。
  • Query Optimizer 不 使用預設的 Parameter Detection 方式來執行 Query Optimization，改用 "OPTIMIZE FOR VALUE/UNKNOWN"  所指示的方式來執行 Query Optimization。
  • 只有在 Query Optimization 階段才使用這些方法的值，來建立 Execution Plan，而不是在 Query Execution 階段。



OPTION OPTIMIZE FOR UNKNOWN


01. 建立 Stored Procedure: up_Parameter_OPTION_OPTIMIZE_FOR_UNKNOWN

  • Use OPTION (OPTIMIZE FOR UNKNOWN);
  • 指示 Query Optimizer 使用 Statistical Data  (而非 Initial Value)，來取代 Query Optimization 階段的  Local Variable 之值。 這包含：使用 Forced Parameterization (強制參數化) 所建立的參數。
  • Query Optimizer 不使用 Parameter Sniffing，不使用 Histogram(長條圖)，僅使用 Density Vector(密度向量) 來執行評估 Cardinality Estimation(CE, 基數預估)。
  • Query Optimizer 使用 Cardinality Estimation，來產生 最佳化的 Execution Plan。
    • 但 OPTIMIZE FOR UNKNOWN 卻可能讓 Query Optimizer 產生效能不佳的 Execution Plan。
  • 建立 Reuse 的 Execution Plan，節省 recompile 所耗用的系統資源。

-- figure 01_Create_Proc_OPTION (OPTIMIZE FOR UNKNOWN)




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

  • Input Parameter: StateProvinceID= 32
  • Use OPTION (OPTIMIZE FOR UNKNOWN);

沒有使用到 Good Execution Plan

-- figure 02_StateProvinceID_32_OPTION (OPTIMIZE FOR UNKNOWN)





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

  • 使用 XQuery 查詢 Execution Plan 的 Estimate Number of Rows。

-- figure 03_Get last performance statistics





  • 實際回傳的資料列筆數是: 1。
  • 但評估 Estimate Number of Rows 卻是: 265.054 ?

-- figure 04_Get last performance statistics



04. 由 SSMS 的 GUI 介面，觀察 Execution Plan 的 Estimate Number of Rows 也是: 265.054 ?

-- figure 05_Execution_Plan_EstimatedNumberofRows




-- figure 0502_Actual_Estimate_Rows





  • Number of Rows Read: 19614
  • Actual Number of Rows: 1
  • Estimated  Number of Rows: 265.054
  • Estimated Number of Rows to be Read: 19614



-- figure 0503_Actual_Number_Estimated_Rows




  • Actual Number of Rows: 1
  • Number of Rows Read: 19614
  • Estimated  Number of Rows: 265.054



05. 由 SSMS 的 GUI 介面，觀察 Execution Plan: [Parameter List] and [Estimate Number of Rows]

  • [Parameter List], [Parameter Runtime Value]: (32)
  • [Estimate Number of Rows]: 265.054

-- figure 06_Execution_Plan_EstimatedNumberofRows_[OptimizeStatsUsage]_Statistics






Summary


Use OPTION (OPTIMIZE FOR UNKNOWN);

  • Only use statistical data for during query optimization.
  • Only use statistical data instead of the initial value.
  • Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.
  • Middle value, mediocre for Execution Plan

@StateProvinceID= 32

  • Not match Execution Plan, Bad
  • retun 1 row
  • Index Scan - [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
  • LastElapsedTime(sec): 0.006785000
  • LastCPUTime(sec)]: 0.001645000
  • LastRows: 1
  • EstimatedNumberofRows: 265.054
  • Last_logical_reads: 218
  • [OptimizeStatsUsage], [Statistics]: [IX_Address_StateProvinceID]




Changing Parameter Values @StateProvinceID = 9



01. 執行 Stored Procedure，輸入 Parameter: @StateProvinceID= 9
  • Input Parameter: StateProvinceID= 9
  • Use OPTION (OPTIMIZE FOR UNKNOWN);

改採 OPTION (OPTIMIZE FOR UNKNOWN) 方式來建立 Execution Plan 後，當 StateProvinceID=9，竟然 沒有 原本該有的 Missing Index 建議。

-- figure 11_StateProvinceID_9_OPTION (OPTIMIZE FOR UNKNOWN)





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

  • 實際回傳的資料列筆數是: 4,564。
  • 但評估 Estimate Number of Rows 卻是: 265.054 ?

-- figure 12_Get last performance statistics





03. 由 SSMS 的 GUI 介面，觀察 Execution Plan 的 Estimate Number of Rows 也是: 265.054 ?

-- figure 13_Execution_Plan_EstimatedNumberofRows




-- figure 1302_Actual_Estimate_Rows





  • Number of Rows Read: 19614
  • Actual Number of Rows: 4564
  • Estimated  Number of Rows: 265.054
  • Estimated Number of Rows to be Read: 19614



-- figure 1303_Actual_Number_Estimated_Rows





  • Actual Number of Rows: 4564
  • Number of Rows Read: 19614
  • Estimated  Number of Rows: 265.054



04. 由 SSMS 的 GUI 介面，觀察 Execution Plan: [Parameter List] and [Estimate Number of Rows]

  • [Parameter List], [Parameter Runtime Value]: (9)
  • [Estimate Number of Rows]: 265.054

-- figure 14_Execution_Plan_EstimatedNumberofRows_[OptimizeStatsUsage]_Statistics





Summary

Use OPTION (OPTIMIZE FOR UNKNOWN);

  • Only use statistical data for during query optimization.
  • Only use statistical data instead of the initial value.
  • Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.

@StateProvinceID= 9

  • Match Execution Plan, But unexpected issue: without missing index 
  • retun 4564 row
  • Index Scan - [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
  • LastElapsedTime(sec): 0.118544000
  • LastCPUTime(sec)]: 0.006451000
  • LastRows: 4564
  • EstimatedNumberofRows: 265.054
  • Last_logical_reads: 218
  • [OptimizeStatsUsage], [Statistics]: [IX_Address_StateProvinceID]



Sample Code

20180106_Parameter_Sniffing_OPTION_OPTIMIZE_FOR_UNKNOWN
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing




參考資料

[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR VALUE
http://sharedderrick.blogspot.tw/2018/01/sql-server-parameter-sniffing-option.html

於

沒有留言:

張貼留言