Les modes de capture du Query Store

Article coécrit avec Claude, qui a mené les tests en laboratoire et une partie des recherches.

Le mode de capture (QUERY_CAPTURE_MODE) décide quelles requêtes le Query Store enregistre. Il se règle par base, avec ALTER DATABASE ... SET QUERY_STORE, et vaut l’une de quatre valeurs : ALL, AUTO, CUSTOM et NONE. CUSTOM existe depuis SQL Server 2019. Le réglage ne concerne que les requêtes nouvelles : une requête déjà enregistrée continue d’accumuler ses statistiques quel que soit le mode. Vous voyez le mode d’une base dans 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;

Les colonnes capture_policy_* ne sont remplies qu’en mode CUSTOM. En AUTO, elles valent NULL, alors que ce mode applique des seuils : elles ne disent donc pas lesquels.

Les mesures de cet article viennent de conteneurs jetables sous Linux, en édition Developer (Enterprise Developer pour la 2025) : SQL Server 2019 CU32 avec sa mise à jour de sécurité, 2022 CU26 et 2025 CU9 avec sa mise à jour de sécurité (17.0.5015.4). Les essais sur PachaDataFormation sont faits sur la 2025, les comparaisons entre versions sur des bases neuves. Le CPU d’une requête que le Query Store n’a pas enregistrée se lit dans le cache des plans, par la colonne total_worker_time de sys.dm_exec_query_stats.

Les modes

Le mode se change à chaud, sans redémarrage, et ce que le Query Store contient déjà reste en place :

ALTER DATABASE PachaDataFormation SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Les seuils du mode AUTO

La page ALTER DATABASE SET les donne : depuis SQL Server 2019, AUTO capture une requête quand elle atteint 30 exécutions, une seconde de CPU de compilation ou 100 ms de CPU d’exécution. Il suffit d’un des trois. Les compteurs sont cumulés sur une période d’évaluation, dont la page ne donne pas la durée pour AUTO ; en CUSTOM, elle vaut 24 heures par défaut. Je ne l’ai pas mesurée. La page sur la gestion du Query Store dit au contraire ces seuils « déterminés en interne ».

J’ai mesuré les deux seuils d’exécution sur PachaDataFormation, chaque essai précédé d’un CLEAR du Query Store et d’un vidage du cache des plans. La requête compte un nombre de lignes variable de Elearning.Activite, en MAXDOP 1 pour que le CPU suive le nombre de lignes, par 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;

La requête de 31 ms a cumulé 31,0, 61,4 puis 92,1 ms sur ses trois premières exécutions, et elle est enregistrée à la quatrième, qui fait passer le cumul au-dessus de 100 ms. La requête légère est enregistrée à la trentième. Une première série avait placé le seuil entre 96,8 ms (non capturée) et 121,5 ms (capturée).

Pour le seuil de compilation, mesuré sur une base neuve, il faut une requête qui compile longtemps et s’exécute vite. Deux listes IN de quelques milliers de constantes, des entiers sur une colonne et des chaînes sur l’autre, font l’affaire : sur une table de 100 lignes, la requête s’exécute en 5 ms, et sa compilation dépasse la seconde vers 3 000 valeurs sur la 2025, 3 500 sur la 2022. Sur la 2025, une compilation de 964 ms de CPU (le CPU time de SET STATISTICS TIME) n’a pas été capturée, une de 1 084 ms l’a été, à sa première exécution, sur trois séries. Sur la 2022, 948 ms n’a pas été capturée, 1 063 ms l’a été.

Sur SQL Server 2019, 2022 et 2025, une même série a donné le même résultat : une requête lancée 29 fois n’est pas enregistrée, la même lancée 31 fois l’est, et une requête de 375 à 470 ms de CPU l’est à sa seule exécution.

Les exécutions non enregistrées

Le Query Store ne garde rien d’une requête tant qu’elle n’a pas franchi un seuil. L’exécution qui déclenche la capture est la première comptée. La requête légère lancée 40 fois apparaît avec 11 exécutions, de la 30e à la 40e, et celle de 31 ms avec 3 sur 6.

Pour qui lit le Query Store en mode AUTO, il en résulte que :

• les totaux d’exécutions et de CPU d’une requête fréquente sont sous-estimés, de ses 29 premières exécutions sur la période au plus ;
• une requête peu fréquente et peu coûteuse n’y laisse aucune trace : lancée moins de 30 fois sur la période d’évaluation, elle n’y apparaît pas.

