DBA Scripts: Get Top CPU Queries

Peter Whyte

–

Part of the DBA-Tools Project.

Three Different Ways a Query Can Be the Problem

“Which query is causing this” has more than one right answer, because a query can be expensive in different ways: it can burn CPU, it can generate huge I/O, or it can just habitually run slowly every time regardless of resource cost. These three scripts each rank the plan cache by a different metric, so the specific kind of expensive gets surfaced rather than lumped into one generic “slow queries” list.

Together they cover the three questions a performance investigation usually needs answered: what’s using the CPU, what’s generating the I/O, and what’s just consistently slow.

Why This Matters

  • A query can be CPU-heavy without being I/O-heavy, or the reverse, ranking by the wrong metric can point you at the wrong query entirely
  • total_worker_time (CPU), total_logical_reads (I/O), and average elapsed time (habitual slowness) are three genuinely different signals, all derived from the same sys.dm_exec_query_stats DMV
  • All three read from the plan cache, so they cover activity since the last restart or plan eviction, not just what’s running right now, complementary to a live-session check like Long-Running Queries
  • On a memory-constrained instance, the plan cache itself can be a moving target, see the honest note in Example Output below

The Scripts

Get-TopCpuQueries — Ranked by Total CPU Time

/*
Script Name : Get-TopCpuQueries
Category    : performance-troubleshooting
Purpose     : List top 20 CPU-consuming queries with execution counts and timing metrics.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-top-cpu-queries/)
Requires    : VIEW SERVER STATE
HealthCheck : Yes
*/
-- Blog: https://sqldba.blog/dba-scripts-get-top-cpu-queries/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT TOP (20)
    DB_NAME(st.dbid) AS database_name,
    qs.execution_count,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000 AS avg_cpu_ms,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
              ((CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE qs.statement_end_offset
                END - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

Get-TopIoQueries — Ranked by Total Logical Reads

/*
Script Name : Get-TopIoQueries
Category    : performance-troubleshooting
Purpose     : Top 20 queries by total logical reads since last restart — primary I/O pressure source.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-top-cpu-queries/)
Requires    : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-top-cpu-queries/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT TOP 20
    DB_NAME(st.dbid)                                                        AS database_name,
    qs.execution_count,
    qs.total_logical_reads,
    CAST(qs.total_logical_reads / NULLIF(qs.execution_count, 0)
         AS BIGINT)                                                         AS avg_logical_reads,
    qs.total_physical_reads,
    CAST(qs.total_physical_reads / NULLIF(qs.execution_count, 0)
         AS BIGINT)                                                         AS avg_physical_reads,
    qs.total_logical_writes,
    CAST(qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000
         AS BIGINT)                                                         AS avg_cpu_ms,
    qs.creation_time                                                        AS plan_cached,
    SUBSTRING(
        st.text,
        (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1
    )                                                                       AS statement_text
FROM sys.dm_exec_query_stats  AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_logical_reads DESC;

Get-SlowQueriesFromCache — Ranked by Average Elapsed Time

/*
Script Name : Get-SlowQueriesFromCache
Category    : performance-troubleshooting
Purpose     : Top 20 queries by average elapsed time from the plan cache — identifies habitually slow queries.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-top-cpu-queries/)
Requires    : VIEW SERVER STATE
Notes       : Covers cached plans since last restart or plan eviction. Complements
              Get-LongRunningQueries (live requests) and Get-TopCpuQueries (total CPU).
*/
-- Blog: https://sqldba.blog/dba-scripts-get-top-cpu-queries/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT TOP 20
    DB_NAME(st.dbid)                                                        AS database_name,
    qs.execution_count,
    CAST(qs.total_elapsed_time / NULLIF(qs.execution_count, 0) / 1000.0
         AS DECIMAL(12,1))                                                  AS avg_elapsed_ms,
    CAST(qs.max_elapsed_time / 1000.0 AS DECIMAL(12,1))                    AS max_elapsed_ms,
    CAST(qs.total_elapsed_time        / 1000.0 AS DECIMAL(14,1))           AS total_elapsed_ms,
    CAST(qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000.0
         AS DECIMAL(12,1))                                                  AS avg_cpu_ms,
    qs.total_logical_reads / NULLIF(qs.execution_count, 0)                 AS avg_logical_reads,
    qs.creation_time                                                        AS plan_cached,
    SUBSTRING(
        st.text,
        (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1
    )                                                                       AS statement_text
FROM sys.dm_exec_query_stats  AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.execution_count > 1
ORDER BY qs.total_elapsed_time / NULLIF(qs.execution_count, 0) DESC;

Same underlying DMV, three different ORDER BY clauses, each answering a genuinely different question about what “expensive” means for that query.

How To Run From The Repo

Clone DBA Tools, initialize and run whichever ranking you need:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Rank by CPU, I/O, or habitual slowness:
.\run.ps1 Get-TopCpuQueries
.\run.ps1 Get-TopIoQueries
.\run.ps1 Get-SlowQueriesFromCache

# To run against a remote sql server:
.\run.ps1 Get-TopCpuQueries -ServerInstance SQLSERVER01

These scripts live in the repo at:

Example Output

Genuinely tested against this lab instance, and the test itself turned into a real finding worth documenting honestly rather than hiding: this box’s plan cache clears down to essentially nothing within seconds of a session disconnecting, confirmed directly, sys.dm_exec_query_stats sat at 0 rows moments after running representative queries, and a single query’s own stats disappeared the instant its session closed. That’s consistent with the sustained memory pressure already found and documented on this same lab instance via Recent Error Log Entries, aggressive plan cache eviction under memory pressure is expected behavior, not a bug in these scripts.

On an instance with normal memory headroom, a row looks like this:

database_name execution_count total_cpu_ms avg_cpu_ms statement_text
SalesDB 1,204 48,200 40 SELECT * FROM Orders WHERE CustomerID = @p1

Understanding the Results

  • execution_count — how many times this exact cached plan has run. A high-cost query with a low execution count is a one-off; the same cost with a high execution count is a query worth tuning, since every run pays the price
  • total_cpu_ms vs avg_cpu_ms — total tells you overall instance impact, average tells you whether each individual execution is expensive. A query with huge total but tiny average is just called constantly, not necessarily poorly written
  • total_logical_reads / avg_logical_reads — logical reads correlate directly with buffer pool pressure and, when they spill to physical reads, real disk I/O
  • avg_elapsed_ms vs avg_cpu_ms (Slow Queries From Cache) — elapsed time well above CPU time means the query is spending time waiting, not computing, pair with Wait Statistics to see what it’s waiting on
  • plan_cached — how long this plan has been in cache. A very recent plan_cached timestamp next to a high execution_count is unusual and worth a second look

Best Practices

  • Check all three, not just one, a query can rank low on CPU and still be the worst I/O offender on the instance
  • Cross-reference with Statistics Health and Implicit Conversions, both are common root causes behind a query that ranks high here for no obvious reason in the query text itself
  • Remember these three only see what’s currently cached, a plan that’s already been evicted (memory pressure, a recompile, a restart) won’t show up, however expensive it actually was

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

Why do all three scripts return the same columns from a different order?

They read the same underlying DMV, sys.dm_exec_query_stats, which tracks every metric for every cached plan at once. Ranking by a different column answers a different question without needing three separate data sources.

Why did my query disappear from the results between two runs?

Its plan was evicted from cache, most commonly from memory pressure, a recompile (a stats update or schema change invalidates the plan), or a server restart. The DMV only reflects what’s currently cached, not a permanent history.

Summary

CPU, I/O, and habitual slowness are three different ways a query can be the one causing a problem, and ranking by the wrong metric can send an investigation in the wrong direction. These three scripts, one DMV, three ORDER BY clauses, cover all three questions directly.

Run all three together as part of a performance investigation rather than picking one, and remember they only see what’s still in the plan cache right now.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *