What the Query Store records

Co-written with Claude, who ran the lab tests and part of the research.

The Query Store is a query recorder built into each database, since SQL Server 2016. It keeps, in the database itself, the text of the queries, their execution plans and their runtime statistics per time interval, and, since SQL Server 2017, their waits. You use it to find a query that slowed down, to compare its plans, and to force the right one. You read it through the database’s sys.query_store_* views, or through the SSMS reports.

It does not keep everything. What it leaves out depends on the capture mode, on the statement, on the database where it runs and on how the execution ends. This article takes stock. The measurements come from Linux containers running SQL Server 2025 CU9 with its security update (17.0.5015.4), in ALL mode unless stated otherwise. It also draws on two neighboring articles: Query Store capture modes and analyzing the Query Store with an AI agent.

What the Query Store contains

The recorded text is that of the statement, not of the batch. When simple parameterization applies, it is the text rewritten by the engine: literals become parameters, names are put in square brackets, and comments disappear. In my test database, SELECT /*marker*/ COUNT(*) FROM dbo.T WHERE id < 7 is recorded as (@1 tinyint)SELECT COUNT(*) FROM [dbo].[T] WHERE [id]<@1. Searching for the marker does not find it.

The recorded plan is an estimated plan. It contains the parameter values at compile time (ParameterCompiledValue), not those of later executions. A procedure compiled with @id = 5, then run with 7 and 9, has only the value 5 in its plan. For a query parameterized by simple parameterization, the compiled value is the literal of the first execution.

No column of the sys.query_store_* views holds a login, user, host or application name, or a session ID (sys.query_store_replicas, since SQL Server 2022, names only the replicas of an availability group). The Query Store tells you what ran and how many times, not by whom. To know that, you need an audit (SQL Server Audit or an Extended Events session) in place before the facts.

The capture mode

ALL mode captures every query from its first execution, except those of natively compiled procedures, whose collection you enable with sys.sp_xtp_control_query_exec_stats. AUTO mode, the default since SQL Server 2019, records a query only when it reaches, over an evaluation period, 30 executions, one second of compile CPU or 100 ms of execution CPU. The executions before that point are not counted. Queries inside stored procedures are captured from their first execution; cursors were until SQL Server 2019, and they are subject to the thresholds since SQL Server 2022. NONE mode no longer records any new query. The details and the measurements are in the article on capture modes.

Whatever the mode, a Query Store in READ_ONLY no longer records anything. It switches to that state on its own when it exceeds MAX_STORAGE_SIZE_MB, and goes back to read-write if size-based cleanup frees enough space. A Query Store that is turned OFF keeps its content, frozen at the date it was turned off.

The missing statements

I ran one statement of each kind in a new database in ALL mode, each with a marker comment in its text and variables rather than literals, so that simple parameterization would not remove the marker:

For creating or rebuilding an index and for updating statistics, the Query Store keeps the text of the command, but as an internal query (is_internal_query = 1), with the plan of the query the engine runs for it: an INSERT that fills the index, a SELECT that computes the statistics. The documentation says so: the Query Store does not collect plans for DDL statements, but for the DML statements they run. TRUNCATE TABLE, CREATE TABLE, ALTER TABLE, DROP TABLE and DBCC CHECKTABLE leave no trace, even in ALL: a table emptied by TRUNCATE cannot be found in the Query Store.

The context database

A query is recorded in the Query Store of the database it runs in, not in that of the database it reads. Run from the Ventes database, the following query appears in the Query Store of Ventes, and not in that of PachaDataFormation:

SELECT * FROM PachaDataFormation.Contact.Adresse WHERE AdresseId = 4812;

The Query Store cannot be enabled on master or on tempdb. An ad hoc query run from either of these databases is therefore recorded nowhere, whatever database it reads. And any login can connect to them. A user database procedure called from master, or an sp_executesql prefixed with the database name, runs in that database and is recorded there. A query run from a user database that reads tempdb is recorded in the user database.

Interrupted executions

sys.query_store_runtime_stats distinguishes three ways an execution ends, in its execution_type_desc column: Regular (finished normally), Aborted (canceled by the client) and Exception (stopped by an error). I interrupted queries in four ways, in a new database in ALL mode, using a query of 3 to 5 seconds for the last three, which give the same result on SQL Server 2017, 2019, 2022 and 2025:

The How Query Store collects data page says that a query’s statistics are not recorded when the session is terminated or when the client application restarts or crashes. That holds for the session ended by KILL. For a killed client whose connection closes, I measure the opposite: the execution is recorded as aborted, which the sys.query_store_runtime_stats page actually describes as “Client initiated aborted execution”. I proposed a correction of the page.

To read these executions, filter on the type rather than adding everything up:

USE PachaDataFormation;

SELECT q.query_id, rs.execution_type_desc,
       SUM(rs.count_executions) AS executions,
       MAX(rs.max_duration) / 1000 AS max_duration_ms
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE rs.execution_type_desc <> N'Regular'
GROUP BY q.query_id, rs.execution_type_desc
ORDER BY executions DESC;

Hard shutdown of the instance

Query texts and their plans are written to disk with minimal latency, the documentation says. Runtime statistics stay in memory and are written every DATA_FLUSH_INTERVAL_SECONDS, 900 seconds by default. I ran a query 10 times, then stopped the instance in three ways before any write, and read the Query Store again after the restart:

A normal shutdown writes the statistics, except with trace flag 7745, which suppresses that write. A shutdown without wait or a crash erases everything that had not been written, up to 15 minutes of executions with the default value. Before a shutdown you know will be abrupt, or before copying a database to analyze it, sp_query_store_flush_db writes the in-memory statistics to disk:

USE PachaDataFormation;

EXEC sys.sp_query_store_flush_db;

Retention

Data is kept 30 days by default (STALE_QUERY_THRESHOLD_DAYS). Size-based cleanup, on by default, removes the cheapest and oldest queries when the Query Store gets close to MAX_STORAGE_SIZE_MB, 1,000 MB by default since SQL Server 2019. A rare, cheap query is therefore the first to go.

The Query Store is stored in the database: a backup keeps it. DBCC CLONEDATABASE copies it into the clone when it is enabled in the source database, without the statistics still in memory. NO_QUERYSTORE and VERIFY_CLONEDB exclude it from the clone.