Script to find out most CPU bound Queries


Execute below query to get TOP 25 completed queries which have the highest cumulative CPU usage

SELECT highest_cpu_queries.plan_handle, 
(highest_cpu_queries.total_worker_time/highest_cpu_queries.execution_count) AS AverageCPU, highest_cpu_queries.execution_count, highest_cpu_queries.total_worker_time, highest_cpu_queries.plan_generation_num, highest_cpu_queries.creation_time, highest_cpu_queries.last_execution_time, 
highest_cpu_queries.last_physical_reads, highest_cpu_queries.min_physical_reads,
q.dbid, q.objectid, q.number, q.encrypted, q.[text]
FROM (SELECT TOP 25 qs.plan_handle, qs.total_worker_time, qs.last_execution_time,
qs.plan_generation_num, qs.creation_time, qs.execution_count, qs.last_physical_reads,
qs.min_physical_reads FROM sys.dm_exec_query_stats qs 
ORDER BY qs.total_worker_time DESC) AS highest_cpu_queries 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY AverageCPU DESC

Above script will return the queries which are completed. You can check the active sessions and CPU details using below query.

SELECT SPID, CPU, s2.text, open_tran, status, program_name,
net_library, loginame FROM sys.sysprocesses 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
where cpu > 5000 and status = 'runnable' 

2 thoughts on “Script to find out most CPU bound Queries

Leave a Reply to chowdary Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s