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

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

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

1. 使用 EXEC ... WITH RECOMPILE

說明：

無須修改 Stored Procedure 程式碼
執行 stored procedure，搭配 WITH RECOMPILE

不方便。還需要帶入 User Parameter。

執行當下 Recompile (重新編譯) 此 stored procedure。

注意事項：

單次性的處理。

處理此次因 Parameter Sniffing 機制，導致重複使用到效能不佳的 Execution Plan。

但仍繼續使用 Parameter Sniffing 機制。

若 Input Parameter 仍差異性過大，仍會遭遇重複使用到效能不佳的 Execution Plan 。

01. 執行當下 Recompile (重新編譯) 此 stored procedure。

-- figure 11_EXEC_WITH RECOMPILE

02. 觀察 Execution Plan

-- Good: Index Scan + Clustered Index Seek, return 12 rows
EXEC dbo.GetSalesList 43659 WITH RECOMPILE

改用 Index Scan + Clustered Index Seek，Good Execution Plan。

-- figure 12_Good_Index Scan_Clustered Index Seek

03. 使用 Plan Explorer 來觀察

EXEC dbo.GetSalesList 43659 WITH RECOMPILE

改用 Index Scan + Clustered Index Seek，Good Execution Plan。
Actual Rows: 12。
Est Rows: 12，這是 Estimated Number of Rows。
Reads: 464，這是 Logical Reads。

與 "dbo.GetSalesList NULL" 對比起來，節省了大量的系統資源，Est Cost 是整體的 10%。

-- figure 13_Plan_Explorer

2. 使用 sp_recompile

說明：

無須修改 Stored Procedure 程式碼。
等到下次執行 stored procedure，才將 Recompile(重新編譯) 此 stored procedure。
使用到此 Table 的各項 SQL 物件：stored procedure, trigger, or user-defined function，都將 Recompile 。
比較起來，不先移除該 stored procedure 的 Execution Plan。

注意事項：

單次性的處理。

處理此次因 Parameter Sniffing 機制，導致重複使用到效能不佳的 Execution Plan。

但仍繼續使用 Parameter Sniffing 機制。

若 Input Parameter 仍差異性過大，仍會遭遇重複使用到效能不佳的 Execution Plan 。

01. 使用 sp_recompile 對 Table 後，再執行 Stored Procedure。

-- figure 21_sp_recompile

02. 使用 Plan Explorer 來觀察

-- Good: Index Scan + Clustered Index Seek, return 12 rows
EXEC sp_recompile 'dbo.GetSalesList'
GO
EXEC dbo.GetSalesList 43659
GO

改用 Index Scan + Clustered Index Seek，Good Execution Plan。
Actual Rows: 12。
Est Rows: 12，這是 Estimated Number of Rows。
Reads: 464，這是 Logical Reads。

與 "dbo.GetSalesList NULL" 對比起來，節省了大量的系統資源，Est Cost 是整體的 10%。

-- figure 22_Plan_Explorer

3. 修改 Stored Procedure，使用 "Local Variables / OPTIMIZE FOR UNKNOWN"

說明：

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

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

設定 Query Optimizer 僅使用 Average Distribution (平均分配) 機制來執行 cardinality estimation。

至於輸入什麼 parameter 值，回傳多少筆數等等，設定讓 Query Optimizer 都不要列入考量。
停用 Parameter Sniffing。

評估方式，例如：

使用 equality (等號)，則 Query Optimizer 僅使用 density vector 來執行 cardinality estimation，也就是： Rows * All density。
使用 Inequality (不相等)，則 Query Optimizer 連 density vector 也不用了，就直接設定資料列總數的 30% 來執行 cardinality estimation，也就是： Rows * 30% 。

注意事項：

停用 Parameter Sniffing 機制。

僅重複使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制產生的 Execution Plan。
堪用，非效能最佳化的 Execution Plan。

看似節省編譯 Execution Plan 的系統資源，卻陷入執行效能不佳的窘境，甚至耗損更多的系統資源!
必須修改 SQL 程式碼。
不適用於需求是高效能的系統。

01. 建立使用 Local Variable 的 Stored Procedure。

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

-- figure 31_ALTER Proc Local Variables_issue

02. 執行使用 Local Variable 的 Stored Procedure。

分別輸入 NULL 與 43659。

當輸入 NULL:

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

當輸入 43659:

取回指定 SalesOrderID 的資料，回傳 12 rows，但卻使用 Clustered Index Scan。

應該使用 Index Seek。

啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 判斷機制，停用 Parameter Sniffing 機制。
看似節省了編譯 Execution Plan 的 CPU 資源，卻陷入執行效能不佳的窘境，導致耗用更多的系統資源！

-- figure 32_EXEC_Local Variables_issue

03. 觀察: 這 2 份 Execution Plan。

因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 判斷機制

停用 Parameter Sniffing 機制。
兩份完全相同的 Execution Plan。

都是使用 Clustered Index Scan！

-- Clustered Index Scan, return 118,744 rows
EXEC GetSalesList_Local_Variable NULL

使用 Clustered Index Scan。
Actual Rows: 118,744。
Est Rows: 38,387，這是 Estimated Number of Rows，不精確。

因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制所計算出來的回傳筆數，與事實不符。

Reads: 2,249，這是 Logical Reads。
Est Cost 卻都顯示 50%，這是 Estimated Subtree Cost，不精確，不具備參考價值。

-- Bad: Clustered Index Scan, return 12 rows
EXEC GetSalesList_Local_Variable 43659

卻使用 Clustered Index Scan。
Actual Rows: 12。
Est Rows: 38,387，這是 Estimated Number of Rows，與 Actual Rows 差異極大，不精確。

因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制所計算出來的回傳筆數，與事實不符。

Reads: 1,562，這是 Logical Reads。
Est Cost 卻都顯示 50%，這是 Estimated Subtree Cost，不精確，不具備參考價值。

-- figure 33_Plan_Explorer

進一步觀察 Execution Plan

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

實際僅取回 12 rows，卻因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制，額外讀取 121,317 rows，浪費了大量的 IO 系統資源!

看似節省編譯 Execution Plan 的系統資源，卻陷入執行效能不佳的窘境，甚至耗損更多的系統資源!

-- figure 34_Plan_Explorer

身為 SQL Developer 人員

需要去了解資料特性、回傳筆數、執行頻率、Index、Statistics 等，選擇適合的因應之道，方能開發出高效能的 Stored Procedure。
一昧使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制，或許，節省了開發時程，但卻落得 Stored Procedure 執行效能不佳的窘境!

Sample Code

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