The slowest single query is not always the biggest problem. Top resource consumers in Query Store reveal which statements consume the most work across a real time window.
Start with the Built-In Report
SSMS includes a Top Resource Consuming Queries report under Query Store for a database. Choose a metric and time interval that matches the complaint. A ranking by maximum duration answers a different question from a ranking by total CPU. Open the plan history for a high-impact query before deciding on a change.
I start with total impact. One query that runs slowly once a month can matter less than a modest query called constantly. The report helps you see both frequency and cost. Ask what resource the users actually lack: CPU, time, reads, or memory. Then choose the metric instead of opening every chart and hoping one looks dramatic.
Check Query Store State
Query Store must be collecting useful runtime data for the report to help. Inspect its actual and desired state in the database. A read-only state can occur because of a size limit or other condition. Check retention and capture settings when expected queries are missing. Do not treat an empty report as evidence that the database has no costly work.
The query reads the current database’s Query Store options. I keep it with the report capture so another DBA knows whether the history was complete. A plan cache query can supplement a gap, but it has a shorter and different retention model.
SELECT desired_state_desc,
actual_state_desc,
readonly_reason,
current_storage_size_mb,
max_storage_size_mb
FROM sys.database_query_store_options;Understand the DMV Join
Query Store links query text to a query, a query to one or more plans, and plans to runtime statistics in time intervals. Joining only text and plan can duplicate rows when several intervals exist. Aggregation must respect plan and interval scope. Runtime values such as avg_duration and avg_cpu_time are reported in microseconds.
I draw the join once before writing a ranking query. It avoids the common error of adding averages as though they were totals. Multiply each average by its execution count, then sum. The resulting number is an estimate of total work over the selected intervals, not a stopwatch recording of every individual call. The data comes from your instance and collection settings.
Rank Top Resource Consumers by Total CPU
This query joins the core Query Store views and weights average CPU by execution count. It limits the time window through runtime stats intervals. Run it in the target database. Review aborted executions separately if they matter to the incident. Query text can contain sensitive literals, so protect exports.
I look at the plan count beside total CPU. A query with one bad new plan can be a regression candidate. A query that is consistently expensive can need indexing or rewrite work. Ranking only by average hides frequency. Ranking only by executions hides per-call cost.
SELECT TOP (20)
q.query_id,
SUM(rs.count_executions) AS executions,
SUM(rs.avg_cpu_time * rs.count_executions) / 1000000.0 AS total_cpu_seconds,
MIN(CONVERT(nvarchar(4000), qt.query_sql_text)) AS sample_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.start_time >= DATEADD(day, -1, SYSDATETIMEOFFSET())
GROUP BY q.query_id
ORDER BY total_cpu_seconds DESC;
Compare Top Resource Consumers by Duration and Reads
A query can spend a long time waiting while using little CPU. Another can finish quickly but read a great deal. Use duration, logical reads, and physical reads to understand the pressure. A report screen that ranks by one metric should not be treated as a universal priority list. Correlate with user complaints and server waits.
I investigate a high-duration query for blocking before changing indexes. If it waited behind a transaction, its own plan can be fine. A high-read query can benefit from a narrower predicate or index, but check how frequently it runs. The best first fix frequently comes from total workload cost, not the most colorful bar.
Look for Plan Regressions
Query Store retains multiple plans for a query when plans change. Compare runtime intervals before and after a release, statistics update, or configuration change. A plan that became slower for representative executions deserves a closer look. Plan forcing can be a short-term control when supported, but it needs monitoring and a root-cause review.
I do not force the oldest plan simply because it was once fast. Data distribution can change. Test the plan under current parameters and check Query Store’s forcing status. The report is a map of history. The decision should reflect present workload. A forced plan without an owner can outlive the reason it helped.
Choose One of the Top Resource Consumers to Fix
Take the top few resource consumers and score them by total resource use, user impact, and change risk. A simple query that runs thousands of times can be easier to improve than a complex monthly report. Look at the actual plan, estimates, predicate, indexes, and application call pattern. Decide whether the right fix is query code, schema, statistics, or scheduling.
I write a one-sentence hypothesis before changing anything. For example, a broad scan repeats on every request because a filter lacks a supporting index. Then I test that exact change. The report’s rank does not prescribe a fix. It tells you where a fix can repay the effort.
Watch Query Store Overhead and Retention
Query Store uses database storage and retention rules. Keep its size and cleanup settings appropriate for the workload. If it becomes read-only, the history can stop reflecting current activity. Review capture mode so tiny one-off statements do not overwhelm the useful signal. Protect the Query Store data because text can expose sensitive values.
I include collection health in the monitoring plan. A report that quietly stops collecting can still display old charts, which is especially misleading. Check actual state and latest intervals when using it for a new incident. Fresh data matters more than a polished report window.
Verify the Tuning Result
After a change, compare the same Query Store metric over comparable time windows and parameter mix. Check plan shape and actual resource use. A lower average for one plan can be outweighed by higher call volume or a worse plan for another value. Keep the application symptom in view.
Which query consumes the most resource that your team can realistically reduce? Answer that from a measured window, not from a single slow screenshot. Query Store earns its place when it helps you choose one high-impact, testable improvement and shows whether it lasted.
Related reading on this blog: Using Query Store to Prove an Upgrade Did Not Hurt and SSMS: Top Queries by CPU and IO.
A top Query Store ranking is not a tuning command, it is a way to choose where measured work pays off.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
