Users report a slow database, but the longest-looking statement is not automatically the main offender. Query Store helps find the worst query by adding up the work it actually consumed.
Confirm the Right Database Is Recording
Query Store belongs to a database. Open the database that serves the affected application before running these queries. A quiet store in the wrong database produces a very tidy explanation of somebody else's workload.
The examples assume recording is enabled. Check the actual state, desired state, capture mode, size, and interval length. READ_WRITE means new runtime information can be collected. READ_ONLY still exposes retained history, but the current complaint can fall outside that history. Investigate the state before interpreting missing entries.
I start here even when somebody says Query Store is definitely on. Being enabled last month does not prove it captured today's query. Capture policies, retention, cleanup, and storage limits affect coverage. Use an account with approved database monitoring permissions, including VIEW DATABASE PERFORMANCE STATE on recent SQL Server versions.
SELECT DB_NAME() AS DatabaseName,
desired_state_desc, actual_state_desc, readonly_reason,
query_capture_mode_desc, current_storage_size_mb,
max_storage_size_mb, interval_length_minutes
FROM sys.database_query_store_options;Collect the Last Day by Plan
Runtime statistics are stored in time intervals and attached to plans. Each query can have several plans. Multiply each recorded average by its execution count, then add those contributions. Simply summing averages gives a rare execution the same weight as a busy one.
The following block builds a temporary summary for intervals overlapping the last day. Query Store aggregates into buckets, so the boundary bucket can include earlier work. The report is an interval-based view, not an exact event log cut at the second. Its earliest and latest interval timestamps show that coverage.
Current intervals can contain multiple runtime rows for the same plan and execution type. Summing their counts and weighted averages handles those contributions together. This sample ranks successfully completed executions. Aborted and failed executions deserve a separate check, especially during a timeout complaint.
DECLARE @ToTime datetimeoffset =
TODATETIMEOFFSET(SYSUTCDATETIME(), '+00:00');
DECLARE @FromTime datetimeoffset = DATEADD(day, -1, @ToTime);
DROP TABLE IF EXISTS #PlanWork;
SELECT p.query_id, p.plan_id,
SUM(rs.count_executions) AS Executions,
SUM(CONVERT(float, rs.count_executions) * rs.avg_cpu_time) AS TotalCpuUs,
SUM(CONVERT(float, rs.count_executions) * rs.avg_duration) AS TotalDurationUs,
MIN(i.start_time) AS FirstIntervalStart,
MAX(i.end_time) AS LastIntervalEnd,
MAX(rs.last_execution_time) AS LastExecutionTime
INTO #PlanWork
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
WHERE i.end_time > @FromTime AND i.start_time < @ToTime
AND rs.execution_type = 0
GROUP BY p.query_id, p.plan_id;Rank the Worst Query by Total CPU First
For CPU pressure, start with accumulated CPU across the selected window. A cheap statement called constantly can consume more CPU than one expensive report. Execution count belongs beside the total so the reason for the ranking remains visible.
The next query rolls all plans up to query level and includes the text. Totals are estimates reconstructed from stored averages. The underlying CPU units are microseconds, so division by one million expresses seconds. The weighted average divides the accumulated CPU by the accumulated execution count.
;WITH QueryWork AS
(
SELECT query_id, SUM(Executions) AS Executions,
SUM(TotalCpuUs) AS TotalCpuUs,
SUM(TotalDurationUs) AS TotalDurationUs,
MIN(FirstIntervalStart) AS FirstIntervalStart,
MAX(LastIntervalEnd) AS LastIntervalEnd
FROM #PlanWork
GROUP BY query_id
)
SELECT TOP (20) w.query_id, w.Executions,
w.TotalCpuUs / 1000000.0 AS TotalCpuSeconds,
w.TotalCpuUs / NULLIF(w.Executions, 0) / 1000.0 AS AverageCpuMs,
w.FirstIntervalStart, w.LastIntervalEnd, t.query_sql_text
FROM QueryWork AS w
JOIN sys.query_store_query AS q ON q.query_id = w.query_id
JOIN sys.query_store_query_text AS t ON t.query_text_id = q.query_text_id
ORDER BY w.TotalCpuUs DESC, w.query_id;Duration Finds a Different Worst Query
Duration includes time spent waiting as well as executing. Rank total duration to identify the statements contributing the most accumulated elapsed work. A blocked query can rank here without leading the CPU list. Compare the two rankings rather than treating them as interchangeable.
Accumulated duration across concurrent executions exceeds wall-clock time without anything being wrong with the arithmetic. It is the sum of those executions' durations. CPU also spans multiple workers. Keep those meanings distinct when explaining why a statement deserves attention.
Which complaint are you fixing, exhausted CPU capacity or one request missing its response target? Total resource consumption is useful for capacity relief. A severe individual latency problem still deserves investigation even when its total ranks lower.
;WITH QueryWork AS
(
SELECT query_id, SUM(Executions) AS Executions,
SUM(TotalCpuUs) AS TotalCpuUs,
SUM(TotalDurationUs) AS TotalDurationUs
FROM #PlanWork
GROUP BY query_id
)
SELECT TOP (20) w.query_id, w.Executions,
w.TotalDurationUs / 1000000.0 AS TotalDurationSeconds,
w.TotalDurationUs / NULLIF(w.Executions, 0) / 1000.0 AS AverageDurationMs,
w.TotalCpuUs / 1000000.0 AS TotalCpuSeconds, t.query_sql_text
FROM QueryWork AS w
JOIN sys.query_store_query AS q ON q.query_id = w.query_id
JOIN sys.query_store_query_text AS t ON t.query_text_id = q.query_text_id
ORDER BY w.TotalDurationUs DESC, w.query_id;
Several Plans Need a Closer Look
Find queries with more than one plan active during the selected interval. The count is a clue, not a verdict. Plans change with compilation context, schema changes, and optimizer decisions. Recent versions also deliberately create dispatcher and variant plans for different parameter cases.
The following report counts distinct plan identifiers from the collected summary. Inspect each plan's work separately afterward. Do not average their averages. A lightly used slow plan and a heavily used fast plan deserve different weights when estimating the workload's total cost.
SELECT query_id, COUNT(DISTINCT plan_id) AS ActivePlanCount,
SUM(Executions) AS Executions,
SUM(TotalCpuUs) / 1000000.0 AS TotalCpuSeconds,
SUM(TotalDurationUs) / 1000000.0 AS TotalDurationSeconds
FROM #PlanWork
WHERE Executions > 0
GROUP BY query_id
HAVING COUNT(DISTINCT plan_id) > 1
ORDER BY TotalCpuSeconds DESC, query_id;Retrieve the Text and Stored Plan
The next block selects the CPU-leading query from this sample summary. Replace that selection with the chosen query_id when duration or application priority points elsewhere. Each returned plan stays paired with its measured contribution from the same interval selection.
The XML is a stored compiled plan. It does not contain the actual row counts from your next test execution. Use it to inspect access paths, joins, estimates, and expensive operations. Then reproduce the statement safely with representative parameters and an actual plan before choosing a fix.
DECLARE @QueryID bigint =
(
SELECT TOP (1) query_id
FROM #PlanWork
GROUP BY query_id
ORDER BY SUM(TotalCpuUs) DESC, query_id
);
SELECT p.query_id, p.plan_id, w.Executions,
w.TotalCpuUs / 1000000.0 AS TotalCpuSeconds,
w.TotalDurationUs / 1000000.0 AS TotalDurationSeconds,
p.is_forced_plan, p.force_failure_count,
p.last_force_failure_reason_desc,
t.query_sql_text, TRY_CONVERT(xml, p.query_plan) AS StoredPlanXml
FROM #PlanWork AS w
JOIN sys.query_store_plan AS p ON p.plan_id = w.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS t ON t.query_text_id = q.query_text_id
WHERE p.query_id = @QueryID
ORDER BY w.TotalCpuUs DESC, p.plan_id;Check What the Ranking Excluded
A successful-execution report does not explain every failed request. Check aborted and exception executions for the same interval window. Their counts reveal whether the investigation is missing a stream of work that ends unsuccessfully. Query text can contain sensitive literals, so keep evidence within the approved audience.
Also inspect the capture mode when an expected statement is absent. Ad hoc query variations can create many entries. A query_id identifies Query Store's recorded query context, not a complete business feature. Several entries can represent one application operation.
DECLARE @Cutoff datetimeoffset =
DATEADD(day, -1, TODATETIMEOFFSET(SYSUTCDATETIME(), '+00:00'));
SELECT rs.execution_type_desc, SUM(rs.count_executions) AS Executions
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.end_time > @Cutoff AND rs.execution_type <> 0
GROUP BY rs.execution_type_desc
ORDER BY Executions DESC;Turn the Worst Query Into One Defensible Target
Keep the business operation beside those identifiers. A scheduled report and a checkout request have different priorities even when their totals match. Verify that the proposed test uses the same parameter shape and comparable data. Then check whether improving this statement increases throughput or merely shifts waiting somewhere else. Plan forcing also needs its own justified decision and follow-up monitoring. The ranking alone supplies no reason to force whichever stored plan looks prettiest.
I save the query_id, plan_id, time coverage, totals, and execution counts before changing anything. That gives the follow-up comparison the same starting point. The first five minutes should identify a defensible target, not produce an untested rewrite or a promise about runtime.
Finding the worst query gets useful when you connect its cost to the complaint. Inspect the plan, confirm the relevant parameters, and test one change. Recollect comparable Query Store intervals afterward. Choosing the worst query by total work makes that first tuning effort count.
Related reading on this blog: Finding the Root Cause of Slow Queries and List Expensive Queries: Updated March 2021.
A tuning target is not the longest SQL text, it is the work that hurts the workload.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
