德瑞克：SQL Server 學習筆記: [Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (4)

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

使用 UNION ALL 或 IF ELSE 改寫 Stored Procedure

仍使用 Parameter Sniffing 機制，Reuse Execution Plan。
讓 Query Optimizer 優化使用到更佳的索引，例如： Clustered Index Seek。

使用 IF ELSE，經判斷後，僅執行滿足條件式的 SQL Statement。
但 UNION ALL 卻是執行全部的 SQL Statement，再組合起來回傳。

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

回顧

條件式: WHERE (@SalesOrderID IS NULL OR sd.SalesOrderID = @SalesOrderID)

以 Index 使用策略來看，已知

@SalesOrderID IS NULL，使用 Index Scan。
sd.SalesOrderID = @SalesOrderID，使用 Index Seek。

今藉由 OR 運算子，整合在同一個 Stored Procedure。

若硬是要找出一個 Index 可以符合這兩者的效能需求，這該怎麼辦呢?

燃起程式魂

跳脫框架，改寫條件式邏輯!

6. 改寫 Stored Procedure，使用 UNION ALL

此處使用改寫，是因為要對條件式進行改寫，而非先前僅是修改編譯參數。

說明：

使用 Parameter Sniffing 機制，Reuse Execution Plan。
UNION ALL

每次執行 Stored Procedure，都必須要分別執行全部 SQL Statement 的 WHERE 條件式之判斷。
再利用 UNION ALL 將結果組合起來回傳。這勢必多耗用系統資源。
Execution Plan 相對複雜，Subtree Cost 已失真。

對比 Local Variables，改寫為 UNION ALL：

讓 Query Optimizer 優化使用到更佳的索引，例如： Clustered Index Seek。
沒有使用到效率差的 Clustered Index Scan。

對比 WITH RECOMPILE 或 OPTION RECOMPILE，改寫為 UNION ALL：

使用 Parameter Sniffing 機制，Reuse Execution Plan，沒有額外重新編譯。
相形之下，會比 WITH RECOMPILE 或 OPTION RECOMPILE 更適合於每秒都要大量執行或同時間會大量執行的程式等情境。
但若有大量 UNION ALL 卻會耗用過多資源，需要進一步的測試與評估。

注意事項：

修改 Stored Procedure 程式碼，牽扯版本問題。

需要與 SQL Developer 人員溝通討論。

01. 建立使用 UNION ALL 的 Stored Procedure。

必須修改原來的程式碼。
將條件式 OR 運算子，改寫為多段 SELECT statement，再額外使用 UNION ALL 結合起來。
換句話說

輸入任何參數值，都必須要分別執行全部 SQL Statement 的 WHERE 條件式之判斷。
再利用 UNION ALL 將結果組合起來回傳。
增加執行成本。

若有多段 SELECT statement，會增加維護的複雜度。

-- figure 61_ALTER Proc UNION ALL

02. 執行使用 UNION ALL 的 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 62_EXEC_Proc UNION ALL

03. 觀察 Execution Plan:

複雜的 Execution Plan

使用 Concatenation 運算子，結合多段 SQL Statement 的 Execution Plan。

將輸入參數為 NULL 與輸入參數 43659 的兩份 Execution Plan，結合成一大份 Execution Plan。

換句話說，輸入任何參數值，都必須要分別執行全部 SQL Statement 的 WHERE 條件式之判斷。
再利用 UNION ALL 將結果組合起來回傳。

採用 Parameter Sniffing 機制

由於是各自獨立的 SQL statement，再使用 UNION ALL 結合再一起，因此，Query Optimizer 可以為各個 SQL statement 選擇更加合適的 Index。

但仍然採取 Parameter Sniffing 機制，重複使用 Execution Plan。
潛藏著誤用 Execution Plan，導致效能更糟的問題。

-- figure 63_Complex Execution Plan

04. 觀察 Execution Plan:

整體的 Estimated Subtree Cost是：5.03934。
已失真的 Subtree Cost

結合多份 SQL statement，所產生的 Execution Plan。

-- figure 64_Complex Execution Plan

05. 綜合觀察 1

當輸入參數是 NULL

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

-- figure 65_Plan_Explorer

05. 綜合觀察 2

當輸入參數是 43659

使用 Clustered Index Seek。
Actual Rows: 12。
Est Rows: 116,323，這是 Estimated Number of Rows。

評估失真。

Reads: 372，這是 Logical Reads。

-- figure 66_Plan_Explorer

7. 改寫 Stored Procedure，使用 IF ELSE

此處使用改寫，是因為要對條件式進行改寫，而非先前僅是修改編譯參數。

說明：

使用 Parameter Sniffing 機制，Reuse Execution Plan。
使用 IF ELSE

經判斷後，僅執行滿足條件式的 Transact-SQL 陳述式。

對比 Local Variables，改寫為 IF ELSE ：

讓 Query Optimizer 優化使用到更佳的索引，例如： Clustered Index Seek。
沒有使用到效率差的 Clustered Index Scan。

對比 WITH RECOMPILE 或 OPTION RECOMPILE，改寫為 IF ELSE：

使用 Parameter Sniffing 機制，Reuse Execution Plan，沒有額外重新編譯。
相形之下，會比 WITH RECOMPILE 或 OPTION RECOMPILE 更適合於每秒都要大量執行或同時間會大量執行的程式等情境。
但若 IF ELSE 是複雜邏輯運算，則應評估與測試。

對比 UNION ALL，改寫為 IF ELSE：

使用 IF ELSE 流程控制語言，經判斷後，僅執行滿足條件式的 Transact-SQL 陳述式；
但 UNION ALL 卻是執行全部的 Transact-SQL 陳述式，再組合起來回傳。
若有多段 SELECT statement 需要使用到 IF ELSE 結合，則會增加維護的複雜度。

說明：

修改 Stored Procedure 程式碼，牽扯版本問題。

需要與 SQL Developer 人員溝通討論。

01. 建立使用 IF ELSE 的 Stored Procedure。

必須修改原來的程式碼。

使用 IF ELSE 流程控制語言，改寫為：

如果 IF 關鍵字的條件獲得滿足，就會執行在 IF 關鍵字及其條件之後的 Transact-SQL 陳述式：布林運算式會傳回 TRUE。
選擇性的 ELSE 關鍵字導入了另一個在 IF 條件未獲滿足時所執行的 Transact-SQL 陳述式：布林運算式會傳回 FALSE。

與 UNION ALL 不同在於

使用 IF ELSE 流程控制語言，經判斷後，僅執行滿足條件式的 Transact-SQL 陳述式；
但 UNION ALL 卻是執行全部的 Transact-SQL 陳述式，再組合起來回傳。
若有多段 SELECT statement 需要使用到 UNION ALL 結合，則會增加維護的複雜度。

-- figure 71_ALTER Proc IF ELSE

02. 執行使用 IF ELSE 的 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 72_EXEC_Proc IF ELSE

03. 觀察 Execution Plan:

以 EXEC GetSalesList_IF 43659 為例

經判斷後，僅執行該 SQL Query 的 Execution Plan

與 UNION ALL 比較起來，使用 IF ELSE 的 Execution Plan 是簡潔的。

使用 IF ELSE 流程控制語言，經判斷後，僅執行滿足條件式的 Transact-SQL 陳述式。

採用 Parameter Sniffing 機制

使用 IF ELSE 流程控制語言僅執行滿足條件式的 Transact-SQL 陳述式，因此，Query Optimizer 可以為各個 SQL statement 選擇更加合適的 Index。

但仍然採取 Parameter Sniffing 機制，重複使用 Execution Plan。
潛藏著誤用 Execution Plan，導致效能更糟的問題。

當輸入參數是 43659

使用 Clustered Index Seek。
Actual Rows: 12。
Est Rows: 1，這是 Estimated Number of Rows。
Reads: 10，這是 Logical Reads。

對比使用 UNION ALL，Reads 是 372，UNION ALL 使用更多的 Logical Reads。

-- figure 73_Plan_Explorer

使用到 Compute Scalar 運算子，這是因為有使用 Computed Column(計算資料行): LineTotal。

[[AdventureWorks2014].[Sales].[SalesOrderDetail].LineTotal] = Scalar Operator(isnull(CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPrice] as [sd].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPriceDiscount] as [sd].[UnitPriceDiscount],0))*CONVERT_IMPLICIT(numeric(5,0),[AdventureWorks2014].[Sales].[SalesOrderDetail].[OrderQty] as [sd].[OrderQty],0),(0.000000)))

-- figure 111_Computed Column_LineTotal