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



-- figure 42_Get execution plan for cached query_ParameterList




Sample Code

20180730_Look at parameter_TSQL
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing



Reference

[SQL Server] SSMS: Look at parameter from execution plan, 查看 執行計畫 所使用的參數
http://sharedderrick.blogspot.com/2017/12/sql-server-look-at-parameter-from.html
於

沒有留言:

張貼留言