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:-

---