You know the query slowed down; the duration chart does not explain why. Query Store wait stats add the captured wait categories that guide the next investigation.
Check Query Store Wait Stats Capture Before Reading an Empty Result
Query-level wait capture is available in SQL Server 2017 and later. The examples that include replica_group_id target SQL Server 2022 and later, where that field is available. Verify the database's actual Query Store state and wait-capture mode before reading its history.
SELECT actual_state_desc,desired_state_desc,readonly_reason,
wait_stats_capture_mode_desc,interval_length_minutes,
current_storage_size_mb
FROM sys.database_query_store_options;A desired read-write state does not guarantee that capture is currently read-write. Storage limits or another documented condition can prevent new records. An empty result can also reflect capture policy, retention, or a query outside the captured population. I check coverage before interpreting the absence of a wait row as an absence of waiting.
A reviewed enablement command for an existing disposable database follows. Confirm resource policy and retention separately before applying a production configuration change. Enabling capture now cannot recreate evidence from an earlier period.
ALTER DATABASE QueryStoreLab SET QUERY_STORE=ON
(OPERATION_MODE=READ_WRITE,WAIT_STATS_CAPTURE_MODE=ON);Run the later queries in the intended database. Keep the capture settings, start of usable history, and any collection gaps with the investigation. A chart that has lost its input window cannot explain that missing period with a confident zero.
Aggregate Query Store Wait Stats at Their Documented Grain
Wait rows belong to a plan, runtime interval, execution type, and wait category. The active interval can have multiple rows representing persisted and in-memory state. Aggregate them rather than selecting one row arbitrarily. Replica-aware history needs its capture-source identity retained too.
SELECT w.replica_group_id,w.plan_id,w.runtime_stats_interval_id,
i.start_time,i.end_time,w.execution_type,w.wait_category,
w.wait_category_desc,
SUM(w.total_query_wait_time_ms) AS TotalWaitMS
FROM sys.query_store_wait_stats AS w
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id=w.runtime_stats_interval_id
GROUP BY w.replica_group_id,w.plan_id,w.runtime_stats_interval_id,
i.start_time,i.end_time,w.execution_type,
w.wait_category,w.wait_category_desc;Regular, client-aborted, and exception-aborted executions describe different populations. Preserve that distinction in the inventory, then choose the population appropriate to the question. An investigation of failed requests should not silently filter them out because successful execution is the easiest baseline to calculate.
The grouped totals are wait time across the represented executions, not one request's wall-clock duration. Parallel tasks and aggregated activity complicate that relationship. Do not add categories and claim the sum must equal elapsed time. Keep duration, CPU, execution count, and wait evidence as related but distinct measurements.
Find the Queries With the Largest Captured Waits
Select a defined observation window and aggregate before joining readable query text. The following example uses fully contained completed intervals within the preceding day. It reports regular executions and preserves replica and plan identities.
DECLARE @EndUTC datetimeoffset=TODATETIMEOFFSET(SYSUTCDATETIME(),'+00:00');
DECLARE @StartUTC datetimeoffset=DATEADD(day,-1,@EndUTC);
WITH WaitTotals AS
(
SELECT w.replica_group_id,w.plan_id,w.wait_category,w.wait_category_desc,
SUM(w.total_query_wait_time_ms) AS TotalWaitMS
FROM sys.query_store_wait_stats AS w
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id=w.runtime_stats_interval_id
WHERE i.start_time>=@StartUTC AND i.end_time<=@EndUTC
AND w.execution_type=0
GROUP BY w.replica_group_id,w.plan_id,w.wait_category,w.wait_category_desc
)
SELECT TOP (30) q.query_id,p.plan_id,w.replica_group_id,
w.wait_category_desc,w.TotalWaitMS,qt.query_sql_text
FROM WaitTotals 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 qt ON qt.query_text_id=q.query_text_id
ORDER BY w.TotalWaitMS DESC,q.query_id,p.plan_id;A high total can reflect frequent execution rather than an unusually slow individual call. Check counts and per-execution behavior before prioritizing the work. Query text can contain sensitive information, so retain the result under the approved diagnostic access policy. Do not export the entire captured workload merely to investigate one query.
Different plans for the same query can have different categories and operating behavior. Keep plan_id in the first review instead of collapsing it immediately. A changed plan and a changed workload are separate possible explanations. The useful next step depends on which one the evidence supports.
Map Categories to the Relevant Investigation
The category summarizes several related wait types rather than preserving every individual type in this view. Lock includes LCK_M waits and suggests reviewing blocking and transaction scope. Buffer IO includes PAGEIOLATCH waits and suggests examining the access path and storage behavior. Buffer Latch points toward in-memory page contention, which is a different question.
CPU includes scheduler-yield behavior, while Parallelism groups exchange and coordination waits. Neither category automatically proves that a server setting should change. Network IO can involve a slow consuming client or an unnecessarily large result. Follow the category with the relevant plan, live diagnostics, and application evidence.
Use the documented wait-category mapping for the installed feature rather than constructing an invented one-to-one relationship. Query Store wait stats provide a direction for investigation, not the complete individual-wait trace. Capture targeted live or event evidence when the precise resource or wait type is required.
Compare Matched Windows Around a Change
Choose non-overlapping before and after windows with comparable workload and complete intervals. The illustrative times below are inputs to replace with the actual reviewed change boundaries. The query aggregates wait totals for one selected query; it does not invent a measured improvement.
DECLARE @QueryID bigint=1;
DECLARE @Windows TABLE(WindowLabel varchar(10),StartUTC datetimeoffset,EndUTC datetimeoffset);
INSERT @Windows VALUES
('Before','2026-09-01T08:00:00+00:00','2026-09-01T09:00:00+00:00'),
('After','2026-09-01T10:00:00+00:00','2026-09-01T11:00:00+00:00');
SELECT x.WindowLabel,w.replica_group_id,p.query_id,w.plan_id,
w.wait_category_desc,SUM(w.total_query_wait_time_ms) AS TotalWaitMS
FROM @Windows AS x
JOIN sys.query_store_runtime_stats_interval AS i
ON i.start_time>=x.StartUTC AND i.end_time<=x.EndUTC
JOIN sys.query_store_wait_stats AS w
ON w.runtime_stats_interval_id=i.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id=w.plan_id
WHERE p.query_id=@QueryID AND w.execution_type=0
GROUP BY x.WindowLabel,w.replica_group_id,p.query_id,w.plan_id,w.wait_category_desc;
SELECT x.WindowLabel,r.replica_group_id,p.query_id,r.plan_id,
SUM(r.count_executions) AS CompletedExecutions,
SUM(r.avg_duration*r.count_executions)
/NULLIF(SUM(CONVERT(float,r.count_executions)),0) AS WeightedDurationUS
FROM @Windows AS x
JOIN sys.query_store_runtime_stats_interval AS i
ON i.start_time>=x.StartUTC AND i.end_time<=x.EndUTC
JOIN sys.query_store_runtime_stats AS r
ON r.runtime_stats_interval_id=i.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id=r.plan_id
WHERE p.query_id=@QueryID AND r.execution_type=0
GROUP BY x.WindowLabel,r.replica_group_id,p.query_id,r.plan_id;Select the real query identity and retain each plan and replica group. Compare total wait with the corresponding completed execution count and weighted duration. Runtime duration uses microseconds, while wait totals use milliseconds, so convert deliberately for any display. Do not join raw category rows directly to raw runtime rows and multiply the measurements.
Which parameter mix or request volume changed between those windows? Check that alongside the deployment. Equal window length does not guarantee equal work. I compare the query's input shape and execution population before attributing every lower total to the code change.
Validate the Category-Specific Correction
A locking change needs evidence about transaction behavior and conflicting access. An I/O change needs plan and read evidence. A client-consumption change needs end-to-end timing. Recheck the accepted result contract and service latency after the targeted correction instead of stopping when one wait category falls.
Keep missing intervals, changed capture settings, and retention cleanup visible. Incomplete coverage can make the after window look artificially quiet. Preserve the selected windows and query identities with the comparison so another reviewer can repeat it against the same evidence.
Keep Query Store Wait Stats Connected to the Query
Retain the plan, category, interval, replica scope, execution outcome, and application context together. Avoid turning a high category into a universal instruction to change parallelism, memory, or storage. The query and workload determine which follow-up is justified.
Query Store wait stats are useful because they connect captured waiting to the statements and plans involved. Use that connection to choose the next diagnostic step, then validate the complete service result. A category chart is strongest when it leads to an explained and measured correction.
Related reading on this blog: Top 3 Wait Stats from Real-World and Wait Statistics from Query Execution Plan.
A wait category is not the whole cause of a slow query, it is captured evidence that guides the next focused investigation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
