Find Top 10 CPU Intensive Queries

Find Top 10 CPU Intensive Queries

·

1 min read

If you want to find the top ‘n’ queries that are currently in the cache, which are consuming more CPU, then you are at the right place.

sys.dm_exec_query_stats DMV contains all the information about the resource (CPU, Memory, I/O) consuming queries that are currently in the cache.

We can just join the above DMV with sys.dm_exec_query_plan and then with sys.dm_exec_sql_text to get query plans and SQL text that is currently executing.

Here is the query that gives TOP 10 queries that are currently in cache that are consuming more CPU:

;WITH eqs
AS (
    SELECT 
         [execution_count]
        ,[total_worker_time]/1000  AS [TotalCPUTime_ms]
        ,[total_elapsed_time]/1000  AS [TotalDuration_ms]
        ,query_hash
        ,plan_handle
        ,[sql_handle]
    FROM sys.dm_exec_query_stats
    )
SELECT TOP 10 est.[text], eqp.query_plan AS SQLStatement
    ,eqs.*
FROM eqs
OUTER APPLY sys.dm_exec_query_plan(eqs.plan_handle) eqp
OUTER APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS est
ORDER BY [TotalCPUTime_ms] DESC

If we change the column in the ORDER BY to total_logical_reads column then we can find out TOP 10 I/O intensive queries. If we do a sort on the total_grant_kb column, then we can find out Memory intensive queries.

See you soon with another script.