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

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


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



4. 修改 Stored Procedure，使用 WITH RECOMPILE



說明

WITH RECOMPILE

• 停用 Reuse Execution Plan 功能
• 每次執行 Stored Procedure，都要 Recompile SQL 陳述式 ，並執行 Parameter Sniffing，這勢必多耗用系統資源。

注意事項

• 修改 Stored Procedure 程式碼，牽扯版本問題。
• 需要與 SQL Developer 人員溝通討論。
• 停用 Reuse Execution Plan 功能
• 每次執行 Stored Procedure，都要 Recompile SQL 陳述式，這勢必多耗用系統資源。
• 以 Execute Frequency(執行頻率) 來考量
• 不適用於 every second(每秒) 都要執行 或 同時間會大量執行 等情境。
• 每次都重新編譯，將過度耗用系統資源。
• 適用於: 年報、季報、月報、周報、日報、小時報或數分鐘報等
• 如能因重新編譯 Stored Procedure，獲取更佳的執行效能。這筆支出，值得。

01. 建立使用 WITH RECOMPILE 的 Stored Procedure。

• 必須修改原來的程式碼。
• 在 Stored Procedure 內，額外再宣告 WITH RECOMPILE 來使用。

-- figure 41_ALTER Proc WITH RECOMPILE




02. 執行 使用 WITH RECOMPILE 的 Stored Procedure。

分別輸入 NULL 與 43659。

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

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

若非 每秒 都要執行 或 同時間會大量執行 的程式，能因 WITH RECOMPILE 重新編譯 Stored Procedure，獲取更佳的執行效能。這筆支出，是值得的。

-- figure 42_EXEC_Proc WITH RECOMPILE




03. 觀察 Execution Plan:


EXEC GetSalesList_WITH_RECOMPILE NULL

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

EXEC GetSalesList_WITH_RECOMPILE 43659

• 重新編譯 ，獲取執行效能更佳的 Execution Plan。
• 使用 Index Scan + Clustered Index Seek。
• Actual Rows: 12。
• Est Rows: 12，這是 Estimated Number of Rows。
• Reads: 464，這是 Logical Reads。

-- figure 43_Plan_Explorer





04. 進一步觀察 Execution Plan

Operation caused residual IO.
The actual number of rows read was 121,317, but the number of rows returned was 12.

雖然已經不用 Clustered Index Scan，Query Optimizer 判斷改用 Index Scan，但實際僅取回 12 rows，卻仍須讀取 121,317 rows，仍是耗用過多的系統資源。

讓我們繼續設計出更有效率的執行方式與 Execution Plan!

-- figure 44_Plan_Explorer

---

5. 修改 Stored Procedure，使用 OPTION RECOMPILE


說明

使用 OPTION (RECOMPILE)

• 針對特定的 SQL Statement，停用其 Reuse Execution Plan 功能。
• 僅 Recompile 指定的 SQL Statement，並且可使用到 Local Variable 的內容值。
• 每次執行 Stored Procedure，僅需 Recompile 指定的 SQL 陳述式，但仍有耗用過多資源的顧慮。

注意事項

• 修改 Stored Procedure 程式碼，牽扯版本問題。
• 需要與 SQL Developer 人員溝通討論。
• 每次執行 Stored Procedure，僅需 Recompile 指定的 SQL 陳述式，仍有耗用過多資源的顧慮。
• 以 Execute Frequency(執行頻率) 來考量
• 不適用於 every second(每秒) 都要執行 或 同時間會大量執行 等情境。
• 每次都重新編譯，將過度耗用系統資源。
• 適用於: 年報、季報、月報、周報、日報、小時報或數分鐘報等
• 如能因重新編譯 Stored Procedure，獲取更佳的執行效能。這筆支出，值得。
• 而且，OPTION (RECOMPILE) 可以解除 "Local Variables / OPTIMIZE FOR UNKNOWN" 所帶來的副作用，但 sp_recompile  與 WITH RECOMPILE 是無此功效。

01. 建立使用 OPTION RECOMPILE 的 Stored Procedure。

• 必須修改原來的程式碼。
• 在指定的 SQL Statement，額外再宣告 OPTION RECOMPILE 來使用。

-- figure 51_ALTER Proc OPTION RECOMPILE






02. 執行 使用 OPTION RECOMPILE 的 Stored Procedure。

分別輸入 NULL 與 43659。


當輸入 NULL:

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

當輸入 43659:

• 取回指定 SalesOrderID 的資料，回傳 12 rows。
• 經 Query Optimizer 執行優化後，使用 Index seek + Clustered Index Seek。
• 沒有使用到效率差的 Clustered Index Scan、Index Scan。
• 這是有提升執行效能的 Execution Plan。
• 與 WITH RECOMPILE 比較起來，OPTION RECOMPILE 有機會產更有效率的 Execution Plan。

若非 每秒 都要大量執行 或 同時間會大量執行 的程式，能因 OPTION RECOMPILE 重新編譯 Stored Procedure，獲取更佳的執行效能。這筆支出，是值得的。

-- figure 52_EXEC_Proc OPTION RECOMPILE




03. 觀察 Execution Plan:

EXEC GetSalesList_OPTION_RECOMPILE NULL

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

EXEC GetSalesList_OPTION_RECOMPILE 43659

• 僅 Recompile 指定的 SQL Statement，就可以獲得執行效能更佳的 Execution Plan。
• 使用 Index Seek + Clustered Index Seek。
• Actual Rows: 12。
• Est Rows: 12，這是 Estimated Number of Rows。
• Reads: 76，這是 Logical Reads。

-- figure 53_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

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與 動態 SQL)
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-dynamic-sql.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

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
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-option_14.html