[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

• SubTreeCost, CompileTime(ms), CompileCPU(ms), 
• CompileMemory(KB), RetrievedFromCache, ExecutionCount, PlanRecompile, etc.

-- figure 04_Get execution plan for cached query_Execution Count, Plan Recompile, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.

• 當 StateProvinceID = 32，SubTreeCost 是 0.0065704

-- figure 05_Get execution plan for cached query_Execution Count, Plan Recompile, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.




02. 觀察 SSMS GUI 的 Execution Plan 之 Properties 視窗。

• 當 StateProvinceID = 32，SubTreeCost 是 0.0065704

-- figure 33_GUI_Execution_Plan

---

在撰寫 XQuery 時，要留意資料類型

01. 遇到一段特別 SubTreeCost 的 SQL 陳述式

-- figure 61_Special_SubTreeCost

• SubTreeCost 是 0.0000012

-- figure 62_Special_SubTreeCost_Execution_Plan




02. 觀察 XML 格式的 Execution Plan

• StatementSubTreeCost="1.157E-06"

-- figure 63_XML_StatementSubTreeCost



03. 正確顯示，與 GUI 介面的 Execution Plan，能顯示出一致的數值：0.0000012。

• XQuery 使用資料類型 float ，再使用 TRY_CAST 轉型為 decimal(38,7)

-- figure 64_XML_StatementSubTreeCost

---

清空 Cache

請勿於 Production 環境執行

-- figure 301_Remove_Cache

---

範例程式碼

20171223_Get_execution_plan_from_DMV
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

[SQL Server] sys.dm_exec_query_stats: Get performance statistics from cached plan, 觀察 Plan Cache 中的效能統計資訊
http://sharedderrick.blogspot.tw/2017/12/sql-server-sysdmexecquerystats-get.html

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

[SQL Server] Look at Execution Plan and index: use Index Hint, WITH(FORCESEEK), Missing Index
http://sharedderrick.blogspot.tw/2017/12/sql-server-look-at-execution-plan-and.html

Display and Save Execution Plans
https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-and-save-execution-plans