Script: Find Top 10 CPU Intensive Queries

This blog post first appeared on SQLMaestros

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

You can further investigate the queries, that are having the same query_hash but different sql_handle values just to check whether optimizer can re-use the plans or not. Because, if the optimizer is not able to reuse the plans, then it has to generate new plans. Generating a new plan is a CPU intensive process. Here is a quick explanation about how to do the above-said analysis.

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.

This post is part of The Scripts Garage. You can find all scripts at The Scripts Garage.