Le premier point compte peu pour l’optimisation, puisqu’une requête qui coûte finit par être capturée. Le second compte quand on cherche dans le Query Store une requête précise, ce que fait l’article sur l’analyse du Query Store par un agent.

Les procédures stockées et les curseurs

La documentation dit que les curseurs, les requêtes des procédures stockées et les requêtes compilées en natif sont toujours capturés en mode ALL, AUTO ou CUSTOM. J’ai vérifié les deux premiers en AUTO, sur des bases neuves, avec une requête qui coûte moins d’une milliseconde :

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;

La procédure est capturée dès sa première exécution sur les trois versions, comme le dit la documentation. Le curseur ne l’est plus depuis SQL Server 2022 : il est soumis aux seuils. SQL Server 2017 (CU31 avec sa mise à jour de sécurité) se comporte comme la 2019. En 2025, le même curseur déclaré, ouvert puis libéré à chacun des 35 tours d’une boucle est enregistré au 31e tour, avec 5 exécutions comptées, soit un tour plus tard qu’une requête ad hoc lancée 35 fois, enregistrée à la 30e avec 6 exécutions comptées. Un curseur STATIC ouvert une fois n’est pas capturé non plus. En mode ALL, le curseur est capturé dès la première ouverture.

Je n’ai pas mesuré les requêtes compilées en natif (In-Memory OLTP). La documentation les dit toujours capturées, à condition d’activer la collecte de leurs statistiques avec sys.sp_xtp_control_query_exec_stats.

Le mode CUSTOM

CUSTOM applique la même règle que AUTO avec des seuils que vous choisissez, dans une clause QUERY_CAPTURE_POLICY. Les trois seuils ont par défaut les valeurs publiées pour AUTO :

Avec les valeurs par défaut, CUSTOM a reproduit AUTO dans mes mesures : une exécution à 98,4 ms non capturée, une à 104,5 ms capturée, la requête légère comptée 11 fois sur 40. Avec un seuil de 5 exécutions, une requête lancée 4 fois n’est pas enregistrée, et une requête lancée 8 fois l’est à la cinquième, avec 4 exécutions comptées :

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));

C’est le mode à choisir quand AUTO laisse de côté des requêtes que vous voulez voir, sans aller jusqu’à ALL.

Le mode NONE

NONE arrête la capture des requêtes nouvelles. Celles qui sont déjà enregistrées continuent d’accumuler leurs statistiques. Une requête capturée en ALL, puis exécutée 5 fois après le passage en NONE, compte 6 exécutions. Une requête nouvelle n’est pas enregistrée, même lancée 40 fois.

La documentation recommande la prudence avec ce mode : une requête qui apparaît après le passage en NONE reste invisible, quel que soit son coût.

Le volume du mode ALL

L’écart entre ALL et AUTO se voit sur une charge de requêtes ad hoc toutes différentes, celles qu’une application produit en concaténant ses valeurs dans le texte SQL. J’ai lancé 3 000 requêtes de ce genre, chacune une seule fois avec un ContactId différent. La paramétrisation simple ne les réunit pas sous un même texte (type de paramétrisation None dans le 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;

En ALL, chaque requête arrive avec son texte et son plan, pour ne jamais servir. Sur une application de ce genre, le Query Store se remplit de requêtes uniques, atteint MAX_STORAGE_SIZE_MB plus vite, et son nettoyage par la taille commence par retirer les requêtes les moins coûteuses et les plus anciennes.

Choisir un mode

AUTO convient à la plupart des bases : il garde les requêtes qui coûtent et celles qui reviennent souvent, et il écarte le bruit. ALL se justifie pour une recherche ponctuelle où chaque requête compte, par exemple pour retrouver les requêtes qui ont lu une table, le temps de cette recherche, en surveillant la taille du Query Store. CUSTOM abaisse ou relève les seuils quand ceux d’AUTO ne conviennent pas à la charge. NONE fige le contenu, et vous ne verrez pas les requêtes qui arrivent ensuite.

Quel que soit le mode, une base dont le Query Store est en READ_ONLY n’enregistre plus rien : vérifiez actual_state_desc avant de conclure qu’une requête n’a pas été capturée à cause du mode.