Find Queries Using Highest CPU Utilization in SQL Server

by Manzoor Siddiqui · Published October 6, 2015 · Updated October 25, 2015

407views

As a DBA, time to time we have to trace SQL queries which are consuming highest CPU utilization in SQL Server and need to tune those queries. You can refer below query which provides details for top 10 expensive queries based on CPU utilization. This query also provides details like last execution time, execution count, worker time, physical reads, logical reads, logical writes, elapsed time etc.

SELECT TOP 10 EQS.total_worker_time / execution_count AS AvgWorkerTime,
substring(EST.text,(EQS.statement_start_offset / 2) + 1, ((
CASE EQS.statement_end_offset WHEN -1 THEN datalength(EST.text)
ELSE EQS.statement_end_offset END - EQS.statement_start_offset) / 2) + 1) AS Statement, 
EQS.last_execution_time, EQS.execution_count, EQS.total_worker_time, EQS.total_physical_reads, 
EQS.total_logical_writes, * FROM sys.dm_exec_query_stats AS EQS
CROSS APPLY
sys.dm_exec_sql_text(EQS.sql_handle) AS EST
ORDER BY AvgWorkerTime DESC

DMO sys.dm_exec_query_stats basically returns aggregate performance statistics for cached query plans so if the plan is removed or cache is cleared then records will not be available. You may get more accurate results if the query has run multiple times.

In above screen snap example there is not much query execution as I had restarted my test server prior to finding these queries so it is showing execution_count column to 1 but in your case you will get some good results.

You can modify this query according to your requirements and you can trace some good information for tuning.