德瑞克：SQL Server 學習筆記: [SQL Server] Get SubTreeCost, CompileTime, CompileMemory, Execution Count, Plan Recompile from Plan Cache

2017-12-23

[SQL Server] Get SubTreeCost, CompileTime, CompileMemory, Execution Count, Plan Recompile from Plan Cache - 子樹成本

在執行 SQL 陳述式的 Performance Tuning 時，CPU 使用時間、Disk IO 使用量等是觀察重點。
請參考這篇： [SQL Server] sys.dm_exec_query_stats: Get performance statistics from cached plan, 觀察 Plan Cache 中的效能統計資訊。

還可以觀察由 Execution Plan 所提供的 SubTreeCost (子樹成本)。
但要提醒的是： SubTreeCost 有時會有不精確的問題，例如：Parameter Sniffing 等，我們將在後續主題討論。

本文利用 DMV 取得 Plan Cache 內的 SubTreeCost, CompileTime, CompileMemory, Execution Count, RetrievedFromCache 等資訊。

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

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
TRY_CAST(qp.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@StatementSubTreeCost)[1]', 'float') AS decimal(38,7))
 StatementSubTreeCost,
qp.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)], 
qp.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
qp.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
qp.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/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 [QueryPlan], cp.plan_handle
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 StatementSubTreeCost DESC;

Get execution plan from DMV: SubTreeCost, CompileTime, CompileMemory, Execution Count, Plan Recompile

01. 執行以下的 Adhoc Query:

-- figure 01_Adhoc_Query

02. 使用 DMV: sys.dm_exec_query_stats 觀察 Plan Cache

Execution Plan, Execution Count, Plan Recompile, Query Plan, etc.

-- figure 02_Get execution plan for cached query_Execution Count, Plan Recompile, Query Plan, etc.

-- figure 03_Get execution plan for cached query_Execution Count, Plan Recompile, Query Plan, etc.

-- Get execution plan for cached query: Execution Count, Plan Recompile, 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 [QueryPlan], cp.plan_handle
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;

使用 XQuery 由 query_plan 取得 SubTreeCost

01. 使用 DMV: sys.dm_exec_query_stats 觀察 Plan Cache