Grouping by query_hash to Find Cheap Queries That Cost the Most

Thousands of fast lookups can keep a server surprisingly busy. Grouping by query_hash brings similar statement shapes together so their total work becomes visible. Execution count belongs beside average cost.

Look Beyond the Slowest Single Execution

A server can stay busy while every individual request looks harmless. A screen refresh calls the same lookup repeatedly. A background process checks one row at a time. Each call finishes quickly, but the combined CPU and page reads occupy a large part of the workload.

I rank accumulated work before choosing a tuning target. A small average duration can hide a large execution count. The fastest call in the application still costs something when multiplied. The plan cache provides a useful starting view of that multiplication, with clear limits on the history it retains.

Query hashes group statements with similar bound logical shapes. Literal differences can share a hash, while context and statement differences can produce separate entries. A hash is a grouping clue, not a permanent business identifier. Read sample text and database context before deciding that every member belongs to the same application operation.

Aggregate the Cached Counters by query_hash

Capture the statement statistics into a temporary table. This gives subsequent reports the same snapshot instead of rereading a changing cache. Worker time is CPU time measured in microseconds. Logical reads count page accesses. Execution count describes completed executions represented by the cached entries.

SELECT query_hash, query_plan_hash, sql_handle,
       statement_start_offset, statement_end_offset,
       execution_count, total_worker_time, total_logical_reads,
       creation_time, last_execution_time
INTO #QueryHashSnapshot
FROM sys.dm_exec_query_stats;
SELECT TOP (20) query_hash,
       SUM(execution_count) AS Executions,
       SUM(total_worker_time)/1000000.0 AS TotalCpuSeconds,
       SUM(total_logical_reads) AS TotalLogicalReads,
       SUM(total_worker_time)*1.0/
           NULLIF(SUM(execution_count),0)/1000 AS AverageCpuMs,
       COUNT(DISTINCT query_plan_hash) AS DistinctPlanHashes
FROM #QueryHashSnapshot
GROUP BY query_hash
ORDER BY SUM(total_worker_time) DESC;

The average is weighted by execution count. Averaging the per-entry averages would give a rarely executed plan equal influence. Include both total and average because they answer different questions. A high total identifies workload importance; a high average identifies expensive individual executions.

On SQL Server 2022 and later, these server performance views require the appropriate performance-state permission. Earlier releases use the older server-state permission model. Have the administrator grant the minimum necessary access rather than broadening a login merely to run an inventory query.

Retrieve One Representative Statement

Use a statement's byte offsets to extract its text from the containing batch. Unicode text stores two bytes per code unit, so the conversion divides offsets by two. An end offset of minus one means the end of the batch. Returning the whole batch can conceal which statement the statistics describe.

WITH Ranked AS
(
    SELECT *, ROW_NUMBER() OVER
        (PARTITION BY query_hash ORDER BY total_worker_time DESC,
         last_execution_time DESC) AS rn
    FROM #QueryHashSnapshot
)
SELECT TOP (20) r.query_hash, r.query_plan_hash,
       SUBSTRING(t.text, r.statement_start_offset/2+1,
         ((CASE r.statement_end_offset
             WHEN -1 THEN DATALENGTH(t.text)
             ELSE r.statement_end_offset END
           - r.statement_start_offset)/2)+1) AS SampleStatement,
       t.dbid, r.execution_count
FROM Ranked r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.rn = 1
ORDER BY r.total_worker_time DESC;

This sample is chosen for its cached CPU contribution. It does not promise to represent every literal or parameter distribution. The cache can evict text after the snapshot, leaving an empty sample. Preserve that absence rather than substituting an unrelated batch and calling it the same statement.

Compare the Plans Behind Each query_hash

Distinct query_plan_hash values indicate different physical plan shapes represented by the snapshot. They do not measure whether a plan is good. A statement can need different plans for different selectivity. One plan hash can also cover executions with very different costs.

SELECT query_hash, query_plan_hash,
       SUM(execution_count) AS Executions,
       SUM(total_worker_time)/1000000.0 AS CpuSeconds,
       SUM(total_logical_reads) AS LogicalReads
FROM #QueryHashSnapshot
GROUP BY query_hash, query_plan_hash
ORDER BY SUM(total_worker_time) DESC;

Review estimates, access paths, and parameter behavior for the important group. Do not force a plan merely because several hashes appear. Also check whether the statement touches different databases or objects through different contexts. The grouping is deliberately broader than a single cached handle.

I verify the application call pattern before recommending another index. Does the application need every repeated lookup, or can one set-based request return the complete result? Eliminating unnecessary round trips can reduce work without changing the table's index inventory.

Use Query Store for Retained query_hash History

Query Store keeps database-scoped history across cache eviction, subject to its capture and retention settings. The following report uses a defined recent interval and regular completed executions. Average CPU and reads are multiplied by execution count before aggregation. That reconstructs totals from the interval statistics.

SELECT TOP (20) q.query_hash,
       SUM(rs.count_executions) AS Executions,
       SUM(rs.avg_cpu_time*rs.count_executions)/1000000.0 AS CpuSeconds,
       SUM(rs.avg_logical_io_reads*rs.count_executions) AS LogicalReads
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval ri
  ON ri.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE ri.end_time > DATEADD(day,-1,SYSDATETIMEOFFSET())
  AND rs.execution_type = 0
GROUP BY q.query_hash
ORDER BY SUM(rs.avg_cpu_time*rs.count_executions) DESC;

The selected intervals can overlap the requested boundary, so this is an interval-based report rather than an exact rolling-second account. Multiple active-interval rows are valid and contribute to the totals. Query Store settings still determine which queries were captured and retained. Run it in the application database, because a new test database returns no rows, as mine did.

Interpret the Missing Work

Cached counters restart when an entry is compiled again and disappear when it is evicted. Entries also have different lifetimes. A single snapshot therefore cannot claim a complete server-wide daily total. Record creation times and capture time, or take controlled deltas for a meaningful comparison.

Long-running requests are not fully represented until their executions complete. Ad hoc workloads can also churn through the cache. Use Query Store and application measurements to investigate those gaps. Then choose between fewer calls, better batching, a query rewrite, or an index supported by the actual plan. The busiest shape deserves investigation, not an automatic schema change.

Check the Business Operation Behind Repetition

Once a query-hash group is important, trace where its calls originate. A list page can issue one lookup for every displayed row. A polling loop can request unchanged state repeatedly. A report can ask the database for the same reference values during each iteration. These patterns need different fixes even when their cached SQL shapes look similar.

Use a controlled application test to count calls per business operation. Compare the count before and after a proposed batching change. Keep the returned rows, ordering, and error behavior equivalent. Replacing many lookups with one large request is useful only when the application still handles missing rows and partial failures correctly.

Also distinguish CPU from elapsed duration. Cached worker time adds CPU consumed by workers, including parallel workers. It is not a stopwatch for the user's request. A low CPU query can still wait on locks or storage. A high logical-read total describes repeated page access, including cached pages, rather than a direct disk-read count. Put each counter beside the question it answers. This stops an appealing ranking from becoming a misleading explanation of every kind of server slowness.

Treat query_hash as a grouping aid rather than an application identifier. Preserve sample text and context beside query_hash so another reader can verify which statement shapes were combined.

Related reading on this blog: List Expensive Queries: Updated March 2021 and Top Resource Consumers in Query Store.

A low average query cost is not a low workload cost, it is one small expense multiplied by the number of executions.

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