德瑞克：SQL Server 學習筆記: 2018/09

消失的 Execution Plan

當使用 Dynamic SQL 動態傳遞 TABLE name(資料表名稱) 變數

無論是使用 sp_executesql 或 EXEC，都要等到執行階段，動態傳入資料表名稱後，Query Optimizer 方能依據傳入的資料表資訊，分析 SQL 陳述式，判斷出取得所需資料的最有效方式。

影響

Display Estimated Execution Plan 無法正確顯示。
Plan Cache 無法直接觀察。

看不到實際執行的 Execution Plan!

Dynamic SQL: sp_executesql, Pass dynamic TABLE name variable,
動態傳遞資料表名稱變數

01. 建立使用 Dynamic SQL: sp_executesql 動態傳遞資料表變數的 Stored Procedure。

@tsql

需要執行的 SQL 陳述式或批次，必須使用 Unicode 字串。

@Parms

N'@TransactionDate datetime'，此為參數定義的 Unicode 字串。，由參數名稱和資料類型組成。

使用 QUOTENAME()

附加傳回 [] 分隔符號，以便讓輸入字串成為有效的 [SQL Server] 分隔識別碼。
限制為 128 個 Unicode 字元。
用於處理 SQL Injection(資料隱碼) 問題，驗證 Dynamic SQL 使用的所有變數都已正確處理。

-- figure 01_Proc_pr_DynamicTable

02. 點選 Display Estimated Execution Plan，嘗試檢視 Execution Plan。

檢視使用 Dynamic SQL: sp_executesql 動態傳遞資料表名稱變數的 Stored Procedure。

-- figure 02_Display Estimated Execution Plan

03. 消失的 Execution Plan!

無可分析!

因為要等到執行階段，動態傳入資料表名稱後，Query Optimizer 方能依據傳入的資料表資訊，分析 SQL 陳述式，判斷出取得所需資料的最有效方式。

-- figure 03_Display Estimated Execution Plan

04. 改用 Include Actual Execution Plan。

點選 Include Actual Execution Plan，執行 2 段 SQL 陳述式。

-- figure 04_Exec_Proc_Include Actual Execution Plan

05. 檢視 Stored Procedure 執行結果。

資料表變數: TransactionHistory

回傳 0 筆資料列。

資料表變數: TransactionHistoryArchive

回傳 215 筆資料列。

-- figure 05_Exec_Proc_Include Actual Execution Plan

06. 檢視 Execution Plan。

資料表變數: TransactionHistory

回傳 0 筆資料列。
Clustered Index Scan。
有 Missing Index。

資料表變數: TransactionHistoryArchive

回傳 215 筆資料列。
Clustered Index Scan。
有 Missing Index。

-- figure 06_View_Actual_Execution_Plan

07. 檢視 Plan Cache

1st: CREATE PROC pr_DynamicTable @tbname sysname, ...

LastElapsedTime, last_logical_reads 等，都是 NULL。
ExecutionCount 累積到 2。
ObjType: Proc。

2nd 與 3rd: SET STATISTICS XML OFF 與 SET STATISTICS XML ON

這是因為啟用了 Include Actual Execution Plan 功能，SQL Server 使用 XML 文件格式來產生 SQL 陳述式執行狀況的詳細資料。
LastElapsedTime, last_logical_reads 等，都是 NULL。
ObjType: Prepared。

4th: (@TransactionDate datetime) SELECT [TransactionID],..

LastElapsedTime, last_logical_reads 等，都有顯示出資料。
這是實際執行 SQL 陳述式後產生的效能資料。
點選 [QueryPlan]，可以看到實際執行的 Execution Plan。
ObjType: Prepared。

5th: (@TransactionDate datetime) SELECT [TransactionID],..

LastElapsedTime, last_logical_reads 等，都有顯示出資料。
這是實際執行 SQL 陳述式後產生的效能資料。
點選 [QueryPlan]，可以看到實際執行的 Execution Plan。
ObjType: Prepared。

-- figure 07_Plan_Cache

08. 可以看到 [ParameterList] 與 [QueryPlan] 都有實際資料。

-- figure 08_Plan_Cache

消失的 Execution Plan

看不到實際執行的 Execution Plan!

-- figure 09_1 Missing Execution Plan

Execution Plan 藏在這裡 ...

-- figure 09_2

09. 重複執行多次 Stored Procedure。

資料表變數: TransactionHistory，再執行 10 次。
資料表變數: TransactionHistoryArchive，再執行 20 次。

-- figure 11_Run many times

10. 觀察 Plan Cache。

1st: ExecutionCount 累積到 32。

ObjType: Proc。

2nd: 資料表變數: TransactionHistoryArchive，ExecutionCount 累積到 21。

