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

在先前的文章裡，陸續介紹過 Parameter Sniffing 的基本概念。本文以 Case Study (案例分享) 方式來綜合討論。


Parameter Sniffing

• 優點：藉由重複使用 Execution Plan 的機制，來節省編譯 Execution Plan 資源 的耗損。
• 缺點：卻存在著誤用 Execution Plan，導致效能更糟的問題。
• 看似節省編譯 Execution Plan 的 CPU 資源，卻掉入效能不佳的窘境。

OR 運算子

• 可篩選多值，延展條件式的功能，十分彈性，但卻潛藏效能不佳的問題。

當兩者結合，遭遇到效能不佳的困境時，該如何解決呢?


本文彙整 10 種 Parameter Sniffing 的 因應之道：

1. 使用 EXEC ... WITH RECOMPILE
2. 使用 sp_recompile
3. 修改 Stored Procedure，使用 "Local Variables / OPTIMIZE FOR UNKNOWN"
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. 因應需求，分別建立 individual Stored Procedure

想知道之間的區別、應用時機 與 優缺點嗎？

---

本系列文章

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

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

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

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

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

---

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



01. 建立 Stored Procedure: dbo.GetSalesList


條件式:  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 等。
• 但透過 OR 運算子，整合在同一個 Stored Procedure，會遇到什麼效能上的難題呢？

-- figure 01_Parameter Sniffing with OR issue





02. 執行此 Stored Procedure: dbo.GetSalesList。

分別輸入 NULL 與 43659。

• 當輸入 NULL:
• 取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。
• 當輸入 43659: 
• 取回指定 SalesOrderID 的資料，回傳 12 rows，但卻仍是使用 Clustered Index Scan。
• 由於 Parameter Sniffing 發揮功效，重複使用 Execution Plan。
• 雖然節省了編譯 Execution Plan 的 CPU 資源，但卻因故誤用效能不佳的 Bad Execution Plan，導致耗用更多的系統資源！

-- figure 02_EXEC_Parameter Sniffing with OR issue





03. 觀察: 這 2 隻 Stored Procedure 的 Execution Plan。

• 由於 Parameter Sniffing 發揮功效，重複使用 Execution Plan。
• 都是使用 Clustered Index Scan！

-- figure 03_Clustered Index Scan





04. 綜合觀察


EXEC dbo.GetSalesList NULL

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

EXEC dbo.GetSalesList 43659

• 仍是使用 Clustered Index Scan。
• Actual Rows: 12。
• Est Rows: 116,322，這是 Estimated Number of Rows。
• Reads: 1,564，這是 Logical Reads。

這就是錯誤的重複使用 Execution Plan 後，所導致的效能低落問題。
看似節省編譯 Execution Plan 的 CPU 資源，卻掉入效能不佳的窘境。

下圖的 Execution Plan 是屬於 EXEC dbo.GetSalesList NULL。

-- figure 04_Plan_Explorer





05. 下圖的 Execution Plan 是屬於 EXEC dbo.GetSalesList 43659。

-- figure 05_Plan_Explorer

---

Sample Code

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

---

Reference

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