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