搜尋本站文章

2018-01-17

[SQL Server] Parameter Sniffing: Local Variable


延續前文:[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2


使用 "Local Variables / OPTIMIZE FOR UNKNOWN"


  • 如果無法識別出 常用 的 parameter 值,或是 Query Optimizer 使用此 parameter 值所 compile 的 Execution Plan 效率不佳(例如:由於 data skew 數據傾斜)。
  • SQL 開發人員 設定 Query Optimizer 僅使用 Average Distribution (平均分配) 機制來執行 cardinality estimation。
    • 至於輸入什麼 parameter,回傳多少筆數,讓 Query Optimizer 都不要列入考量,也不使用 Parameter Sniffing。
    • 在此次範例中,甚至連該有的 Missing Index 建議也不見了!
  • "Local Variables / OPTIMIZE FOR UNKNOWN" 這兩者是相同功能。
    • 先前,若要停用 Parameter Sniffing ,要對每一個 Input Parameter,額外再多宣告對應出 Local Parameter 來承接。
    • 在 SQL Server 2008 可以改用 OPTIMIZE FOR UNKNOWN,這是 SQL Statement 陳述式層級方式來 停用 Parameter Sniffing。


使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 建立 Reuse Execution Plan 是個好方法嗎?

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


評估方式
"Local Variables / OPTIMIZE FOR UNKNOWN" 

ConditionsQuery Optimizer only use
equality: =Rows * All density
inequality: >  ,  < , etc., Rows * 30%



當條件式是:


  • 使用 equality (等號),則 Query Optimizer 僅使用 density vector 來執行 Cardinality Estimation,也就是: Rows * All density
  • 使用 Inequality (不相等),則 Query Optimizer 連 density vector 也不用了,就直接設定資料列總數的 30% 來執行 Cardinality Estimation,也就是: Rows * 30%

試想:

  • 這種方式建立出來的 Execution Plan 會有好的效能嗎?
  • 這會是 效能最佳化 的 Execution Plan 嗎?


Compare Match and Not match Execution Plan


以 Local Variables / 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 建議 卻不見了!





Local Variable and Equality Operator


01. 建立 Stored Procedure: up_Parameter_Local_Variable


  • Use Input Parameter
  • Use Local Variable
  • Use Equality Operator

  • SQL 開發人員 為了要 停用 Parameter Sniffing,設定 Query Optimizer 僅使用 Average Distribution (平均分配) 機制來執行 Cardinality Estimation。
  • 至於輸入什麼 parameter,回傳多少筆數,讓 Query Optimizer 都不要列入考量。

要達成 停用 Parameter Sniffing 方法是:
  • 額外再宣告 Local Variable。
  • 承接來自於 Input Parameter 的值
  • 設定條件式僅使用 Local Variable。

-- figure 01_Local Variable




02. 設定 Input Parameter: @StateProvinceID= 32,執行此 Stored Procedure。


  • Input Parameter: StateProvinceID= 32
  • Use Local Parameter

  • Actual Number of Rows 是: 1。
  • Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] 
  • 但評估 Estimate Number of Rows 卻是: 265.054 ?

Local Variable 與 OPTIMIZE FOR UNKNOWN 兩者是一樣的功能,有著相同的 誤用問題!


-- figure 02_Local Variable_StateProvinceID_32





03. 由 SSMS 的 GUI 介面,觀察 Execution Plan

  • StatementEstRows="265.054"
  • OptimizeStatsUsage

在 OptimizeStatsUsage,可以看是 Statistics: [IX_Address_StateProvinceID]。

也就是說,Query Optimizer 是使用 Index: [IX_Address_StateProvinceID] 的 statistics (統計資料),執行 Query Optimization。

-- figure 03_OptimizeStatsUsage





04. Show Execution Plan XML,可以到相關的資訊。


  • 在 OptimizeStatsUsage,可以看是 Statistics: [IX_Address_StateProvinceID]。


-- figure 04_XML_OptimizeStatsUsage







Changing Parameter Values @StateProvinceID = 9

01. 執行 Stored Procedure,輸入 Parameter: @StateProvinceID= 9


  • Input Parameter: StateProvinceID= 9
  • Use Local Parameter


改採 Use Local Parameter方式來建立 Execution Plan 後,當 StateProvinceID=9,竟然 沒有 原本該有的 Missing Index 建議。

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

-- figure 11_Local Variable_StateProvinceID_9




後續的相關測試,請參考先前的文章:

[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2

"Local Variables / OPTIMIZE FOR UNKNOWN" 這兩者是相同功能,有著相同的 誤用問題!



Local Variable and Inequality Operator


Use Inequality Operators, 使用 不相等


01. 建立新的 Stored Procedure: up_Parameter_Local_Variable_Inequality,使用 Inequality Operators。


  • Use Local Parameter
  • Use < (Less Than) Operators


-- figure 21_inequality





02. 執行 Stored Procedure,輸入 Parameter: @StateProvinceID= 2


  • Number of Rows Read: 19614
  • Actual Number of Rows: 25
  • Estimated  Number of Rows: 5884.2
  • Estimated Number of Rows to be Read: 19614


Use Local Parameter,使用 < (Less Than)


  • return 25 rows
  • 但為什麼  EstimatedNumberofRows 卻是 5884.2 ?


為什麼是使用 EstimatedNumberofRows: 5884.2 來評估建立 Execution Plan 呢?


-- figure 31_inequality_StateProvinceID_2_Execution_Plan





03. 再度更換 Parameter: @StateProvinceID= 181


  • Number of Rows Read: 19614
  • Actual Number of Rows: 19583
  • Estimated  Number of Rows: 5884.2
  • Estimated Number of Rows to be Read: 19614


Use Local Parameter,使用 < (Less Than)


  • return 19583 rows
  • 但為什麼  EstimatedNumberofRows 卻是 5884.2 ?


怎麼又是使用 EstimatedNumberofRows: 5884.2 來評估建立 Execution Plan 呢?

-- figure  41_inequality_StateProvinceID_181_Execution_Plan




後續的相關測試,請參考先前的文章:

[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2

"Local Variables / OPTIMIZE FOR UNKNOWN" 這兩者是相同功能,有著相同的 誤用問題!




Sample Code

20180116_Parameter_Sniffing_Local_Variable
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing



參考資料

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

沒有留言:

張貼留言