德瑞克：SQL Server 學習筆記: [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 的因應之道：

使用 EXEC ... WITH RECOMPILE
使用 sp_recompile
修改 Stored Procedure，使用 Local Variables
修改 Stored Procedure，使用 WITH RECOMPILE
修改 Stored Procedure，使用 OPTION RECOMPILE
改寫 Stored Procedure，使用 UNION ALL
改寫 Stored Procedure，使用 IF ELSE
改寫 Stored Procedure，使用 Dynamic SQL: EXEC
改寫 Stored Procedure，使用 Dynamic SQL: sp_executesql
因應需求，分別建立 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)