Query Store capture modes

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

The capture mode (QUERY_CAPTURE_MODE) decides which queries the Query Store records. It is set per database, with ALTER DATABASE ... SET QUERY_STORE, and takes one of four values: ALL, AUTO, CUSTOM and NONE. CUSTOM exists since SQL Server 2019. The setting only applies to new queries: a query already recorded keeps accumulating its statistics whatever the mode. You can see a database’s mode in sys.database_query_store_options:

USE PachaDataFormation;

SELECT actual_state_desc, query_capture_mode_desc,
       capture_policy_execution_count,
       capture_policy_total_compile_cpu_time_ms,
       capture_policy_total_execution_cpu_time_ms,
       capture_policy_stale_threshold_hours
FROM sys.database_query_store_options;

The capture_policy_* columns are filled only in CUSTOM mode. In AUTO, they are NULL, even though this mode applies thresholds: they do not tell you which ones.

The measurements in this article come from throwaway Linux containers, Developer edition (Enterprise Developer for 2025): SQL Server 2019 CU32 with its security update, 2022 CU26, and 2025 CU9 with its security update (17.0.5015.4). The tests on PachaDataFormation were run on 2025, the comparisons between versions on new databases. The CPU of a query the Query Store did not record is read from the plan cache, in the total_worker_time column of sys.dm_exec_query_stats.

The modes

The mode can be changed online, without a restart, and what the Query Store already holds stays in place:

ALTER DATABASE PachaDataFormation SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

The AUTO thresholds

The ALTER DATABASE SET page gives them: since SQL Server 2019, AUTO captures a query when it reaches 30 executions, one second of compile CPU or 100 ms of execution CPU. Any one of the three is enough. The counters accumulate over an evaluation period, whose length the page does not give for AUTO; in CUSTOM, it defaults to 24 hours. I did not measure it. The page on managing the Query Store says instead that these thresholds are “internally determined”.

I measured the two execution thresholds on PachaDataFormation, each test preceded by a Query Store CLEAR and a plan cache flush. The query counts a variable number of rows from Elearning.Activite, with MAXDOP 1 so that CPU follows the number of rows, through sp_executesql:

USE PachaDataFormation;

EXEC sp_executesql
    N'SELECT COUNT_BIG(*) FROM (SELECT TOP (@n) ActiviteId FROM Elearning.Activite) AS t OPTION (MAXDOP 1);',
    N'@n int', @n = 1;

The 31 ms query accumulated 31.0, 61.4 then 92.1 ms over its first three executions, and it is recorded at the fourth, which takes the total above 100 ms. The light query is recorded at the thirtieth. An earlier series had placed the threshold between 96.8 ms (not captured) and 121.5 ms (captured).

For the compile threshold, measured on a new database, you need a query that compiles for a long time and runs fast. Two IN lists of a few thousand constants, integers on one column and strings on the other, do the job: on a 100-row table, the query runs in 5 ms, and its compilation goes past one second at about 3,000 values on 2025 and 3,500 on 2022. On 2025, a compilation of 964 ms of CPU (the CPU time of SET STATISTICS TIME) was not captured, one of 1,084 ms was, at its first execution, over three series. On 2022, 948 ms was not captured, 1,063 ms was.

On SQL Server 2019, 2022 and 2025, the same series gave the same result: a query run 29 times is not recorded, the same query run 31 times is, and a query of 375 to 470 ms of CPU is recorded at its only execution.

The executions that are not recorded

The Query Store keeps nothing of a query until it has crossed a threshold. The execution that triggers the capture is the first one counted. The light query run 40 times shows 11 executions, from the 30th to the 40th, and the 31 ms one shows 3 out of 6.

For anyone reading the Query Store in AUTO mode, this means that:

• the execution and CPU totals of a frequent query are underestimated, by at most its first 29 executions in the period;
• an infrequent, cheap query leaves no trace: run fewer than 30 times in the evaluation period, it does not appear.