ObjType: Prepared。

3rd: 資料表變數: TransactionHistory，ExecutionCount 累積到 11。

ObjType: Prepared。

-- figure 12_Plan_Cache

11. 輸入更多不同的 Parameter value

資料表變數: TransactionHistory

但日期更換為: 2013-06-10, 2013-06-11, 2013-06-12

資料表變數: TransactionHistoryArchive

但日期更換為: 2013-06-10, 2013-06-11, 2013-06-12

-- figure 13_ Different parameter value with the same table

12. 觀察 Plan Cache。

1st: ExecutionCount 累積到 38。

ObjType: Proc。

2nd: 資料表變數: TransactionHistoryArchive，ExecutionCount 累積到 24。

使用相同資料表，雖然 Parameter value不同，但可以 Reuse Execution Plan。
ObjType: Prepared。

3rd: 資料表變數: TransactionHistory，ExecutionCount 累積到 14。

使用相同資料表，雖然 Parameter value不同，但可以 Reuse Execution Plan。
ObjType: Prepared。

Dynamic SQL: sp_executesql

ObjType: Prepared
使用 Parameter Sniffing 機制來 Re-Use Execution Plan。
必須面對 Parameter Sniffing 所帶來的 Side Effects。

-- figure 14_Plan Cache

Dynamic SQL: EXEC, Pass dynamic TABLE name variable
動態傳遞資料表名稱變數

改用 EXEC。

01. 建立使用 Dynamic SQL: EXEC 動態傳遞資料表變數的 Stored Procedure。

@tsql: 需要執行的 SQL 陳述式或批次。

-- figure 71_Proc_Pass dynamic TABLE name to EXEC

02. 點選 Display Estimated Execution Plan，消失的 Execution Plan，無可分析!

因為要等到執行階段，動態傳入資料表名稱後，Query Optimizer 方能依據傳入的資料表資訊，分析 SQL 陳述式，判斷出取得所需資料的最有效方式。

03. 改用 Include Actual Execution Plan。

點選 Include Actual Execution Plan，執行以下多段 SQL 陳述式。

-- figure 72_Exec_Proc_pr_DynamicTable_EXEC

04. 觀察 Execution Plan。

1st: CREATE PROC pr_DynamicTable @tbname sysname, ...

LastElapsedTime, last_logical_reads 等，都是 NULL。
ExecutionCount 累積到 2。
ObjType: Proc。

2nd: 是重複執行 'TransactionHistoryArchive', '2013-07-30';

ExecutionCount 累積到 21。
ObjType: Adhoc。

3rd: 是重複執行 'TransactionHistory', '2013-07-30';

ExecutionCount 累積到 11。
ObjType: Adhoc。

4th~9th: 資料表變數: TransactionHistory 或是 TransactionHistoryArchive

但日期更換為: 2013-06-10, 2013-06-11, 2013-06-12
ExecutionCount 都是 1

相同資料表，但 Parameter value不同，產生的 hash value 也不相同。
導致無法 Reuse Execution Plan。

ObjType: Adhoc。

-- figure 73_Plan_Cache

05. 觀察 Execution Plan，檢視 SQL statement 的 Parameter value。

Parameter value不同，產生的 hash value 也不相同，導致無法 Reuse Execution Plan。

Dynamic SQL: EXEC

採取比對 hash 值

相同，就能重複使用 Execution Plan。
不同，產生新的 Execution Plan。
ObjType: Adhoc。

-- figure 74_Plan_Cache

QUOTENAME()

功能：傳回 Unicode 字串，且附加了分隔符號，以便使輸入字串成為有效的 [SQL Server] 分隔識別碼。
限制為 128 個字元。大於 128 個字元的輸入會傳回 NULL。

語法
QUOTENAME ( 'character_string' [ , 'quote_character' ] )

'quote_character'
這是用來當做分隔符號的單字元字串。
它可以是單引號 ( ' )、左或右方括弧 ( [] )、雙引號 ( " )、左或右括弧 ( () )、
大於或小於符號 ( >< )、左或右大括弧 ( {} ) 或反勾號 ( ` )。

如果提供了無法接受的字元，則會傳回 NULL。
如果未指定 quote_character，就會使用方括號。

用於面對 SQL Injection(資料隱碼)，建議搭配 QUOTENAME() 或 REPLACE()，來驗證 Dynamic SQL 使用的所有變數都已正確處理。

Sample Code

20180917_Dynamic SQL Pass dynamic TABLE name variable
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

延續前一篇文章：[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 方式，程式碼可讀性略差，是會增加維護成本。

-- figure 91_ALTER Proc Dynamic SQL EXEC

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。

-- figure 92_EXEC Proc Dynamic SQL EXEC

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。

-- 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)