搜尋本站文章

2018-08-14

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與 動態 SQL)



Dynamic SQL: sp_executesql 與 EXEC 有其彈性 與 應用的特殊場合。
但 Dynamic SQL 需面對 Parameter Sniffing 所帶來的 優點 與 Side Effects。


Re-Use Execution Plan ObjType
Dynamic SQL: EXEC Ad hoc Query - 比對 hash 值
Adhoc
Dynamic SQL: sp_executesql Parameter Sniffing
Prepared
Stored Procedure Parameter Sniffing
Proc




Dynamic SQL: EXEC 
  • 採取比對 hash 值
    • 相同,就能重複使用 Execution Plan。
    • 不同,產生新的 Execution Plan。

Dynamic SQL: sp_executesql 
  • 使用 Parameter Sniffing 機制來 Re-Use Execution Plan。
  • 必須面對 Parameter Sniffing 所帶來的 Side Effects。

思考

  • sp_executesql,受制於 Parameter Sniffing,導致重複使用效能不佳的 Execution Plan。



後續文章,將繼續討論 例外情形。



sp_executesql

  • 若 Transact-SQL 陳述式本身維持不變,只有 parameters  值改變。
    • 因此,Query Optimizer 可能會重複使用它針對第 1 次執行所產生的Execution Plan。
  • 也就是說,當陳述式 parameters  值的變更是唯一的變數時,您可以利用 sp_executesql 取代 Stored Procedure 來重複執行 Transact-SQL 陳述式。 
  • 若要提升效能,請在陳述式字串中使用 完整物件名稱。

在 sp_executesql 中替換 parameters 的能力,會為利用 EXECUTE 陳述式來執行字串帶來下列好處:

  • 由於在各次執行之間,sp_executesql 字串中 Transact-SQL 陳述式的實際文字不會改變。
  • 因此,Query Optimizer 可能會符合第 2 次執行的 Transact-SQL 陳述式與針對第 1 次執行所產生的執行計畫。因此,不需要編譯第 2 個陳述式。
  • Transact-SQL 字串只建立一次。




Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC 
Parameter Sniffing 與 動態 SQL



01. 使用 Dynamic SQL: EXEC

  • 執行 10 次。
  • 傳入參數都不同。

-- figure 11_Dynamic SQL_EXEC




02. 觀察 Performance Statistics:


使用 Dynamic SQL: EXEC

  • 如同 Ad hoc Query 一般,Query Optimizer 為每一個 SQL 陳述式建立 hash value,以此 hash value 比對另一 SQL 陳述式的 hash value 是否相同。
    • 若 hash 值 相同,就能重複使用該 SQL 陳述式所建立 Execution Plan。
    • 若 hash 值 不同,就必須為該 SQL 陳述式編譯新的 Execution Plan。
  • 也就是說,要能重複使用 Execution Plan,則執行的 Ad hoc Query 必須完全相同。
    • 舉例來說,這包含了:空白、註解、大小寫、結尾符號、SET 選項 等。


觀察

  • RetrievedFromCache 是 true
  • Execution Count ,若 hash 值 相同,重複使用 Execution Plan,累加。
  • CacheObject 是 Compiled Plan。
  • ObjType 是 Adhoc


Ad hoc Query and Reuse - 重複使用
http://sharedderrick.blogspot.com/2017/11/sql-server-ad-hoc-query-and-reuse.html

-- figure 12_Get last performance statistics



03. 再度執行 01. 使用 Dynamic SQL: EXEC 後,觀察 Performance Statistics

  • Execution Count ,若 hash 值 相同,重複使用 Execution Plan。
    • 本次範例,已累加到 2
  • ObjType 是 Adhoc

-- figure 13_2_Get last performance statistics





04. 使用 Dynamic SQL: sp_executesql

  • 執行 10 次。
  • 傳入參數都不同。

-- figure 21_Dynamic SQL_sp_executesql





05. 觀察 Performance Statistics:


使用 Dynamic SQL: sp_executesql

  • Parameter Sniffing: Re-Use Execution Plan。
  • 僅第 1 次 有編譯產生 Execution Plan,之後都是共用同一份 Execution Plan。


觀察

  • TSQL 是「Parameterization(參數化)」
    • (@StateProvinceID INT)SELECT City FROM Person.Address WHERE StateProvinceID = @StateProvinceID
  • RetrievedFromCache 是 true
  • 由於 Parameter Sniffing 發揮功能,重複使用同一份 Execution Plan。
    • 本次範例 Execution Count,已經累加到 10
  • CacheObject 是 Compiled Plan。
  • ObjType 是 Prepared


-- figure 22_Get last performance statistics





06. 再度執行 使用 Dynamic SQL: sp_executesql 後,觀察 Performance Statistics


  • 由於 Parameter Sniffing 發揮功能,重複使用同一份 Execution Plan。
    • 本次範例 Execution Count,已經累加到 20
  • ObjType 是 Prepared


-- figure 23_2nd_Get last performance statistics






Proc and Dynamic SQL: sp_executesql, EXEC




01. 建立 Stored Procedure: Proc and Dynamic SQL: EXEC


  • 在 Proc 內使用 Dynamic SQL: EXEC

-- figure 31_Proc and Dynamic SQL_EXEC





