SQL Server Query Store Wait Statistics by Category

Purpose

Identifies which wait categories are responsible for the execution time of a specific query over a defined interval. Useful when a query has regressed and you need to understand whether the cause is I/O, CPU pressure, lock contention, memory grants, or another wait type — rather than guessing from execution time alone.

The script joins sys.query_store_wait_stats with the Query Store runtime interval and plan/query tables to return per-category wait totals for a single query_id within a time window you supply.

Supported Platforms and Versions

SQL Server 2017 and later (on-premises and SQL Server on Azure VMs).

sys.query_store_wait_stats was introduced in SQL Server 2017. The script will not run on SQL Server 2016.

Azure SQL Database is supported — Query Store is enabled by default and sys.query_store_wait_stats is available.

Required Permissions

VIEW DATABASE STATE on the target database.

Preconditions

Query Store must be enabled on the target database and must have been collecting data during the time window you supply:

-- Verify Query Store is enabled and check collection mode
SELECT name, is_query_store_on, query_store_flush_interval_seconds
FROM sys.databases
WHERE name = DB_NAME();

SELECT actual_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

actual_state_desc must be READ_WRITE for data to be collected. If it shows READ_ONLY, Query Store has hit its storage limit and stopped collecting — investigate and expand the size limit before relying on the results.

You also need to know the query_id of the query you are investigating. Locate it from Query Store views or from a previous health check:

-- Find query_id by partial text match
SELECT TOP 20
    qsq.query_id,
    qsqt.query_sql_text,
    qsq.last_execution_time
FROM sys.query_store_query      qsq
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE N'%your_table_or_keyword%'
ORDER BY qsq.last_execution_time DESC;

Replace 1 in the script with the actual query_id before running.

Risk Level

Low. Read-only. Queries system catalogue views only. No changes to Query Store configuration, query plans, or data.

Script

-- ============================================================
-- SQL Server Query Store Wait Statistics by Category
-- Permissions : VIEW DATABASE STATE
-- Risk        : Read-only
-- Precondition: Query Store enabled; replace query_id value
-- ============================================================

DECLARE @StartTime datetime2 = '2026-02-26 12:30:00';  -- adjust to your window
DECLARE @EndTime   datetime2 = '2026-02-26 13:30:00';  -- adjust to your window

SELECT
    qsws.wait_category_desc,
    SUM(qsws.total_query_wait_time_ms)                          AS total_wait_ms,
    SUM(qsws.total_query_wait_time_ms)
        / NULLIF(SUM(qsws.total_query_wait_time_ms
            / NULLIF(qsws.avg_query_wait_time_ms, 0)), 0)       AS avg_wait_ms,
    MAX(qsws.max_query_wait_time_ms)                            AS max_wait_ms,
    COUNT(DISTINCT qsq.query_id)                                AS distinct_queries_affected
FROM sys.query_store_wait_stats             qsws
JOIN sys.query_store_runtime_stats_interval qsrsi
    ON qsws.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
JOIN sys.query_store_plan                   qsp  ON qsws.plan_id  = qsp.plan_id
JOIN sys.query_store_query                  qsq  ON qsp.query_id  = qsq.query_id
WHERE qsrsi.start_time >= @StartTime
  AND qsrsi.end_time   <= @EndTime
  AND qsq.query_id = 1                                          -- replace with target query_id
GROUP BY qsws.wait_category_desc
ORDER BY total_wait_ms DESC;

Safe Execution Guidance

1. Set @StartTime and @EndTime to the interval of interest — for example, the window of a known performance incident or a deployment.
2. Replace qsq.query_id = 1 with the actual query_id of the query you are investigating.
3. Run in the context of the target database (USE [YourDatabase]).
4. The interval resolution is controlled by Query Store’s INTERVAL_LENGTH_MINUTES setting (default 60 minutes). If your window is shorter than one interval, results may span a wider period than expected.

Expected Output

One row per wait category observed for the target query during the window. Result set ordered by total_wait_ms descending — the top row is the dominant wait type.

An empty result set means no wait data was recorded for this query in the interval — either the query did not execute, Query Store was not collecting, or the interval boundaries do not align with a stored interval.

Interpreting Results

• CPU at the top: The query is compute-bound. Investigate execution plan for excessive sorts, hash joins, or scans on large tables.
• Buffer IO at the top: Physical reads are dominating. Look for missing indexes, stale statistics, or parameter sniffing causing a plan to scan instead of seek.
• Lock at the top: Lock contention is the bottleneck. Review blocking chains using sys.dm_exec_requests during the window, or look for deadlock graphs in the system health session.
• Memory at the top: Memory grant issues — the query is waiting for a workspace memory grant. Check for large sorts or hash operations and review OPTION (RECOMPILE) or memory grant hints.
• Network IO: Results are being sent to the client faster than the client is consuming them. Usually an application-side concern.

Compare the same query across two time windows (before and after a change) to isolate which wait category shifted.

Rollback Steps

Not applicable — read-only script.

Official References

• sys.query_store_wait_stats
• sys.query_store_runtime_stats_interval
• Monitoring performance by using the Query Store