Ce que coûte le Query Store, mesuré
Categories:
11 minutes à lire
Sur une charge paramétrée, les modes de capture ALL, AUTO et CUSTOM n’ont pas eu de coût mesurable. Sur une charge ad hoc, le mode ALL a ralenti les requêtes de 19 % sur une connexion et de 41 % sur huit, où il a perdu une partie de ce qu’il devait capturer ; AUTO s’est contenté de 5 % sur une connexion. Le pire résultat vient du réglage qu’on choisit pour alléger le magasin : en QUERY_CAPTURE_MODE = NONE, une requête paramétrée que le magasin n’avait jamais capturée, exécutée par huit connexions, a pris 3,6 fois plus de temps.
La question revient à chaque audit : « Est-ce que le Query Store ralentit le serveur ? ». Cet article y répond par la mesure, sur SQL Server 2025 CU7 (17.0.4065.4) sous Linux, 22 processeurs, avec une vérification sur SQL Server 2022 CU26 pour le résultat le plus surprenant. Les durées sont propres à ce montage ; les écarts entre configurations se reproduisent mieux que les valeurs. Pour l’activation et la lecture du magasin, voir le guide Query Store.
La position de Microsoft
La documentation ne donne aucun pourcentage. Elle décrit une collecte faite en mémoire puis écrite sur disque de façon asynchrone (How Query Store collects data), et renvoie au mode de capture CUSTOM les administrateurs que ce surcoût inquiète (ALTER DATABASE SET options). Le chiffre de 3 à 5 % qui circule ne vient pas d’elle.
La documentation précise aussi ce qui suit :
- depuis SQL Server 2019, le mode par défaut est
AUTO, qui capture une requête dès qu’elle atteint, sur une période d’évaluation dont la durée n’est pas publiée, 30 exécutions, une seconde de compilation ou 100 ms de processeur ; c’étaitALLen 2016 et 2017. Selon la documentation, les instructions des procédures stockées, les curseurs et les requêtes compilées en natif sont toujours capturés ; je mesure que les curseurs sont soumis aux seuils depuis SQL Server 2022 (les modes de capture du Query Store) ; - depuis SQL Server 2019 CU9, 2017 CU23 et 2016 SP2 CU15, le Query Store limite la mémoire qu’il consomme et passe de lui-même en lecture seule quand il atteint ces limites, qui ne sont pas documentées (Best practices with Query Store).
Sur une base créée sous SQL Server 2025, le magasin est actif avec ces valeurs : READ_WRITE, capture AUTO, 1 000 Mo, écriture toutes les 900 secondes, intervalle de 60 minutes, 200 plans par requête, attentes capturées.
Le banc de mesure
Une base jetable, en mode de récupération simple, avec deux tables : 200 000 commandes et 10 000 clients, un index sur la colonne de jointure. Toutes les charges exécutent la même requête, une jointure suivie d’un GROUP BY sur un client, qui coûte environ 80 µs une fois compilée.
- La charge paramétrée l’exécute par
sp_executesqlavec un paramètre : une seule requête dans le magasin, 160 000 exécutions réparties sur huit connexions. - La charge ad hoc concatène deux littéraux dans le texte et l’exécute par
EXEC: chaque exécution est une requête distincte, compilée à chaque fois, et capturée en modeALL. Le paramétrage simple ne s’applique pas à cette requête : le magasin confirme que chaque texte reste distinct (autant dequery_text_idque d’exécutions, pour un seulquery_hash). Une compilation coûte ici environ 2,3 ms.
Chaque connexion exécute sa boucle dans le serveur et mesure sa propre durée, ce qui écarte les allers-retours du client. Avant chaque essai, le magasin est arrêté, vidé (CLEAR ALL), puis reconfiguré, et le cache des plans de la base est vidé ; l’état réel du magasin (actual_state_desc, query_capture_mode_desc) est relu et consigné avec chaque durée. Les valeurs données sont des médianes de cinq essais, configurations alternées, sauf celles d’une connexion × 50 000 exécutions, qui viennent d’une première série de trois essais.
Une charge paramétrée
| Configuration | 8 connexions × 20 000 exécutions |
|---|---|
| Query Store désactivé | 2 003 ms |
ALL | 2 082 ms |
CUSTOM (1 000 exécutions, 10 s de compilation, 10 s de processeur) | 2 313 ms |
AUTO | 2 411 ms |
NONE | 7 117 ms |
Entre OFF, ALL, AUTO et CUSTOM, les écarts sont dans le bruit de la mesure : la seule configuration OFF a varié de 1 837 à 2 439 ms sur cinq essais, et une série précédente de trois essais avait classé AUTO devant OFF. Sur une seule connexion et 50 000 exécutions, les quatre configurations se tenaient en 4 % (3 937 à 4 084 ms en médiane). Une requête qui s’exécute souvent n’est capturée qu’une fois ; ensuite, le magasin ne fait qu’agréger ses statistiques d’exécution en mémoire, et ce travail ne s’est pas vu.
La dernière ligne fait l’objet d’une section à part.
Une charge ad hoc
| Configuration | 1 connexion × 2 000 requêtes | 8 connexions × 1 000 requêtes |
|---|---|---|
| Query Store désactivé | 4 666 ms | 2 764 ms |
AUTO | 4 906 ms (+5 %) | 2 890 ms (+5 %) |
NONE | 5 057 ms (+8 %) | 2 865 ms (+4 %) |
CUSTOM (mêmes seuils) | 5 004 ms (+7 %) | 3 079 ms (+11 %) |
ALL | 5 561 ms (+19 %) | 3 901 ms (+41 %) |
C’est ici que le magasin coûte. En ALL, chaque requête nouvelle est enregistrée avec son texte et son plan, soit environ 0,45 ms de plus par requête sur une seule connexion, pour une compilation qui en coûte 2,3. Avec huit connexions, l’écart grandit. AUTO et CUSTOM l’évitent parce que ces requêtes, exécutées une seule fois, restent sous leurs seuils et ne sont pas enregistrées. Il reste de 5 à 8 % en AUTO, CUSTOM et NONE sur une connexion, que la mesure ne permet pas d’attribuer à un mécanisme précis du magasin. Sur huit connexions, AUTO et NONE restent dans la dispersion de OFF (2 639 à 3 026 ms) ; seul CUSTOM s’en écarte, de 11 %.
L’enregistrement est asynchrone. Trois secondes après la fin d’une charge de 2 000 requêtes, le magasin n’en contenait qu’environ 1 400 ; les autres arrivent dans les secondes qui suivent.
Avec huit connexions, le mode ALL a basculé en lecture seule dans les cinq essais :
USE [PachaDataFormation];
SELECT actual_state_desc, desired_state_desc, readonly_reason,
current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;
readonly_reason valait 262144 : la mémoire des éléments en attente d’écriture sur disque a atteint sa limite interne, et le magasin reste en lecture seule jusqu’à ce qu’ils soient écrits (sys.database_query_store_options). Le magasin n’occupait pourtant que 5 Mo sur les 1 000 autorisés. Il est revenu de lui-même en lecture-écriture entre 20 s et une minute environ après la fin de la charge (cinq essais, état relevé toutes les 3 à 15 s), bien avant l’échéance de 900 s ; ces essais, deux avec DATA_FLUSH_INTERVAL_SECONDS = 60 et trois avec 900, ne montrent pas d’effet de l’intervalle sur ce délai. Les requêtes exécutées pendant la lecture seule ne sont jamais enregistrées : sur les 8 000 requêtes de la charge, le magasin en a gardé entre 3 100 et 4 800 selon les essais. Une charge ad hoc soutenue en mode ALL paie donc le surcoût le plus élevé pour un historique incomplet.
La mémoire est restée modeste. Les clerks du Query Store sont communs à toute l’instance (dix bases y avaient le magasin actif) : la mesure compare donc leur taille avant et après une charge ad hoc en ALL, qui a laissé environ 3 650 requêtes dans le magasin. Ils ont pris 25 Mo, dont 16 pour les statistiques d’exécution, 6 pour la table de hachage des requêtes et 3 pour les instructions :
SELECT type, name, SUM(pages_kb) / 1024 AS mo
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%QDS%' OR type LIKE '%QUERYDISKSTORE%'
GROUP BY type, name
HAVING SUM(pages_kb) >= 1024
ORDER BY mo DESC;
L’instance avait 4 Go de max server memory. Les limites internes ne sont pas documentées, et rien ne dit que le passage en lecture seule surviendrait au même moment sur un serveur doté de plus de mémoire.
Le mode NONE
QUERY_CAPTURE_MODE = NONE arrête la capture des nouvelles requêtes et continue de collecter les statistiques de celles déjà capturées. Il passe facilement pour un mode allégé. Sur la charge paramétrée, il a été le seul réglage coûteux :
| Configuration | 1 connexion × 50 000 | 8 connexions × 20 000 |
|---|---|---|
| Query Store désactivé | 3 937 ms | 2 003 ms |
NONE, requête jamais capturée | 4 828 ms (+23 %) | 7 117 ms (× 3,6) |
Pendant la charge, les connexions attendent toutes le même verrou :
SELECT request_session_id, resource_subtype, resource_description,
request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_type = 'METADATA'
AND resource_subtype = 'QDS_STATEMENT_STABILITY';
Chaque exécution d’une requête que le magasin ne connaît pas prend un verrou de métadonnées exclusif (X) sur la clé de cette instruction (qds_statement_key), calculée sur le texte sans ses commentaires. Compté par l’événement lock_acquired, il est même acquis 22 fois par exécution (22 044 acquisitions pour 1 000 exécutions), autant que l’instance a de processeurs. Les exécutions concurrentes de la même requête se suivent alors une par une : sur un essai de 160 000 exécutions, 141 000 attentes LCK_M_X pour 30 secondes d’attente cumulée, contre moins de 50 ms d’attente en ALL comme en OFF, et 54 fois plus de rotations sur le spinlock LOCK_HASH qu’en ALL.
Ce que la mesure a délimité :
- huit connexions qui exécutent chacune une requête différente ne se gênent pas (1 686 ms contre 1 673 sans Query Store, sur 2022 CU27) : chaque instruction a sa clé ; des textes qui ne diffèrent que par un commentaire ont la même ;
- une procédure stockée appelée par huit connexions subit le même ralentissement (7 934 ms contre 2 059 sans Query Store sur 2025 CU7, 5 695 contre 1 452 sur 2022 CU27), sur le même verrou ;
- une requête déjà capturée avant le passage en
NONEn’est pas touchée (1 851 ms) ; READ_ONLYn’a pas ce coût (1 892 ms), pas plus queCUSTOMavec des seuils qu’aucune requête n’atteint (2 020 ms), alors que dans les deux cas la requête n’est jamais capturée ; enCUSTOMcomme enAUTO, les instructions d’une procédure stockée sont d’ailleurs toujours capturées, quels que soient les seuils (mesuré : capturée dès sa première série d’exécutions avec un seuil à un million) ;- le verrou et le ralentissement se retrouvent sur SQL Server 2017 CU31, 2019 CU32, 2022 CU27 et 2025 CU7 et CU10, avec ses 22 acquisitions par exécution et environ 135 000 attentes
LCK_M_Xpour 160 000 exécutions sur huit connexions.
Ni la documentation ni les articles consultés ne décrivent ce verrou ; ce qui précède est un constat sur ces versions. Il frappe le cas où NONE paraît le plus naturel : un magasin vidé, ou une base où l’application déploie du code nouveau après le passage en NONE. Pour suspendre la capture, READ_ONLY arrête aussi l’enregistrement des requêtes nouvelles, sans ce coût, mais il cesse en plus d’enregistrer les statistiques des requêtes déjà capturées.
Les réglages qui réduisent le coût
La capture AUTO convient aux charges paramétrées, qui sont l’essentiel des applications à procédures stockées ou à requêtes préparées : le surcoût n’y a pas été mesurable.
Sur une charge ad hoc, AUTO ou CUSTOM ramènent le surcoût à quelques pour cent (de 5 à 11 % dans ces essais), au prix de ne rien voir de ces requêtes. CUSTOM permet de relever les seuils d’AUTO, par exemple pour ne retenir que les requêtes exécutées au moins 100 fois par jour ou qui consomment au moins une seconde de compilation ou d’exécution :
ALTER DATABASE [PachaDataFormation] SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = CUSTOM,
QUERY_CAPTURE_POLICY = (
STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
EXECUTION_COUNT = 100,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 1000
)
);
Les trois seuils sont alternatifs : une requête est capturée dès qu’elle en dépasse un. Le remède de fond reste le paramétrage du code. À défaut, Microsoft propose le paramétrage forcé (PARAMETERIZATION FORCED) quand ces milliers de textes ne produisent que peu de plans différents (Best practices with Query Store).
Le mode ALL est à réserver aux analyses ponctuelles, et à éviter sur une charge ad hoc soutenue, où il a coûté le plus et perdu une partie de l’historique.
NONE n’est pas un mode allégé : sur la charge ad hoc, il a coûté à peu près autant qu’AUTO, et il a fortement ralenti une instruction qu’il n’avait pas capturée dès qu’elle était exécutée souvent et par plusieurs connexions à la fois. Pour suspendre la collecte, utilisez OPERATION_MODE = READ_ONLY ou désactivez le magasin.
WAIT_STATS_CAPTURE_MODE = OFF a réduit la durée de la charge ad hoc en ALL de 5 à 7 %, soit un quart à un tiers du surcoût de ce mode (sur une série de trois essais), sans effet visible sur la charge paramétrée. Les attentes par requête sont une des raisons d’utiliser le magasin ; ce gain ne justifie pas de s’en priver.
DATA_FLUSH_INTERVAL_SECONDS règle la fréquence d’écriture des statistiques sur disque ; son effet sur le coût des requêtes n’a pas été mesuré ici, et il n’a pas eu d’effet visible sur la durée du passage en lecture seule décrit plus haut. Le raccourcir réduit la quantité de statistiques perdues en cas d’arrêt brutal.
Le trace flag 7745 empêche l’écriture des données en mémoire à l’arrêt de l’instance ou de la base : il accélère un arrêt ou une bascule, au prix de ces données, et ne change rien pendant la charge (DBCC TRACEON, trace flags). Le 7752, qui rendait le chargement du magasin asynchrone au démarrage, n’a plus d’effet depuis SQL Server 2019.
Enfin, sur SQL Server 2016, 2017 et 2019, vérifiez le niveau de mise à jour avant d’activer le magasin sur une charge ad hoc : les limites de mémoire qui protègent l’instance y sont arrivées par des mises à jour cumulatives.