The first point matters little for tuning, since a costly query ends up captured. The second matters when you search the Query Store for one specific query, which is what the article on analyzing the Query Store with an AI agent does.

Stored procedures and cursors

The documentation says that cursors, queries inside stored procedures and natively compiled queries are always captured in ALL, AUTO or CUSTOM mode. I checked the first two in AUTO, on new databases, with a query that costs less than a millisecond:

USE PachaDataFormation;
GO
CREATE PROCEDURE dbo.VerifCapture
AS
SELECT Nom FROM Contact.Contact WHERE ContactId = 7;
GO
EXEC dbo.VerifCapture;
GO
DECLARE c CURSOR LOCAL FOR
    SELECT Nom FROM Contact.Contact WHERE ContactId BETWEEN 10 AND 12;
OPEN c;
FETCH NEXT FROM c;
CLOSE c;
DEALLOCATE c;
GO
DROP PROCEDURE dbo.VerifCapture;

The procedure is captured from its first execution on all three versions, as the documentation says. The cursor no longer is since SQL Server 2022: it is subject to the thresholds. SQL Server 2017 (CU31 with its security update) behaves like 2019. On 2025, the same cursor declared, opened and deallocated on each of 35 loop iterations is recorded at the 31st iteration, with 5 executions counted, one iteration later than an ad hoc query run 35 times, which is recorded at the 30th with 6 executions counted. A STATIC cursor opened once is not captured either. In ALL mode, the cursor is captured from the first open.

I did not measure natively compiled queries (In-Memory OLTP). The documentation says they are always captured, provided you enable the collection of their statistics with sys.sp_xtp_control_query_exec_stats.

The CUSTOM mode

CUSTOM applies the same rule as AUTO with thresholds you choose, in a QUERY_CAPTURE_POLICY clause. The three thresholds default to the values published for AUTO:

With the default values, CUSTOM reproduced AUTO in my measurements: one execution at 98.4 ms not captured, one at 104.5 ms captured, the light query counted 11 times out of 40. With a threshold of 5 executions, a query run 4 times is not recorded, and a query run 8 times is recorded at the fifth, with 4 executions counted:

ALTER DATABASE PachaDataFormation SET QUERY_STORE (
    QUERY_CAPTURE_MODE = CUSTOM,
    QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 1 HOURS,
        EXECUTION_COUNT = 5,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100));

This is the mode to choose when AUTO leaves out queries you want to see, without going as far as ALL.

The NONE mode

NONE stops the capture of new queries. Those already recorded keep accumulating their statistics. A query captured in ALL, then run 5 times after the switch to NONE, shows 6 executions. A new query is not recorded, even when run 40 times.

The documentation advises caution with this mode: a query that appears after the switch to NONE stays invisible, whatever its cost.

The volume of ALL mode

The gap between ALL and AUTO shows on a workload of ad hoc queries that are all different, the kind an application produces by concatenating its values into the SQL text. I ran 3,000 queries of this kind, each one once with a different ContactId. Simple parameterization does not merge them under one text (parameterization type None in the Query Store):

USE PachaDataFormation;

SELECT c.Nom, a.Adresse1
FROM Contact.Contact AS c
JOIN Contact.Adresse AS a ON a.AdresseId = c.AdressePostaleId
WHERE c.ContactId = 1234;

In ALL, each query arrives with its text and its plan, never to be used. On an application of this kind, the Query Store fills up with unique queries, reaches MAX_STORAGE_SIZE_MB sooner, and its size-based cleanup starts by removing the cheapest and oldest queries.

Choosing a mode

AUTO suits most databases: it keeps the queries that cost and those that come back often, and it discards the noise. ALL is justified for a one-off investigation where every query counts, for example to find the queries that read a table, for the duration of that investigation, while watching the Query Store size. CUSTOM lowers or raises the thresholds when those of AUTO do not fit the workload. NONE freezes the content, and you will not see the queries that arrive afterward.

Whatever the mode, a database whose Query Store is READ_ONLY no longer records anything: check actual_state_desc before concluding that a query was not captured because of the mode.