Find Queries Using Highest CPU Utilization in SQL Server

CPUUtilization

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

HighestCPUUtilization

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.

CLICK HERE to watch live practical.

[Note: Right click the video link and open in new window if it is not opening]

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like...

1 Response

  1. Pertsa says:

    That query brings the statements that have the highest AVERAGE cpu utilization and it totally ignores number of executions — so you can get list of statements that have only been executed once, and totally miss statements executed hundreds or thousands of times.

Leave a Reply

Your email address will not be published.