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'
Great guide – keep helping the community. Thank you
Really helpfull……………great Articles