SQL Server Query Store Top Queries by Wait Category

Purpose

Surfaces the queries accumulating the most wait time across a configurable set of wait categories during a defined interval. Where the wait statistics by category script diagnoses a single known query, this script is for discovery — identifying which queries are responsible for contention when the culprit is not yet known.

The script joins sys.query_store_wait_stats with runtime stats, plan, query, and query text tables to return per-query, per-plan, per-category wait totals. The average wait per execution is derived from sys.query_store_runtime_stats execution counts, which is more accurate than deriving it from stored averages alone.

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 all referenced views are available.

Required Permissions

VIEW DATABASE STATE on the target database.

Preconditions

Query Store must be enabled and actively collecting data during the time window you supply:

-- Verify Query Store state and storage headroom
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. A READ_ONLY state means Query Store has hit its storage ceiling and stopped collecting — results will be incomplete or absent.

The interval resolution is controlled by the INTERVAL_LENGTH_MINUTES setting (default 60 minutes). Time window boundaries that fall within a single interval will return data for that full interval.

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 Top Queries by Wait Category
-- Permissions : VIEW DATABASE STATE
-- Risk        : Read-only
-- Precondition: Query Store enabled on target database
-- ============================================================

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
    qsq.query_id,
    qsp.plan_id,
    qsqt.query_sql_text,
    qsws.wait_category_desc,
    SUM(qsws.total_query_wait_time_ms)                          AS total_wait_ms,
    SUM(qsws.total_query_wait_time_ms)
        / NULLIF(SUM(qsrs.count_executions), 0)                AS avg_wait_ms,
    MAX(qsws.max_query_wait_time_ms)                            AS max_wait_ms,
    SUM(qsrs.count_executions)                                  AS total_executions,
    MIN(qsrsi.start_time)                                       AS interval_start,
    MAX(qsrsi.end_time)                                         AS interval_end
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
JOIN sys.query_store_query_text             qsqt
    ON  qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_runtime_stats          qsrs
    ON  qsws.plan_id                   = qsrs.plan_id
    AND qsws.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
    AND qsws.execution_type            = qsrs.execution_type
WHERE qsrsi.start_time >= @StartTime
  AND qsrsi.end_time   <= @EndTime
  AND qsws.wait_category_desc IN (
        'Lock',           -- blocking locks
        'Latch',          -- memory structure contention
        'Buffer Latch',   -- buffer pool contention
        'Buffer IO',      -- disk I/O waits
        'Network IO',     -- slow client consuming results
        'Parallelism',    -- parallel query coordination
        'Memory'          -- memory pressure / grants
        -- remove this filter entirely to see ALL wait categories
  )
GROUP BY
    qsq.query_id,
    qsp.plan_id,
    qsqt.query_sql_text,
    qsws.wait_category_desc
ORDER BY total_wait_ms DESC;

Safe Execution Guidance

1. Set @StartTime and @EndTime to the window of interest — for example, the duration of a reported slowdown or the period immediately after a deployment.
2. Run in the context of the target database (USE [YourDatabase]).
3. The wait_category_desc filter covers the most operationally significant wait types. Remove it entirely to see all categories, including CPU and Unknown.
4. query_sql_text is normalised by Query Store — literal values are replaced with parameter placeholders. The same query_id represents all executions of the same query structure regardless of parameter values.
5. A query appearing with multiple plan_id values used different plans during the interval — this is itself a finding worth investigating.

Expected Output

One row per unique combination of query_id, plan_id, and wait_category_desc, ordered by total_wait_ms descending. The top rows identify the queries responsible for the most contention in the window.

An empty result set means no wait data was recorded for the selected categories in the interval — verify Query Store state and confirm the interval boundaries align with stored data.

Interpreting Results

Multiple queries at the top with Lock: Broad lock contention — look for a long-running transaction or a table without appropriate indexes that is causing escalation.

One query dominating Buffer IO: That specific query is driving physical reads. Pull its execution plan and check for scans, missing indexes, or stale statistics.

High Parallelism across many queries: Parallel query coordination overhead is significant. Review MAXDOP settings and cost threshold for parallelism.

High Memory on a small number of queries: Those queries are waiting for workspace memory grants. Large sorts and hash joins are common causes — consider memory grant hints or index changes to reduce the sort requirement.

Same query_id with multiple plan_id values: Plan instability during the window. The query ran under different plans — one of which may be significantly worse. Use the plan_id to retrieve the plans from sys.query_store_plan for comparison.

Use this script first to identify the top offenders, then use the wait statistics by category script to drill into a specific query_id in detail.

Rollback Steps

Not applicable — read-only script.

Official References

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