SQL Server Query Store Plan and Wait Analysis

Purpose

Produces a ranked top-20 view of the costliest query plans in a time window, combining runtime duration metrics with wait category totals in a single result set. Eliminates the need to cross-reference multiple Query Store views manually during an investigation.

Each row represents one query plan and includes:

• Execution count, average/min/max/total duration
• Total and average wait time per execution
• A wait_breakdown column listing every wait category and its millisecond total for that plan, ordered heaviest first
• The clickable XML execution plan

The script is built in three CTEs:

1. runtime_agg — aggregates sys.query_store_runtime_stats per plan over the window, converting microseconds to milliseconds
2. ws_by_category — aggregates sys.query_store_wait_stats per plan and wait category
3. ws_rollup — pivots wait categories into a single STRING_AGG breakdown column per plan

Results are ordered by total_duration_ms by default. Swap the ORDER BY to total_wait_ms or avg_wait_ms_per_exec to rank by wait time instead.

Supported Platforms and Versions

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

sys.query_store_wait_stats and STRING_AGG are both SQL Server 2017 additions. 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 collecting data during the time window:

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. If READ_ONLY, Query Store has hit its storage limit — expand it before relying on the results.

Note that Query Store wait categories are broader than sys.dm_os_wait_stats types. The wait_category_desc column reports approximately 23 high-level buckets (e.g., Buffer IO, Lock, Memory) rather than granular wait names like PAGEIOLATCH_SH. Use this script for category-level triage; use sys.dm_os_wait_stats for granular wait-type analysis.

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 Plan and Wait Analysis
-- Permissions : VIEW DATABASE STATE
-- Risk        : Read-only
-- Precondition: Query Store enabled; SQL Server 2017+
-- ============================================================

DECLARE @StartTime datetime2 = '2026-05-20 12:00:00';  -- adjust to your window
DECLARE @EndTime   datetime2 = '2026-05-26 14:30:00';  -- adjust to your window

/* ------------------------------------------------------------------
   1. Runtime stats per plan — durations converted from microseconds
      to milliseconds.
   ------------------------------------------------------------------ */
WITH runtime_agg AS
(
    SELECT
        q.query_id,
        p.plan_id,
        q.object_id,
        qt.query_sql_text,
        p.query_plan,
        SUM(rs.count_executions)                                    AS executions,
        SUM(rs.avg_duration * 1.0 * rs.count_executions)
            / NULLIF(SUM(rs.count_executions), 0) / 1000.0          AS avg_duration_ms,
        MIN(rs.min_duration) * 1.0 / 1000.0                         AS min_duration_ms,
        MAX(rs.max_duration) * 1.0 / 1000.0                         AS max_duration_ms,
        SUM(rs.avg_duration * 1.0 * rs.count_executions) / 1000.0   AS total_duration_ms,
        MAX(rs.last_execution_time)                                  AS last_execution_time,
        MAX(rsi.end_time)                                            AS last_interval
    FROM sys.query_store_runtime_stats          AS rs
    JOIN sys.query_store_plan                   AS p   ON rs.plan_id      = p.plan_id
    JOIN sys.query_store_query                  AS q   ON p.query_id      = q.query_id
    JOIN sys.query_store_query_text             AS qt  ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_runtime_stats_interval AS rsi ON rs.runtime_stats_interval_id
                                                        = rsi.runtime_stats_interval_id
    WHERE rsi.start_time < @EndTime
      AND rsi.end_time   > @StartTime
    GROUP BY q.query_id, p.plan_id, q.object_id, qt.query_sql_text, p.query_plan
),
/* ------------------------------------------------------------------
   2. Wait stats per plan and category — already in milliseconds.
      Query Store buckets waits into ~23 categories (CPU, Lock,
      Buffer IO, Latch, Memory, ...) — not granular wait type names.
   ------------------------------------------------------------------ */
