2018-08-23

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)



延續前一篇文章：[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)


繼續討論 Parameter Sniffing 的 因應之道：

  1. 使用 EXEC ... WITH RECOMPILE
  2. 使用 sp_recompile
  3. 修改 Stored Procedure，使用 Local Variables
  4. 修改 Stored Procedure，使用 WITH RECOMPILE
  5. 修改 Stored Procedure，使用 OPTION RECOMPILE
  6. 改寫 Stored Procedure，使用 UNION ALL
  7. 改寫 Stored Procedure，使用 IF ELSE
  8. 改寫 Stored Procedure，使用 Dynamic SQL: EXEC
  9. 改寫 Stored Procedure，使用 Dynamic SQL: sp_executesql
  10. 因應需求，分別建立 Stored Procedure



Parameter Sniffing with OR Operator issue,
當 Parameter Sniffing 遇上 OR 運算子(2)




1. 使用 EXEC ... WITH RECOMPILE


說明：
  • 無須修改 Stored Procedure 程式碼
  • 執行 stored procedure，搭配 WITH RECOMPILE
    • 不方便。還需要 帶入 User Parameter。
  • 執行當下 Recompile (重新編譯) 此 stored procedure。

注意事項：
  • 單次性的處理。
    • 處理此次 因 Parameter Sniffing 機制，導致重複使用到 效能不佳 的 Execution Plan。
  • 但仍繼續使用 Parameter Sniffing 機制。
    • 若 Input Parameter 仍差異性過大，仍會遭遇重複使用到 效能不佳 的 Execution Plan 。

01. 執行當下 Recompile (重新編譯) 此 stored procedure。

-- figure 11_EXEC_WITH RECOMPILE






02. 觀察 Execution Plan

-- Good: Index Scan + Clustered Index Seek, return 12 rows
EXEC dbo.GetSalesList 43659 WITH RECOMPILE

  • 改用 Index Scan + Clustered Index Seek，Good Execution Plan。


-- figure 12_Good_Index Scan_Clustered Index Seek





03. 使用 Plan Explorer 來觀察


EXEC dbo.GetSalesList 43659 WITH RECOMPILE
  • 改用 Index Scan + Clustered Index Seek，Good Execution Plan。
  • Actual Rows: 12。
  • Est Rows: 12，這是 Estimated Number of Rows。
  • Reads: 464，這是 Logical Reads。

與 "dbo.GetSalesList NULL" 對比起來，節省了大量的系統資源，Est Cost 是整體的 10%。

-- figure 13_Plan_Explorer





2. 使用 sp_recompile


說明：
  • 無須修改 Stored Procedure 程式碼。
  • 等到下次執行 stored procedure，才將 Recompile(重新編譯) 此 stored procedure。
  • 使用到此 Table 的各項 SQL 物件：stored procedure, trigger, or user-defined function，都將 Recompile 。
  • 比較起來，不先移除 該 stored procedure 的 Execution Plan。

注意事項：
  • 單次性的處理。
    • 處理此次 因 Parameter Sniffing 機制，導致重複使用到 效能不佳 的 Execution Plan。
  • 但仍繼續使用 Parameter Sniffing 機制。
    • 若 Input Parameter 仍差異性過大，仍會遭遇重複使用到 效能不佳 的 Execution Plan 。

01. 使用 sp_recompile 對 Table 後，再執行 Stored Procedure。


-- figure 21_sp_recompile





02. 使用 Plan Explorer 來觀察

-- Good: Index Scan + Clustered Index Seek, return 12 rows
EXEC sp_recompile 'dbo.GetSalesList'
GO
EXEC dbo.GetSalesList 43659
GO


  • 改用 Index Scan + Clustered Index Seek，Good Execution Plan。
  • Actual Rows: 12。
  • Est Rows: 12，這是 Estimated Number of Rows。
  • Reads: 464，這是 Logical Reads。


與 "dbo.GetSalesList NULL" 對比起來，節省了大量的系統資源，Est Cost 是整體的 10%。

-- figure 22_Plan_Explorer





3. 修改 Stored Procedure，使用 "Local Variables / OPTIMIZE FOR UNKNOWN"