02. 執行 Proc_DynamicSQL_EXEC

  • 執行 10 次。
  • 傳入參數都不同。

-- figure 32_Exec_Proc_DynamicSQL_EXEC




03. 觀察 Performance Statistics:


在 Proc 內使用 Dynamic SQL: EXEC
  • 如同 Ad hoc Query 一般,Query Optimizer 為每一個 SQL 陳述式建立 hash value,以此 hash value 比對另一 SQL 陳述式的 hash value 是否相同。
    • 若 hash 值 相同,就能重複使用該 SQL 陳述式所建立 Execution Plan。
    • 若 hash 值 不同,就必須為該 SQL 陳述式編譯新的 Execution Plan。
  • 也就是說,要能重複使用 Execution Plan,則執行的 Ad hoc Query 必須完全相同。
    • 舉例來說,這包含了:空白、註解、大小寫、結尾符號、SET 選項 等。
觀察

  • RetrievedFromCache 是 true
  • Execution Count ,若 hash 值 相同,重複使用 Execution Plan,累加。
  • CacheObject 是 Compiled Plan。
  • ObjType 是 Adhoc



-- figure 33_Get last performance statistics



04. 再度執行 02. 執行 Proc_DynamicSQL_EXEC 後,觀察 Performance Statistics

  • Execution Count ,若 hash 值 相同,重複使用 Execution Plan。
    • 本次範例,已累加到 2
  • ObjType 是 Adhoc

-- figure 34_2nd_Get last performance statistics




05. 建立 Stored Procedure: Proc_DynamicSQL_sp_executesql


  • 在 Proc 內使用 Dynamic SQL: sp_executesql


-- figure 41_Proc and Dynamic SQL_sp_executesql




06. 執行 Proc_DynamicSQL_sp_executesql

  • 執行 10 次。
  • 傳入參數都不同。

-- figure 42_Exec_Proc_DynamicSQL_sp_executesql




07. 觀察 Performance Statistics:

在 Proc 內使用 Dynamic SQL: sp_executesql


  • Parameter Sniffing: Re-Use Execution Plan
  • 僅第 1 次 有編譯產生 Execution Plan,之後都是共用同一份 Execution Plan。
  • 執行 10 次,即便是輸入不同的參數值,都是共用同一份 Execution Plan。
  • 節省 編譯 Execution Plan 的資源。


觀察

  • TSQL 是「Parameterization(參數化)」
    • (@StateProvinceID INT)SELECT City FROM Person.Address WHERE StateProvinceID = @StateProvinceID
  • RetrievedFromCache 是 true
  • 由於 Parameter Sniffing 發揮功能,重複使用同一份 Execution Plan。
    • 本次範例 Execution Count,已經累加到 10
  • CacheObject 是 Compiled Plan。
  • ObjType 是 Prepared


-- figure 43_Get last performance statistics




08. 再度執行 06. 執行 Proc_DynamicSQL_sp_executesql 後,觀察 Performance Statistics

  • 由於 Parameter Sniffing 發揮功能,重複使用同一份 Execution Plan。
    • 本次範例 Execution Count,已經累加到 20
  • ObjType 是 Prepared

-- figure 44_2nd_Get last performance statistics







對照組


-- figure 51_Proc without Dynamic SQL



-- figure 52_Exec_Proc_NonDynamicSQL



-- figure 53_Get last performance statistics



-- figure 54_2nd_Get last performance statistics





很單純的 Proc,沒有使用 Dynamic SQL

Parameter Sniffing: Re-Use Execution Plan

  • 僅第 1 次 有編譯產生 Execution Plan,之後都是共用同一份 Execution Plan。
  • 執行 10 次,即便是輸入不同的參數值,都是共用同一份 Execution Plan。
  • 節省 編譯 Execution Plan 的資源。


觀察

  • RetrievedFromCache 是 true
  • 由於 Parameter Sniffing 發揮功能,重複使用同一份 Execution Plan。
    • Execution Count,已經累加。
  • CacheObject 是 Compiled Plan。
  • ObjType 是 Proc




Sample Code

20180814_Parameter_Sniffing_Dynamic_SQL
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing





Reference

Parameter Sniffing: Pros and Cons, 參數探測
http://sharedderrick.blogspot.com/2017/12/sql-server-parameter-sniffing-pros-and.html

Parameter Sniffing: sp_recompile, WITH RECOMPILE
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing.html

Compare "WITH RECOMPILE / sp_recompile" with "OPTION RECOMPILE"
http://sharedderrick.blogspot.com/2018/02/sql-server-compare-with-recompile-with.html

Enable optimize for ad hoc workloads , 針對特定工作負載最佳化
http://sharedderrick.blogspot.com/2017/11/sql-server-enable-optimize-for-ad-hoc.html

Ad hoc Query and Reuse - 重複使用
http://sharedderrick.blogspot.com/2017/11/sql-server-ad-hoc-query-and-reuse.html

Dynamic SQL - 動態 SQL
https://docs.microsoft.com/zh-tw/sql/odbc/reference/dynamic-sql?view=sql-server-2017

Static SQL - 靜態 SQL
https://docs.microsoft.com/zh-tw/sql/odbc/reference/static-sql?view=sql-server-2017

sp_executesql (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

EXECUTE (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017

沒有留言:

張貼留言