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
沒有留言:
張貼留言