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

2018-08-22

[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 的因應之道：

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