搜尋本站文章

2018-09-07

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


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


一談起 Dynamic SQL,許多人以為要使用 sp_executesql 才是 對的、效能好的,但其實這是囫圇吞棗後的謬誤。

  • sp_executesql,仍使用 Parameter Sniffing 機制來 Reuse Execution Plan
    • 有機會發生重複使用了效能 不佳 Execution Plan,陷入效能更糟的窘境。 
  • Dynamic SQL: EXEC,採取 Ad hoc Query 機制
    • 採取比對 hash 值來 重複使用 Execution Plan,為各個輸入 parameter 值,建立 效能佳 的 Execution Plan。

方法總比困難多

在本案例中:
  • 與 Dev Team 攜手合作,理解 Business 商務面的需求,改寫為 兩隻符合各自需求的 Stored Procedure,會是 執行效能最佳的解決方案!





繼續討論 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 運算子(5)



使用 UNION ALL 或 IF ELSE 改寫 Stored Procedure,確實可以讓 Query Optimizer 優化使用到更佳的索引。

但改寫、分解 條件式邏輯,這是項大工程,需要與 SQL Developer 進一步討論、驗證與測試。

其實還有其他解決方案,試試 Dynamic SQL: EXEC。


8. 改寫 Stored Procedure,使用 Dynamic SQL: EXEC



說明:

Dynamic SQL: EXEC

  • 採取 Ad hoc Query 機制來 重複使用 Execution Plan
    • 採取比對 hash 值
      • 相同,就能重複使用 Execution Plan。
      • 不同,產生新的 Execution Plan。
  • 為各個輸入 parameter 值,建立 效能佳 的 Execution Plan。
    • 但若每次輸入 parameter 值完全不會重複,則每次執行都會編譯,耗用系統資源。
  • 減少因 Parameter Sniffing 機制,重複使用了效能 不佳 Execution Plan,導致效能更糟的問題。 


注意事項:

  • 修改 Stored Procedure 程式碼,牽扯版本問題。
    • 需要與 SQL Developer 人員溝通討論。


01. 建立使用 Dynamic SQL: EXEC 的 Stored Procedure。

  • 必須修改原來的程式碼。
  • 宣告 字串變數 @sql 存放 SQL statement。
  • 傳入 parameter 
    • 可能需要轉型為 Character strings 類型。
    • 以及對 NULL 值的處理。
  • 基本上
    • 採用組合 SQL statement 方式。這是與原始程式雷同。
    • 沒有像 UNION ALL 或 IF ELSE 般分拆改寫。
  • 由於採取組 SQL statement 方式,程式碼可讀性略差,是會增加維護成本。
-- figure 91_ALTER Proc Dynamic SQL EXEC





02. 執行 使用 Dynamic SQL: EXEC 的 Stored Procedure。

分別輸入 NULL 與 43659。

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

  • 當輸入 43659: 
    • 取回指定 SalesOrderID 的資料,回傳 12 rows。
    • 經 Query Optimizer 執行優化後,使用 Clustered Index Seek
      • 沒有使用到效率差的 Clustered Index Scan。
    • 這是有提升執行效能的 Execution Plan。

進一步來檢視 Execution Plan。

-- figure 92_EXEC Proc Dynamic SQL EXEC





03. 觀察 Execution Plan:


當輸入參數是 NULL

  • 使用 Clustered Index Scan。
  • Actual Rows: 118,744。
  • Est Rows: 116,323,這是 Estimated Number of Rows。
  • Reads: 2,249,這是 Logical Reads。


-- figure 93_Plan_Explorer






04. 觀察 Execution Plan:


當輸入參數是 43659

  • 使用 Clustered Index Seek。
  • Actual Rows: 12。
  • Est Rows: 12,這是 Estimated Number of Rows。
  • Reads: 12,這是 Logical Reads。
  • 對比使用 UNION ALL,Reads 是 372,UNION ALL 使用更多的 Logical Reads。

-- figure 114_GetSalesList_DynamicSQL_EXEC 43659



-- figure 94_Plan_Explorer





05. 使用不同的輸入 parameter,分別執行。
  • 刻意設計,有 2 次使用相同的輸入 parameter。

-- figure 95_Execute many time with different parameter





