德瑞克：SQL Server 學習筆記

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

2018-07-30

TSQL: Look at parameter from execution plan, 查看執行計畫所使用的參數

延續先前文章 SSMS: Look at parameter from execution plan, 查看執行計畫所使用的參數

在對 Stored Procedure 執行 Performance Optimization (效能優化)時，若能知道編譯時期的 Parameter Compiled Value，將能夠精準有效率的執行優化任務。

可使用以下方式查詢：Parameter Compiled Value

使用 SSMS
使用 DMV: sys.dm_exec_query_plan

使用 SSMS 觀察 Execution Plan，可以看到

Column
Parameter Compiled Value
Parameter Data Type
Parameter Runtime Value

使用 DMV: sys.dm_exec_query_plan 的資料行: query_plan，可以觀察 ParameterList Element(元素)：

Column: 傳入的 Parameter 名稱。
ParameterDataType: 使用的資料類型。
ParameterCompiledValue: 編譯 Execution Plan 的值。

TSQL: Look at parameter from execution plan, 查
看執行計畫所使用的參數

01. 使用以下範例，其中：

[dbo].[uspGetWhereUsedProductID] 使用的參數是：

@StartProductID=819
@CheckDate='2010-12-23'

-- figure 01_TSQL_Sample

02. 使用以下語法查詢取得 last performance statistics for cached query:

-- Get last performance statistics for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, etc.
SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)],
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)],
 qs.last_logical_reads LastLogicalReads,
 qs.last_physical_reads LastPhysicalRead,
 qs.last_logical_writes LastlogicalWrites,
 qs.last_rows LastRows,
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;
GO

-- figure 11_Get last performance statistics for cached query

03. 檢視查詢結果，可以看到：[ParameterList]。

考量會傳入多個參數值，採用 XML 格式呈現。

-- figure 12_last performance statistics_ParameterList

04. 檢查 ParameterList 的 XML 格式，可以觀察'：

Column: 傳入的 Parameter 名稱。
ParameterDataType: 使用的資料類型。
ParameterCompiledValue: 編譯 Execution Plan 的值。

Column="@CheckDate" ParameterDataType="datetime" ParameterCompiledValue="'2010-12-23 00:00:00.000'"
Column="@StartProductID" ParameterDataType="int" ParameterCompiledValue="(819)"

-- figure 13_Review_ParameterCompiledValue

05. Get performance statistics for cached query - Full

-- Get performance statistics for cached query - Full
/*
sys.dm_exec_query_stats - Returns aggregate performance statistics
An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. 
More accurate results may be determined by rerunning the query.

total_elapsed_time: Total elapsed time, reported in microseconds (but only accurate to milliseconds), 
for completed executions of this plan.
*/

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], cp.usecounts [ExecutionCount],
 qs.total_elapsed_time/cp.usecounts/1000000.0 [AvgElapsedTime(sec)], 
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)], qs.max_elapsed_time/1000000.0 [MaxElapsedTime(sec)], 
 qs.min_elapsed_time/1000000.0 [MinElapsedTime(sec)], qs.total_elapsed_time/1000000.0 [TotalElapsedTime(sec)],
 qs.total_worker_time/cp.usecounts/1000000.0 [AvgCPUTime(sec)], 
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)], qs.max_worker_time/1000000.0 [MaxCPUTime(sec)], 
 qs.min_worker_time/1000000.0 [MinCPUTime(sec)], qs.total_worker_time/1000000.0 [TotalCPUTime(sec)], 
 qs.total_logical_reads/cp.usecounts [AvgLogicalRead], 
 qs.last_logical_reads, qs.max_logical_reads, qs.min_logical_reads, qs.total_logical_reads, 
 qs.last_physical_reads [LastPhysicalRead], qs.max_physical_reads, qs.min_physical_reads, qs.total_physical_reads,  
 qs.total_logical_writes/cp.usecounts [AvgLogicalWrite], qs.last_logical_writes,
 qs.max_logical_writes, qs.min_logical_writes, qs.total_logical_writes,
 qs.last_rows [LastRows], qs.max_rows, qs.min_rows, qs.total_rows,
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList], 
 qp.query_plan [QueryPlan], cp.plan_handle, cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 st.objectid, qs.last_execution_time, qs.creation_time --qs.*
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;
GO

/*
CPU bound - ORDER BY qs.total_worker_time , [AvgElapsedTime(sec)]
I/O bound - ORDER BY qs.total_logical_reads , [AvgLogicalRead]
*/

-- figure 21_Get performance statistics for cached query

06. 檢視回傳的結果：

-- figure 22_Get performance statistics for cached query

07. Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, etc.

-- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, etc.
SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList], 
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;
GO

-- figure 31_Get execution plan for cached query

08. 檢視回傳的結果：

-- figure 32_Get execution plan for cached query_ParameterList

09. -- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.

-- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns)

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 TRY_CAST(qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@StatementSubTreeCost)[1]', 'float') 
 AS decimal(38,7)) [StatementSubTreeCost],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)], 
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@RetrievedFromCache)[1]', 'nvarchar(50)') [RetrievedFromCache],
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014') 
-- AND cp.objtype = 'Proc' CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY StatementSubTreeCost DESC;
GO

-- figure 41_Get execution plan for cached query