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

想透過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新語法,使用在之前版本要改一下。

查詢結果出來了~

  • 可以點選最右邊的query plan超連結,SSMS會跳出圖形執行計畫,如果有安裝付費版的apexSQL,他呈現的圖形更厲害。
  • 查詢結果也包含最近的執行統計, 包含Buffer Cache的讀取及寫入、實體磁碟讀取、執行次數、CPU時間、最近執行時間、資料列數及子樹成本等。

 


小結

  • 可以把執行計畫儲存,然後用Visual Studio打開觀察xml結構,可以加快寫XQuery的速度。
  • 可以選自己重視的條件排序,成本雖高有時跑很快。
  • 瞬間有居高臨下的感覺,但看到要調的很多,哈 ^^

五稜郭的星,是日本江戶時代末期在蝦夷地的箱館建造的星形要塞(北海道-函館)

 


參考

sys.dm_exec_cached_plans (Transact-SQL)

sys.dm_exec_query_plan (Transact-SQL)

sys.dm_exec_query_stats (Transact-SQL)

sys.dm_exec_sl_text (Transact-SQL)

顯示圖形執行計畫 (SQL Server Management Studio)

Simple query to check the recent performance history II – now including Query Plan information

查詢存放區使用案例

ApexSQL