德瑞克：SQL Server 學習筆記: Dynamic SQL: Pass dynamic TABLE name variable, 動態傳遞資料表名稱變數

消失的 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