2018-09-17

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



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

QUOTENAME (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/quotename-transact-sql?view=sql-server-2017

SQL 資料隱碼
https://docs.microsoft.com/zh-tw/sql/relational-databases/security/sql-injection?view=sql-server-2017
於

沒有留言:

張貼留言