ws_by_category AS
(
    SELECT
        ws.plan_id,
        ws.wait_category_desc,
        SUM(ws.total_query_wait_time_ms)    AS category_wait_ms,
        MAX(ws.max_query_wait_time_ms)      AS category_max_wait_ms
    FROM sys.query_store_wait_stats             AS ws
    JOIN sys.query_store_runtime_stats_interval AS wsi ON ws.runtime_stats_interval_id
                                                        = wsi.runtime_stats_interval_id
    WHERE wsi.start_time < @EndTime
      AND wsi.end_time   > @StartTime
    GROUP BY ws.plan_id, ws.wait_category_desc
),
/* ------------------------------------------------------------------
   3. Roll categories into one row per plan with a readable breakdown.
   ------------------------------------------------------------------ */
ws_rollup AS
(
    SELECT
        plan_id,
        SUM(category_wait_ms)   AS total_wait_ms,
        MAX(category_max_wait_ms) AS max_wait_ms,
        STRING_AGG(
            CONCAT(wait_category_desc, ' (',
                   CAST(CAST(category_wait_ms AS bigint) AS varchar(20)),
                   ' ms)'),
            ' | ')
            WITHIN GROUP (ORDER BY category_wait_ms DESC) AS wait_breakdown
    FROM ws_by_category
    GROUP BY plan_id
)
SELECT TOP (20)
    DB_NAME()                                       AS database_name,
    OBJECT_SCHEMA_NAME(ra.object_id, DB_ID())       AS schema_name,
    OBJECT_NAME(ra.object_id, DB_ID())              AS object_name,
    ra.query_id,
    ra.plan_id,
    ra.query_sql_text,
    TRY_CONVERT(xml, ra.query_plan)                 AS query_plan_xml,
    ra.executions,
    ra.avg_duration_ms,
    ra.min_duration_ms,
    ra.max_duration_ms,
    ra.total_duration_ms,
    ISNULL(ws.total_wait_ms, 0)                     AS total_wait_ms,
    ISNULL(ws.total_wait_ms, 0)
        / NULLIF(ra.executions, 0)                  AS avg_wait_ms_per_exec,
    ws.max_wait_ms,
    ws.wait_breakdown,
    ra.last_execution_time,
    ra.last_interval
FROM      runtime_agg AS ra
LEFT JOIN ws_rollup   AS ws ON ra.plan_id = ws.plan_id
ORDER BY  ra.total_duration_ms DESC;
-- swap ORDER BY to total_wait_ms or avg_wait_ms_per_exec to rank by wait instead

Safe Execution Guidance

1. Set @StartTime and @EndTime to the window of interest. The CTE uses < @EndTime and > @StartTime (open interval) to capture intervals that overlap the window rather than requiring them to be fully contained within it.
2. Run in the context of the target database (USE [YourDatabase]).
3. Click the query_plan_xml cell in SSMS to open the graphical execution plan for any row.
4. Change TOP (20) to a larger value if you need broader coverage; reduce it on databases with high plan volume to keep response time acceptable.
5. Change the ORDER BY to total_wait_ms DESC or avg_wait_ms_per_exec DESC to surface queries that wait the most rather than run the longest.

Expected Output

Up to 20 rows, one per query plan, ordered by total execution time descending.

A NULL wait_breakdown means no wait data was recorded for that plan — either the plan executed entirely on CPU or Query Store did not capture wait stats for those intervals.

Interpreting Results

High total_duration_ms, low total_wait_ms: The query is CPU-bound. Time is spent on computation rather than waiting for resources. Review the execution plan for large scans, sorts, or hash joins.

High total_wait_ms relative to total_duration_ms: Most elapsed time is waiting for resources. Read wait_breakdown to identify the category and cross-reference with the appropriate investigation path.

wait_breakdown showing Buffer IO as the dominant category: The plan is generating physical reads. Look for missing indexes, stale statistics, or parameter sniffing forcing a scan.

wait_breakdown showing Lock as dominant: Lock contention is the primary cost. Identify blocking chains using sys.dm_exec_requests or deadlock graphs from the system health session.

Same query_id appearing under multiple plan_id values: The query ran under different plans during the window — plan instability. Compare the plans by clicking query_plan_xml on each row.

object_name populated: The query belongs to a stored procedure or function. The plan cost can be attributed to that object directly.

Use this script to rank and triage. Drill into individual queries using the wait statistics by category script once the target query_id is identified.

Rollback Steps

Not applicable — read-only script.

Official References

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