延續前一篇文章:[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (4)
一談起 Dynamic SQL,許多人以為要使用 sp_executesql 才是 對的、效能好的,但其實這是囫圇吞棗後的謬誤。
- sp_executesql,仍使用 Parameter Sniffing 機制來 Reuse Execution Plan
- 有機會發生重複使用了效能 不佳 Execution Plan,陷入效能更糟的窘境。
- Dynamic SQL: EXEC,採取 Ad hoc Query 機制
- 採取比對 hash 值來 重複使用 Execution Plan,為各個輸入 parameter 值,建立 效能佳 的 Execution Plan。
方法總比困難多
在本案例中:
- 與 Dev Team 攜手合作,理解 Business 商務面的需求,改寫為 兩隻符合各自需求的 Stored Procedure,會是 執行效能最佳的解決方案!
繼續討論 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 運算子(5)
使用 UNION ALL 或 IF ELSE 改寫 Stored Procedure,確實可以讓 Query Optimizer 優化使用到更佳的索引。
但改寫、分解 條件式邏輯,這是項大工程,需要與 SQL Developer 進一步討論、驗證與測試。
其實還有其他解決方案,試試 Dynamic SQL: EXEC。
8. 改寫 Stored Procedure,使用 Dynamic SQL: EXEC
說明:
Dynamic SQL: EXEC
- 採取 Ad hoc Query 機制來 重複使用 Execution Plan
- 採取比對 hash 值
- 相同,就能重複使用 Execution Plan。
- 不同,產生新的 Execution Plan。
- 為各個輸入 parameter 值,建立 效能佳 的 Execution Plan。
- 但若每次輸入 parameter 值完全不會重複,則每次執行都會編譯,耗用系統資源。
- 減少因 Parameter Sniffing 機制,重複使用了效能 不佳 Execution Plan,導致效能更糟的問題。
注意事項:
- 修改 Stored Procedure 程式碼,牽扯版本問題。
- 需要與 SQL Developer 人員溝通討論。
01. 建立使用 Dynamic SQL: EXEC 的 Stored Procedure。
- 必須修改原來的程式碼。
- 宣告 字串變數 @sql 存放 SQL statement。
- 傳入 parameter
- 可能需要轉型為 Character strings 類型。
- 以及對 NULL 值的處理。
- 基本上
- 採用組合 SQL statement 方式。這是與原始程式雷同。
- 沒有像 UNION ALL 或 IF ELSE 般分拆改寫。
- 由於採取組 SQL statement 方式,程式碼可讀性略差,是會增加維護成本。
02. 執行 使用 Dynamic SQL: EXEC 的 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。
03. 觀察 Execution Plan:
當輸入參數是 NULL
- 使用 Clustered Index Scan。
- Actual Rows: 118,744。
- Est Rows: 116,323,這是 Estimated Number of Rows。
- Reads: 2,249,這是 Logical Reads。
-- figure 93_Plan_Explorer
04. 觀察 Execution Plan:
當輸入參數是 43659
- 使用 Clustered Index Seek。
- Actual Rows: 12。
- Est Rows: 12,這是 Estimated Number of Rows。
- Reads: 12,這是 Logical Reads。
- 對比使用 UNION ALL,Reads 是 372,UNION ALL 使用更多的 Logical Reads。
-- figure 114_GetSalesList_DynamicSQL_EXEC 43659
-- figure 94_Plan_Explorer
05. 使用不同的輸入 parameter,分別執行。
- 刻意設計,有 2 次使用相同的輸入 parameter。
-- figure 95_Execute many time with different parameter
06. 由 Cache ,觀察 Performance data
- 確認 Reuse Execution Plan。
- ExecutionCount: 2,就是先前刻意設計有 2 次使用相同的輸入 parameter。
- ObjType: Adhoc
Dynamic SQL: EXEC
- 採取比對 hash 值
- 相同,重複使用 Execution Plan。
- 不同,產生新的 Execution Plan。
-- figure 96_Adhoc_Execution_Plan
Dynamic SQL: EXEC 的 特色:
- 為各個輸入 parameter 值,建立合適的 Execution Plan。
- 採取 Ad hoc Query 機制來 重複使用 Execution Plan
- 比對 hash 值,若不同時,就產生新的 Execution Plan。
- 若每次輸入 parameter 值完全不會重複,則每次執行都會編譯,耗用系統資源。
- 減少因 Parameter Sniffing 機制,重複使用效能不佳 Execution Plan,導致效能更糟的問題。
9. 改寫 Stored Procedure,使用 Dynamic SQL: sp_executesql
一談起 Dynamic SQL,許多人都以為要使用 sp_executesql 才是 對的、效能好的,但其實這是囫圇吞棗後的謬誤。
若能正確理解 sp_executesql,方能避免因故重複使用效率差的 Execution Plan,導致 執行效能更糟的窘境。
說明
Dynamic SQL: sp_executesql
- 使用 Parameter Sniffing 機制來 Reuse Execution Plan。
- 必須面對 Parameter Sniffing 所帶來的 Side Effects。
注意事項
- 修改 Stored Procedure 程式碼,牽扯版本問題。
- 需要與 SQL Developer 人員溝通討論。
01. 建立使用 Dynamic SQL: sp_executesql 的 Stored Procedure。
- 必須修改原來的程式碼。
- 宣告 字串變數 @sql 存放 SQL statement,資料類型需要是 Unicode。
- 傳入 parameter
- 可能需要轉型為 Character strings 類型。
- 以及對 NULL 值的處理。
- 基本上
- 採用組合 SQL statement 方式。這是與原始程式雷同。
- 沒有像 UNION ALL 或 IF ELSE 般分拆改寫。
- 由於採取組 SQL statement 方式,程式碼可讀性略差,是會增加維護成本。
-- figure 101_ALTER Proc Dynamic SQL sp_executesql
02. 執行 使用 Dynamic SQL: sp_executesql 的 Stored Procedure。
分別輸入 NULL 與 43659。
- 當輸入 NULL:
- 取回全部資料,回傳 118,744 rows,使用 Clustered Index Scan。
- 當輸入 43659:
- 取回指定 SalesOrderID 的資料,回傳 12 rows,但卻仍是使用 Clustered Index Scan。
- Dynamic SQL: sp_executesql 使用 Parameter Sniffing 機制來 Re-Use Execution Plan。
- 雖然節省了編譯 Execution Plan 的 CPU 資源,但卻因故誤用效能不佳的 Bad Execution Plan,導致耗用更多的系統資源!
Dynamic SQL: sp_executesql
必須面對 誤用效能不佳的 Bad Execution Plan,所帶來的 Side Effects。
必須面對 誤用效能不佳的 Bad Execution Plan,所帶來的 Side Effects。
-- figure 102_EXEC Proc Dynamic SQL sp_executesql
03. 觀察 Execution Plan
- 輸入 NULL
在 Proc 內使用 Dynamic SQL: sp_executesql
- Parameter Sniffing: Reuse Execution Plan
- 僅第 1 次 有編譯產生 Execution Plan,之後都是共用同一份 Execution Plan。
- 執行 10 次,即便是輸入不同的參數值,都是共用同一份 Execution Plan。
- 節省 編譯 Execution Plan 的資源。
-- figure 103_Plan_Explorer
04. 觀察 Execution Plan
- 輸入 43659
-- figure 104_Plan_Explorer
05. 執行 GetSalesList_DynamicSQL_sp_executesql
- 執行 6 次。
- 但第 2 與 3 次的輸入 parameter 都是相同。
-- figure 105_Parameter Sniffing reuse execution plan
06. 由 Cache,觀察 Performance Data
- ExecutionCount: 6。
- ObjType: Prepared。
- 重複使用同一份 Execution Plan。
Dynamic SQL: sp_executesql
- 採取 Parameter Sniffing 機制來 Reuse Execution Plan。
- 必須面對 因故重複使用效率差的 Execution Plan,所帶來的 Side Effects。
-- figure 106_Prepared_Execution_Plan
10. 因應需求,分別建立 Stored Procedure
Use that big brain of yours to think your way out! Look for a new angle.
還有什麼方法,可以有效解決 因故重複使用效率差的 Execution Plan 呢?
記得這句話:方法總比困難多。
回顧一下
條件式: 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 等。
與 Dev Team 討論,理解 Business 商務面的需求:
- 輸入參數是 NULL
- 用於找回全部資料,並且額外顯示 資料表: Sales.SalesOrderDetail 的相關資訊。
- 此為管理階層常用的報表。
- 輸入參數是 特定值
- 是客戶於登入系統時要使用,不需要 資料表: Sales.SalesOrderDetail 的資料。
兩個不同的需求,使用 OR 運算子整合在同一個 Stored Procedure 內。
經確認與討論後,決定改寫為兩隻符合各自需求的 Stored Procedure。
01. 建立 2 隻符合各自需求的 Stored Procedure
GetSalesList_Only
- 輸入參數是 NULL
- WHERE (@SalesOrderID IS NULL)
- 額外顯示 資料表: Sales.SalesOrderDetail 的相關資訊
GetSalesOrderID
- 輸入參數是 特定值
- WHERE sd.SalesOrderID = @SalesOrderID
- 不需要 資料表: Sales.SalesOrderDetail 的資料: sd.*
-- figure 81_Create 2 Procs for different purposes
02. 執行 這兩 2 隻符合各自需求的 Stored Procedure
分別輸入 GetSalesList_Only NULL 與 GetSalesOrderID 43659。
GetSalesList_Only NULL:
- 取回全部資料,回傳 118,744 rows,使用 Clustered Index Scan。
GetSalesOrderID 43659
- 取回指定 SalesOrderID 的資料,回傳 12 rows。
- 經 Query Optimizer 執行優化後,使用效率佳 Clustered Index Seek。
- 這是有提升執行效能的 Execution Plan。
-- figure 82_EXEC_2 Procs
03. 觀察 Execution Plan
當執行 GetSalesList_Only NULL:
- 使用 Clustered Index Scan。
- Actual Rows: 118,744。
- Est Rows: 116,323,這是 Estimated Number of Rows。
- Reads: 2,249,這是 Logical Reads。
相比先前的解決方案,與使用 輸入參數 NULL 的 Execution Plan 都雷同。
-- figure 83_Plan_Explorer
-- figure 84_Plan_Explorer
-- figure 85_Compute Scalar 1
-- figure 86_Compute Scalar 2
-- figure 87_LineTotal_Data
04. 觀察 Execution Plan
當執行 GetSalesOrderID 43659:
- 使用 Clustered Index Seek。
- Actual Rows: 12。
- Est Rows: 12,這是 Estimated Number of Rows。精準符合 Actual Rows。
- Reads: 12,這是 Logical Reads。
- 省去無需使用的 資料行
- 只需 3 個資料行:c.PersonID, sh.Freight, cc.CardType
- 省去使用 Compute Scalar 運算子。
相較先前的解決方案,這是 執行效能最佳 的 Execution Plan !
-- figure 88_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
[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
[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (3)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or_24.html
[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (4)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or_31.html
[Parameter Sniffing] Reuse bad execution plan and Recompile stored procedure, 重複使用效能差的 執行計劃 與 重新編譯 預存程序
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-reuse-bad-execution.html
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
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/2017/11/sql-server-ad-hoc-query-and-reuse.html
Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與 動態 SQL)
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-dynamic-sql.html
沒有留言:
張貼留言