06. 由 Cache ,觀察 Performance data

  • 確認 Reuse Execution Plan。
    • ExecutionCount: 2,就是先前刻意設計有 2 次使用相同的輸入 parameter。
  • ObjType: Adhoc

Dynamic SQL: EXEC

  • 採取比對 hash 值
    • 相同,重複使用 Execution Plan。
    • 不同,產生新的 Execution Plan。


-- figure 96_Adhoc_Execution_Plan




Dynamic SQL: EXEC 的 特色:

  • 為各個輸入 parameter 值,建立合適的 Execution Plan。
  • 採取 Ad hoc Query 機制來 重複使用 Execution Plan
    • 比對 hash 值,若不同時,就產生新的 Execution Plan。
      • 若每次輸入 parameter 值完全不會重複,則每次執行都會編譯,耗用系統資源。
  • 減少因 Parameter Sniffing 機制,重複使用效能不佳 Execution Plan,導致效能更糟的問題。 




9. 改寫 Stored Procedure,使用 Dynamic SQL: sp_executesql


一談起 Dynamic SQL,許多人都以為要使用 sp_executesql 才是 對的、效能好的,但其實這是囫圇吞棗後的謬誤。

若能正確理解 sp_executesql,方能避免因故重複使用效率差的 Execution Plan,導致 執行效能更糟的窘境。


說明

Dynamic SQL: sp_executesql
  • 使用 Parameter Sniffing 機制來 Reuse Execution Plan。
  • 必須面對 Parameter Sniffing 所帶來的 Side Effects。

注意事項

  • 修改 Stored Procedure 程式碼,牽扯版本問題。
    • 需要與 SQL Developer 人員溝通討論。

01. 建立使用 Dynamic SQL: sp_executesql 的 Stored Procedure。

  • 必須修改原來的程式碼。
  • 宣告 字串變數 @sql 存放 SQL statement,資料類型需要是 Unicode。
  • 傳入 parameter 
    • 可能需要轉型為 Character strings 類型。
    • 以及對 NULL 值的處理。
  • 基本上
    • 採用組合 SQL statement 方式。這是與原始程式雷同。
    • 沒有像 UNION ALL 或 IF ELSE 般分拆改寫。
  • 由於採取組 SQL statement 方式,程式碼可讀性略差,是會增加維護成本。

-- figure 101_ALTER Proc Dynamic SQL sp_executesql





02. 執行 使用 Dynamic SQL: sp_executesql 的 Stored Procedure。

分別輸入 NULL 與 43659。

  • 當輸入 NULL:
    • 取回全部資料,回傳 118,744 rows,使用 Clustered Index Scan
  • 當輸入 43659: 
    • 取回指定 SalesOrderID 的資料,回傳 12 rows,但卻仍是使用 Clustered Index Scan
    • Dynamic SQL: sp_executesql 使用 Parameter Sniffing 機制來 Re-Use Execution Plan。
    • 雖然節省了編譯 Execution Plan 的 CPU 資源,但卻因故誤用效能不佳的 Bad Execution Plan,導致耗用更多的系統資源!

Dynamic SQL: sp_executesql
必須面對 誤用效能不佳的 Bad Execution Plan,所帶來的 Side Effects。

-- figure 102_EXEC Proc Dynamic SQL sp_executesql




03. 觀察 Execution Plan

  • 輸入 NULL

在 Proc 內使用 Dynamic SQL: sp_executesql
  • Parameter Sniffing: Reuse Execution Plan
  • 僅第 1 次 有編譯產生 Execution Plan,之後都是共用同一份 Execution Plan。
  • 執行 10 次,即便是輸入不同的參數值,都是共用同一份 Execution Plan。
  • 節省 編譯 Execution Plan 的資源。


-- figure 103_Plan_Explorer





04. 觀察 Execution Plan

  • 輸入 43659


-- figure 104_Plan_Explorer





05. 執行 GetSalesList_DynamicSQL_sp_executesql
  • 執行 6 次。
  • 但第 2 與 3 次的輸入 parameter 都是相同。

-- figure 105_Parameter Sniffing reuse execution plan




06. 由 Cache,觀察 Performance Data


  • ExecutionCount: 6。
  • ObjType: Prepared。
  • 重複使用同一份 Execution Plan。


