Query Store Architecture, Capture Modes, Runtime Stats, Wait Stats, and Storage

Learning outcomes

ServiceHub receives a familiar incident report: “the same endpoint became slow after yesterday’s deployment, but the live plan cache no longer contains the bad plan.” A transient DMV snapshot cannot answer what happened before a cache eviction, restart, recompilation, or plan change. Query Store exists to retain database-scoped query text, plans, aggregated runtime statistics, and wait categories so a practitioner can compare behavior over time instead of reconstructing history from memory.

Lab bootstrap: isolate Query Store changes from ServiceHubLab

Query Store configuration is database state. The mandatory lab therefore uses ServiceHubQSLab, not the long-lived ServiceHubLab. The settings below are teaching values for a small disposable database, not universal production recommendations. A production quota, capture policy, retention period, and aggregation interval must be sized from workload volume and evidence.

USE master;GOIF DB_ID(N'ServiceHubQSLab') IS NULLBEGIN  CREATE DATABASE ServiceHubQSLab;END;GOALTER DATABASE ServiceHubQSLab SET COMPATIBILITY_LEVEL = 170;ALTER DATABASE ServiceHubQSLab SET QUERY_STORE = ON(  OPERATION_MODE = READ_WRITE,  QUERY_CAPTURE_MODE = AUTO,  WAIT_STATS_CAPTURE_MODE = ON,  INTERVAL_LENGTH_MINUTES = 5,  MAX_STORAGE_SIZE_MB = 100,  SIZE_BASED_CLEANUP_MODE = AUTO,  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 7));GOUSE ServiceHubQSLab;GOIF SCHEMA_ID(N'lab11') IS NULL EXEC(N'CREATE SCHEMA lab11 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab11.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab11.WorkOrderFact  (    work_order_id bigint IDENTITY(1,1) NOT NULL,    customer_id int NOT NULL,    region_code char(3) NOT NULL,    status varchar(12) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    amount decimal(12,2) NOT NULL,    notes varchar(200) NULL,    CONSTRAINT PK_lab11_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (40000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT lab11.WorkOrderFact(customer_id,region_code,status,priority,opened_at,amount,notes)  SELECT CASE WHEN n <= 24000 THEN 1 ELSE 2 + n % 3999 END,         CASE WHEN n <= 27000 THEN 'N01' WHEN n <= 35000 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 23 = 0 THEN 'ESCALATED' WHEN n % 5 = 0 THEN 'CLOSED' ELSE 'OPEN' END,         CASE WHEN n <= 27000 THEN 1 ELSE 5 END,         DATEADD(minute,n,'2026-01-01T00:00:00'),         CAST(20 + (n % 8000) / 10.0 AS decimal(12,2)),         CASE WHEN n % 41=0 THEN REPLICATE('x',180) END  FROM n;  CREATE INDEX IX_lab11_Customer    ON lab11.WorkOrderFact(customer_id)    INCLUDE(region_code,status,opened_at,amount);  CREATE INDEX IX_lab11_RegionStatus    ON lab11.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);END;GOCREATE OR ALTER PROCEDURE lab11.GetOrdersByCustomer  @customer_id intASBEGIN  SET NOCOUNT ON;  SELECT work_order_id,region_code,status,opened_at,amount  FROM lab11.WorkOrderFact  WHERE customer_id=@customer_id  ORDER BY opened_at,work_order_id;END;GO

1. The repository has several identities, not one “query row”

Query Store normalizes its model into related catalog views. sys.query_store_query_text stores captured statement text. sys.query_store_query identifies a logical captured query and records context such as object association and query hash. sys.query_store_plan stores plans that SQL Server generated for those queries. Runtime rows are aggregated by plan and by a configurable runtime statistics interval; they are not one row per execution.

This matters operationally. If a query has three plans across four runtime intervals, joining query → plan → runtime statistics can produce several rows. Summing averages directly is mathematically wrong. Use count_executions as the weight when producing a combined average, and keep time windows explicit.

USE ServiceHubQSLab;GOSELECT actual_state_desc,desired_state_desc,readonly_reason,       query_capture_mode_desc,wait_stats_capture_mode_desc,       interval_length_minutes,current_storage_size_mb,max_storage_size_mb,       size_based_cleanup_mode_desc,stale_query_threshold_daysFROM sys.database_query_store_options;GOSELECT DB_NAME() AS database_name,       compatibility_level,is_query_store_onFROM sys.databasesWHERE database_id=DB_ID();GO

For SQL Server 2022 and later, the Query Store catalog views used in this lesson generally require VIEW DATABASE PERFORMANCE STATE or a greater database permission for read-only diagnosis. Changing Query Store options or forcing behavior requires stronger database permissions; the course assumes an administrator-level account only on this disposable local database.

2. Capture mode is an admission policy, not a promise that every execution is stored

Query capture mode controls which statements are admitted to Query Store. ALL is broad, AUTO uses built-in significance criteria to avoid filling the store with low-value one-off statements, NONE stops capturing new queries while retaining existing history, and CUSTOM lets administrators specify capture-policy thresholds on supported versions. The right choice depends on troubleshooting goals and workload shape.

ServiceHub uses AUTO for the mandatory lab. Run a stable tagged workload enough times that it becomes observable; do not switch blindly to ALL just because one test statement is absent.

USE ServiceHubQSLab;GOEXEC lab11.GetOrdersByCustomer @customer_id=1;EXEC lab11.GetOrdersByCustomer @customer_id=2;EXEC lab11.GetOrdersByCustomer @customer_id=1;EXEC lab11.GetOrdersByCustomer @customer_id=17;EXEC lab11.GetOrdersByCustomer @customer_id=1;GOSELECT q.query_id,q.query_hash,q.object_id,q.count_compiles,       p.plan_id,p.plan_type_desc,p.is_forced_plan,       qt.query_sql_textFROM sys.query_store_query_text AS qtJOIN sys.query_store_query AS q ON q.query_text_id=qt.query_text_idJOIN sys.query_store_plan AS p ON p.query_id=q.query_idWHERE qt.query_sql_text LIKE N'%lab11.WorkOrderFact%customer_id=@customer_id%'ORDER BY q.query_id,p.plan_id;GO

Absence after only one tiny execution does not prove Query Store is broken under AUTO. Verify capture mode, execute a representative workload, and inspect state. Conversely, presence in Query Store proves the statement was captured; it does not prove current plan-cache residency or current blocking state.

3. Runtime and wait data are historical aggregates

sys.query_store_runtime_stats records aggregate metrics such as execution count, duration, CPU, logical I/O and row counts for a plan during a runtime interval. sys.query_store_runtime_stats_interval gives the interval boundaries. Query Store wait statistics aggregate waits into categories per plan and interval; they are useful for asking “what kind of waiting accompanied this plan over time?” rather than “what is this session waiting on right now?”

USE ServiceHubQSLab;GOSELECT q.query_id,p.plan_id,p.plan_type_desc,       rsi.start_time,rsi.end_time,       rs.count_executions,rs.avg_duration,rs.avg_cpu_time,       rs.avg_logical_io_reads,rs.avg_rowcountFROM sys.query_store_query_text AS qtJOIN sys.query_store_query AS q ON q.query_text_id=qt.query_text_idJOIN sys.query_store_plan AS p ON p.query_id=q.query_idJOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_idJOIN sys.query_store_runtime_stats_interval AS rsi  ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_idWHERE qt.query_sql_text LIKE N'%lab11.WorkOrderFact%customer_id=@customer_id%'ORDER BY rsi.start_time,p.plan_id;GOSELECT q.query_id,p.plan_id,rsi.start_time,       ws.wait_category_desc,ws.total_query_wait_time_ms,ws.avg_query_wait_time_msFROM sys.query_store_query_text AS qtJOIN sys.query_store_query AS q ON q.query_text_id=qt.query_text_idJOIN sys.query_store_plan AS p ON p.query_id=q.query_idJOIN sys.query_store_wait_stats AS ws ON ws.plan_id=p.plan_idJOIN sys.query_store_runtime_stats_interval AS rsi  ON rsi.runtime_stats_interval_id=ws.runtime_stats_interval_idWHERE qt.query_sql_text LIKE N'%lab11.WorkOrderFact%customer_id=@customer_id%'ORDER BY rsi.start_time,ws.total_query_wait_time_ms DESC;GO

4. Storage and cleanup are part of correctness of the evidence

Query Store data lives inside the user database and has a configured maximum size. If it reaches conditions that make it read-only, new evidence stops accumulating. desired_state_desc = READ_WRITE is therefore insufficient; check actual_state_desc, readonly_reason, current size, quota, and cleanup mode. Size-based cleanup can remove older or less valuable history to recover space.

The wrong reaction to a confusing store is “clear Query Store and start over.” A clear operation destroys the historical baseline you may need to explain the incident, and feedback mechanisms can depend on Query Store persistence. First export or record the relevant evidence, identify the exact problem—quota, capture policy, excessive ad hoc text, retention, or corruption/error state—and make the smallest change.

USE ServiceHubQSLab;GOSELECT actual_state_desc,readonly_reason,current_storage_size_mb,max_storage_size_mb,       flush_interval_seconds,interval_length_minutes,       size_based_cleanup_mode_desc,query_capture_mode_descFROM sys.database_query_store_options;GOSELECT COUNT_BIG(*) AS captured_queries FROM sys.query_store_query;SELECT COUNT_BIG(*) AS captured_plans FROM sys.query_store_plan;SELECT MIN(start_time) AS oldest_interval,MAX(end_time) AS newest_intervalFROM sys.query_store_runtime_stats_interval;GO

5. Production judgment and bridge

Use Query Store when you need a persisted account of query/plan behavior across cache turnover and time. Do not treat it as a live session monitor, a full tracing system, or an infinite telemetry warehouse. Size it, monitor its state, preserve enough history to span release/incident cycles, and document any capture-policy change because that change alters what the absence of data means.

The mandatory lab assumes SQL Server 2025 CU7 build 17.0.4065.4, compatibility 170, a single local Developer/Express instance, and SSMS 22.8.2 or supported VS Code MSSQL/current sqlcmd. No cluster, cloud service, restart, or paid production license is required. SQL Server 2025 can also support Query Store on readable Availability Group secondaries, but that topology-specific capability is not required here and has replica-aware state that must be interpreted separately.

Authoritative references

• How Query Store collects data — query, plan and runtime collection pipeline
• Best practices for managing Query Store — state, sizing, cleanup and interval guidance
• sys.database_query_store_options — effective Query Store configuration and permissions
• sys.query_store_runtime_stats — aggregated per-plan runtime metrics
• Monitor performance by using Query Store — plans, regressions and Query Store wait statistics
• SQL Server 2025 build versions — servicing baseline
• SSMS 22 release notes — current client-tool baseline