說明：
  • 修改 Stored Procedure 程式碼，牽扯版本問題。
    • 需要與 SQL Developer 人員溝通討論。
  • 設定 Query Optimizer 僅使用 Average Distribution (平均分配) 機制來執行 cardinality estimation。
    • 至於輸入什麼 parameter 值，回傳多少筆數等等，設定讓 Query Optimizer 都不要列入考量。
    • 停用 Parameter Sniffing。
  • 評估方式，例如：
    • 使用 equality (等號)，則 Query Optimizer 僅使用 density vector 來執行 cardinality estimation，也就是： Rows * All density。
    • 使用 Inequality (不相等)，則 Query Optimizer 連 density vector 也不用了，就直接設定資料列總數的 30% 來執行 cardinality estimation，也就是： Rows * 30% 。

注意事項：
    • 停用 Parameter Sniffing 機制。
      • 僅重複使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制產生的 Execution Plan。
      • 堪用，非效能最佳化的 Execution Plan。
    • 看似節省編譯 Execution Plan 的系統資源，卻陷入 執行效能不佳的窘境，甚至耗損更多的 系統資源!
    • 必須修改 SQL 程式碼。
    • 不適用於需求是 高效能 的系統。


01. 建立使用 Local Variable 的 Stored Procedure。

  • 必須修改原來的程式碼。
  • 在 Stored Procedure 內，額外再宣告 Local Variable 來使用。

-- figure 31_ALTER Proc Local Variables_issue






02. 執行 使用 Local Variable 的 Stored Procedure。

分別輸入 NULL 與 43659。


  • 當輸入 NULL:
    • 取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。


  • 當輸入 43659: 
    • 取回指定 SalesOrderID 的資料，回傳 12 rows，但卻使用 Clustered Index Scan。
      • 應該使用 Index Seek。
    • 啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 判斷機制，停用 Parameter Sniffing 機制。
    • 看似節省了編譯 Execution Plan 的 CPU 資源，卻陷入 執行效能不佳的窘境，導致耗用更多的系統資源！


-- figure 32_EXEC_Local Variables_issue





03. 觀察: 這 2 份 Execution Plan。


  • 因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 判斷機制
    • 停用 Parameter Sniffing 機制。
    • 兩份完全相同的 Execution Plan。
  • 都是使用  Clustered Index Scan！


-- Clustered Index Scan, return 118,744 rows
EXEC GetSalesList_Local_Variable NULL


  • 使用 Clustered Index Scan。
  • Actual Rows: 118,744。
  • Est Rows: 38,387，這是 Estimated Number of Rows，不精確。
    • 因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制所計算出來的回傳筆數，與事實不符。
  • Reads: 2,249，這是 Logical Reads。
  • Est Cost 卻都顯示 50%，這是 Estimated Subtree Cost，不精確，不具備參考價值。


-- Bad: Clustered Index Scan, return 12 rows
EXEC GetSalesList_Local_Variable 43659

  • 卻使用 Clustered Index Scan。
  • Actual Rows: 12。
  • Est Rows: 38,387，這是 Estimated Number of Rows，與 Actual Rows 差異極大，不精確。
    • 因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制所計算出來的回傳筆數，與事實不符。
  • Reads: 1,562，這是 Logical Reads。
  • Est Cost 卻都顯示 50%，這是 Estimated Subtree Cost，不精確，不具備參考價值。

-- figure 33_Plan_Explorer




進一步觀察 Execution Plan

Operation caused residual IO.
The actual number of rows read was 121,317, but the number of rows returned was 12.

實際僅取回 12 rows，卻因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制，額外讀取 121,317 rows，浪費了大量的 IO 系統資源!

看似節省編譯 Execution Plan 的系統資源，卻陷入 執行效能不佳的窘境，甚至耗損更多的 系統資源!

-- figure 34_Plan_Explorer




身為 SQL Developer 人員


  • 需要去 了解 資料特性、回傳筆數、執行頻率、Index、Statistics 等，選擇適合的因應之道，方能開發出 高效能 的 Stored Procedure。
  • 一昧使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制，或許，節省了開發時程，但卻落得 Stored Procedure 執行效能不佳的窘境!



Sample Code

20180821_Parameter_Sniffing_with_OR_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing



Reference

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or.html

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與 動態 SQL)
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-dynamic-sql.html

Compare "WITH RECOMPILE / sp_recompile" with "OPTION RECOMPILE"
http://sharedderrick.blogspot.com/2018/02/sql-server-compare-with-recompile-with.html

Parameter Sniffing: Local Variable
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-local.html
於

沒有留言:

張貼留言