Dynamic SQL: sp_executesql

  • 採取 Parameter Sniffing 機制來 Reuse Execution Plan。
  • 必須面對 因故重複使用效率差的 Execution Plan,所帶來的 Side Effects。


-- figure 106_Prepared_Execution_Plan







10. 因應需求,分別建立 Stored Procedure


Use that big brain of yours to think your way out! Look for a new angle.

還有什麼方法,可以有效解決 因故重複使用效率差的 Execution Plan 呢?
記得這句話:方法總比困難多。


回顧一下



條件式:  WHERE (@SalesOrderID IS NULL OR sd.SalesOrderID = @SalesOrderID)
  • @SalesOrderID IS NULL: 
    • 當 @SalesOrderID 是 NULL 時,取回全部的資料,回傳 118,744 rows
  • sd.SalesOrderID = @SalesOrderID: 
    • 取回 指定的 SalesOrderID 的 資料,回傳 12 rows


以 Index 使用策略來看:

  • 這兩個條件式,以執行效能角度來看,應該是使用不同的 Index 存取方式,例如:Index Scan、Index Seek 等。




與 Dev Team 討論,理解 Business 商務面的需求:

  • 輸入參數是 NULL
    • 用於找回全部資料,並且額外顯示 資料表: Sales.SalesOrderDetail 的相關資訊。
    • 此為管理階層常用的報表。
  • 輸入參數是 特定值
    • 是客戶於登入系統時要使用,不需要 資料表: Sales.SalesOrderDetail 的資料。


兩個不同的需求,使用 OR 運算子整合在同一個 Stored Procedure 內。
經確認與討論後,決定改寫為兩隻符合各自需求的 Stored Procedure。


01. 建立 2 隻符合各自需求的 Stored Procedure


GetSalesList_Only

  • 輸入參數是 NULL
  • WHERE (@SalesOrderID IS NULL
  • 額外顯示 資料表: Sales.SalesOrderDetail 的相關資訊


GetSalesOrderID

  • 輸入參數是 特定值
  • WHERE sd.SalesOrderID = @SalesOrderID
  • 不需要 資料表: Sales.SalesOrderDetail 的資料: sd.*

-- figure 81_Create 2 Procs for different purposes





02. 執行 這兩 2 隻符合各自需求的 Stored Procedure

分別輸入 GetSalesList_Only NULL 與 GetSalesOrderID 43659。

GetSalesList_Only NULL:

  • 取回全部資料,回傳 118,744 rows,使用 Clustered Index Scan。


GetSalesOrderID 43659

  • 取回指定 SalesOrderID 的資料,回傳 12 rows。
  • 經 Query Optimizer 執行優化後,使用效率佳 Clustered Index Seek
  • 這是有提升執行效能的 Execution Plan。

-- figure 82_EXEC_2 Procs





03. 觀察 Execution Plan

當執行 GetSalesList_Only NULL:

  • 使用 Clustered Index Scan。
  • Actual Rows: 118,744。
  • Est Rows: 116,323,這是 Estimated Number of Rows。
  • Reads: 2,249,這是 Logical Reads。

相比先前的解決方案,與使用 輸入參數 NULL 的 Execution Plan 都雷同。

-- figure 83_Plan_Explorer




-- figure 84_Plan_Explorer



-- figure 85_Compute Scalar 1



-- figure 86_Compute Scalar 2



-- figure 87_LineTotal_Data





04. 觀察 Execution Plan

當執行 GetSalesOrderID 43659:

  • 使用 Clustered Index Seek
  • Actual Rows: 12。
  • Est Rows: 12,這是 Estimated Number of Rows。精準符合 Actual Rows。
  • Reads: 12,這是 Logical Reads。
  • 省去無需使用的 資料行
    • 只需 3 個資料行:c.PersonID, sh.Freight, cc.CardType
    • 省去使用 Compute Scalar 運算子。


相較先前的解決方案,這是 執行效能最佳 的 Execution Plan !

-- figure 88_Plan_Explorer









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

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

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

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

[Parameter Sniffing] Reuse bad execution plan and Recompile stored procedure, 重複使用效能差的 執行計劃 與 重新編譯 預存程序
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-reuse-bad-execution.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

Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-option_7.html

Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2

沒有留言:

張貼留言