[SQL Server][DMV]透過動態管理檢視(DMV)抓子樹成本較高的SQL 指令或是預存程序

2017-11-30

想透過cached plan抓最近經常被使用，而且SQL子樹成本(SubTreeCost)較高的執行計畫作年底上線前的最後衝刺。排進Backlog之前，希望有SQL Text、資料列筆數、執行時間、cpu時間以及圖形執行計畫的資訊綜合輔助自己開Jira Issue。

可惜這家客戶使用的版本不是SQL 2016，超想試試Query Store，今晚我們先組合幾個動態管理檢視(DMV)解任務。

打開圖形執行計畫的第一件事就是看整段SQL語法估計的子樹成本SubTreeCost，他是CPU與I/O成本的算術加總，也是SQL決定是否平行的參考關鍵值(1 Core)。

執行SQL Script

簡單執行一段AdventureWorks2014 資料庫內的Join

USE AdventureWorks2014
SELECT * FROM [Sales].[SalesOrderDetail] A JOIN Production.Product B
     ON A.ProductID = B.ProductID

抓子樹成本較高的SQL 指令或是預存程序

SELECT  top 200
         t.text
         ,cp.objtype
         ,qs.creation_time
         ,qs.last_execution_time
         ,qs.last_elapsed_time / 1000 AS 'last_execution_time(ms)'
         ,qs.execution_count
         ,qs.last_worker_time / 1000 AS 'last_worker_time(ms)'
         ,qs.last_rows
         ,qs.last_logical_reads
         ,qs.last_logical_writes
         ,p.query_plan
         ,TRY_CONVERT(DECIMAL(11,3),p.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]',
         'nvarchar(20)'),-1) AS StatementSubTreeCost
FROM sys.dm_exec_cached_plans cp
JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
where t.dbid = db_id()
   and t.text not like '(@_%'
ORDER by StatementSubTreeCost desc

*TRY_CONVERT是2012新語法，使用在之前版本要改一下。

查詢結果出來了~