Reading a Workload Capture Without a Tool

A workload capture should tell you where the server spent its effort, not merely which statement had the longest duration. Group related queries and compare totals before choosing what to tune.

Know What Was Captured

A saved Extended Events capture, Query Store history, and a plan-cache snapshot cover different things. Label the source before analyzing it. Record the collection window and filters. If failed requests or short statements were excluded, the totals describe that filtered workload rather than everything the server did.

The examples here inspect completed executions represented in the current plan cache. They are a useful starting point when no separate capture tool is available. They aren’t a durable history. A restart, eviction, or recompile can remove or change the evidence.

SELECT
    SYSDATETIMEOFFSET() AS captured_at,
    sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT MIN(creation_time) AS oldest_cached_plan,
       MAX(last_execution_time) AS latest_execution
FROM sys.dm_exec_query_stats;

The oldest cached plan isn’t the beginning of a complete observation window. Other plans from the same period can already be gone. Save that limitation with the result. It prevents a cache summary from becoming an accidental claim about the entire day’s traffic.

Group Similar Work

SELECT TOP (20)
    query_hash,
    SUM(execution_count) AS executions,
    SUM(total_worker_time) / 1000.0 AS total_cpu_ms,
    SUM(total_logical_reads) AS total_logical_reads,
    SUM(total_elapsed_time) / 1000.0 AS total_elapsed_ms
FROM sys.dm_exec_query_stats
GROUP BY query_hash
ORDER BY SUM(total_worker_time) DESC;

Query hash helps group statements with similar logic. It can reduce the clutter created by related query forms. Treat it as an investigation key, not a perfect business identity. Inspect the underlying text and database context before combining results into a conclusion.

The worker and elapsed counters here are reported in microseconds, so the query converts them to milliseconds. Execution count belongs to the cached statistics being summarized. Keep units visible. A misplaced factor of a thousand can make an ordinary query look like a server-wide emergency.

Compare Totals With Per-Execution Cost

A moderately expensive statement executed repeatedly can consume more CPU than one dramatic outlier. That is why I sort by total resource use first. Then I look at the frequency and the per-execution average. Each tells a different part of the story.

SELECT TOP (20)
    query_hash,
    SUM(execution_count) AS executions,
    SUM(total_worker_time) / 1000.0 AS total_cpu_ms,
    SUM(total_worker_time) / 1000.0
        / NULLIF(SUM(execution_count), 0) AS average_cpu_ms,
    SUM(total_logical_reads) * 1.0
        / NULLIF(SUM(execution_count), 0) AS average_logical_reads
FROM sys.dm_exec_query_stats
GROUP BY query_hash
ORDER BY SUM(total_logical_reads) DESC;

Calculate a weighted average from the totals rather than averaging averages across plans. Otherwise a rarely executed plan can influence the result as much as a busy one. Still keep the individual plans available. An average can hide a mix of cheap and expensive executions.

Inspect the Statement Behind the Number

Once you identify a query hash, inspect its constituent statements and plans. The following returns the busiest cached statements by CPU. Restrict it to the selected hash when following one candidate. The text extraction uses the statement offsets within the containing batch.

SELECT TOP (10)
    qs.query_hash, qs.plan_handle,
    qs.execution_count,
    SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,
        (CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.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 t
ORDER BY qs.total_worker_time DESC;

Keep captured SQL in an approved location because literals can expose business data. Check whether the request belongs to a scheduled process or an interactive action. A costly overnight load and a delayed checkout have different consequences even when their CPU totals match.

Don’t Confuse Duration With Resource Demand

Elapsed duration includes waiting. A blocked statement can be the longest-running request while consuming little CPU. Conversely, parallel work can accumulate CPU across workers. Don’t subtract totals casually and call the remainder a precise explanation of every wait.

The slowest query still matters when it violates an important response requirement. Aggregate impact isn’t the only priority. Keep user impact beside resource use and frequency. The goal is choosing the most useful improvement, not defending one sorting column as the universal answer.

For a real capture, group by meaningful context as well as query shape. Separate databases or applications where necessary. Check that overlapping files or repeated imports haven’t counted the same event twice. A tidy total built from duplicate events remains wrong.

Measure the Candidate Again

Choose a candidate, preserve its evidence, and test a specific change on representative data. Compare a similar workload window afterward. A quiet hour after a busy hour isn’t a fair before-and-after test. Record parameter distribution and concurrency when they influence the result.

Use Query Store or a designed capture when you need durable history. The plan cache is convenient but forgetful. I keep the analysis query with its output so another person can see the grouping and filters. That makes the conclusion reviewable without installing a special dashboard.

A workload capture is not a contest for the slowest statement, it is a record of where repeated work adds up.

This post was rewritten from scratch in September 2026. The original, published on 2014-12-13, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.