SQL SERVER – Find Most Expensive Queries Using DMV


The following query returns the top 10 queries by total CPU time, to help you identify the most expensive queries on your SQL Server instance.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qst.statement_start_offset/2)+1,
((CASE qst.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qst.statement_end_offset
END - qst.statement_start_offset)/2)+1) AS query_text ,
qst.total_logical_reads, 
qst.last_logical_reads,
qst.total_logical_writes, 
qst.last_logical_writes,
qst.execution_count,
qst.total_worker_time,
qst.last_worker_time,
qst.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qst
CROSS APPLY sys.dm_exec_sql_text(qst.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qst.plan_handle) qp
ORDER BY qst.total_worker_time DESC

 

Output:-


 

Leave a Reply

Your email address will not be published. Required fields are marked *