Le Query Store en production : saturation, forçage, angles morts
Categories:
12 minutes à lire
Le nettoyage par la taille ne garantit pas un Query Store toujours en lecture-écriture : sur SQL Server 2025, un magasin qui se remplit de nouveau peu après un passage a attendu le suivant environ quinze minutes en lecture seule, sans rien enregistrer. Un plan forcé qui ne peut plus s’appliquer ne renvoie aucune erreur, et son compteur d’échecs ne dit pas si l’échec est en cours. Le magasin ne garde ni les valeurs des paramètres, ni l’identité du bloqueur, ni le message des erreurs.
Le guide Activer et utiliser le Query Store décrit l’activation, et l’article sur ce que coûte le Query Store mesure son surcoût. Celui-ci regarde ce qui arrive au magasin quand il tourne depuis des mois sur une base de production : il se remplit, des plans forcés cessent de s’appliquer, et une partie de ce qu’on lui demande n’y a jamais été enregistrée.
Les mesures
La saturation du magasin a été mesurée le 17 octobre 2026 sur une instance de test SQL Server 2025 CU9 avec sa mise à jour de sécurité (17.0.5015.4) Enterprise Developer sous Linux, dans des bases jetables. Le plan forcé et les angles morts l’ont été le 18 octobre 2026 sur SQL Server 2025 CU7 (17.0.4065.4) Enterprise Developer sous Linux, dans une copie de la base PachaDataFormation en compatibilité 170. Les scripts sont dans l’article ; les identifiants (query_id, plan_id) sont ceux de l’instance de test, à remplacer par les vôtres.
La saturation du magasin
Le magasin s’arrête d’enregistrer quand il atteint MAX_STORAGE_SIZE_MB : il passe en lecture seule, ses rapports s’ouvrent toujours, et ils s’arrêtent à la date de la saturation. Le nettoyage par la taille (SIZE_BASED_CLEANUP_MODE = AUTO, la valeur par défaut) doit l’éviter. La documentation d’ALTER DATABASE SET dit qu’il démarre quand le magasin atteint 90 % de sa taille maximale, qu’il supprime d’abord les requêtes les moins coûteuses et les plus anciennes, et qu’il s’arrête vers 80 %. Elle précise aussi que la taille n’est contrôlée qu’à l’écriture sur disque, toutes les DATA_FLUSH_INTERVAL_SECONDS (900 secondes par défaut), et qu’un magasin qui dépasse sa limite entre deux contrôles passe en lecture seule.
Le banc remplit une base jetable de requêtes ad hoc toutes différentes, capturées en mode ALL, avec une écriture sur disque toutes les 60 secondes. Ces requêtes, courtes et à plan simple, ont occupé environ 3 Ko chacune.
-- Base jetable : remplacez verif_qs par le nom de votre base de test
ALTER DATABASE verif_qs SET QUERY_STORE (
OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL,
MAX_STORAGE_SIZE_MB = 100, SIZE_BASED_CLEANUP_MODE = AUTO,
DATA_FLUSH_INTERVAL_SECONDS = 60, INTERVAL_LENGTH_MINUTES = 1);
| Taille maximale | Avant la lecture seule | Nettoyage | Retour en lecture-écriture |
|---|---|---|---|
| 100 Mo | 90, 96, 102, 108 Mo sans nettoyage, puis lecture seule à 115 Mo | progressif, de 37 107 à 24 569 requêtes en deux minutes et demie, jusqu’à 87 Mo | environ trois minutes après la saturation |
| 10 Mo | lecture seule à 21 Mo | un premier passage jusqu’à 10 Mo, puis la taille remonte à 11 Mo | environ quinze minutes, après un second passage qui descend à 4 Mo |
| 10 Mo, écriture forcée toutes les 500 requêtes | lecture seule à 10 Mo | aucun passage visible pendant le quart d’heure | environ seize minutes, à 8 Mo |
Dans ces essais, le nettoyage n’a pas démarré à 90 % : à 100 Mo, le magasin a franchi 90, 96 puis 102 Mo en lecture-écriture sans nettoyage, et un relecteur l’a vu rester à 90,2 % pendant plus de trois minutes. Un autre relecteur, avec une écriture sur disque toutes les vingt requêtes, l’a vu démarrer à 9 Mo sur 10 et supprimer les 420 requêtes les plus anciennes : le contrôle n’a lieu qu’à l’écriture, et un banc qui écrit rarement peut sauter la fenêtre entre 90 et 100 %. Ce qui s’est reproduit dans tous les essais à 10 Mo, le sien compris, c’est la suite : quand le magasin se remplit de nouveau après un passage, le passage suivant n’arrive qu’environ quinze minutes plus tard, et le magasin reste en lecture seule jusque-là. La documentation ne parle pas de ce délai, mais le moteur le donne : à chaque contrôle qui tombe dedans, l’événement étendu query_store_size_retention_cleanup_skipped (package qds) indique le temps écoulé depuis le passage précédent (time_since_last_cleanup_seconds) et la période minimale entre deux passages (min_repeat_period_seconds), 900 secondes. La valeur est la même sur SQL Server 2019, 2022 et 2025, que le magasin écrive sur disque toutes les 60 ou toutes les 180 secondes ; l’article sur un Query Store saturé par des identifiants concaténés donne la session qui capte ces événements et la séquence observée. Avec 100 Mo, la coupure a duré trois minutes. Pendant ce temps, rien n’est enregistré : sur les 60 000 requêtes de l’essai à 100 Mo, celles qui sont arrivées pendant la coupure ne sont jamais entrées dans le magasin. Avec le nettoyage désactivé (OFF), le magasin de 10 Mo est passé en lecture seule à 3 002 requêtes et y est resté jusqu’à l’augmentation de sa taille, quelques minutes plus tard ; la documentation ne prévoit alors aucune sortie automatique.
Le banc accélère le remplissage, mais une charge réelle riche en requêtes ad hoc distinctes peut aussi épuiser vite un magasin trop petit, comme le signale la page des bonnes pratiques. Le nettoyage par la taille ne suffit pourtant pas à garantir un magasin toujours en lecture-écriture : il ne passe qu’au contrôle, et pas moins de 900 secondes après le passage précédent. Ce qui prévient la saturation :
- une taille dimensionnée sur le rythme réel de remplissage, relevé dans
current_storage_size_mbsur quelques semaines, pour rester nettement sous la limite avec la rétention voulue (STALE_QUERY_THRESHOLD_DAYS, 30 jours par défaut) ; - le mode de capture
AUTO(la valeur par défaut depuis SQL Server 2019) ouCUSTOM, qui n’enregistre une requête ad hoc qu’au-delà d’un seuil : enAUTO, sur une journée, 30 exécutions, une seconde de processeur de compilation ou 100 ms de processeur d’exécution ; les requêtes ad hoc exécutées une seule fois et bon marché, celles qui remplissent le magasin, restent dehors, alors que les instructions des procédures stockées sont toujours enregistrées ; - la surveillance de l’état réel, plus bas.
Augmenter MAX_STORAGE_SIZE_MB fait sortir le magasin de la lecture seule au contrôle suivant : passé de 10 à 20 Mo, le magasin au nettoyage désactivé est revenu en lecture-écriture en moins de 75 secondes, avec une écriture toutes les 60 secondes ; avec les 900 secondes par défaut, l’attente peut aller jusqu’à un quart d’heure. La page des bonnes pratiques ajoute SET QUERY_STORE (OPERATION_MODE = READ_WRITE) pour revenir tout de suite, ce que l’instance de test a accepté.
Le plan forcé
Le forçage d’un plan par sp_query_store_force_plan est la réponse la plus courante à une régression, et celle qu’on oublie le plus vite. Le scénario de test reprend la procédure de recherche par code postal sur Contact.ProspectUS, un tas d’un million de lignes. Le scénario supprime d’abord l’index sur CP, s’il existe (le livre le crée dans ses exercices) : quarante appels sans index, puis quarante avec l’index, donnent deux plans pour la même requête.
USE [PachaDataFormation];
GO
CREATE OR ALTER PROCEDURE Contact.ProspectsParCodePostal @CP varchar(20)
AS
SET NOCOUNT ON;
SELECT Prenom, Nom, Adresse, Ville, Email
FROM Contact.ProspectUS
WHERE CP = @CP;
GO
-- L'index livré avec la base est supprimé, puis recréé plus bas
DROP INDEX IF EXISTS nix_prospectus_cp ON Contact.ProspectUS;
-- 40 appels sans index, puis
CREATE INDEX nix_prospectus_cp ON Contact.ProspectUS (CP);
-- et 40 appels avec l'index
Le magasin contient alors le plan 1, un parcours du tas à 14 760 lectures par appel, et le plan 4, une recherche dans l’index à 40 lectures. Le plan 4 est forcé :
USE [PachaDataFormation];
-- Remplacez 1 et 4 par le query_id et le plan_id de votre requête
EXEC sys.sp_query_store_force_plan @query_id = 1, @plan_id = 4;
La compilation d’un plan forcé
Un plan forcé ne recompile pas la requête à chaque exécution. Le forçage agit à la compilation : l’optimiseur reçoit le plan à reproduire, et le plan obtenu va dans le cache comme les autres. Après quarante appels forcés, count_compiles de la requête n’avait augmenté que d’une unité, et la requête en cache affichait 40 exécutions depuis sa dernière compilation.
L’échec du forçage
L’index supprimé, le plan forcé ne peut plus être reproduit. La requête ne renvoie aucune erreur : l’optimiseur compile un autre plan, ici le parcours du tas, et le magasin note l’échec.
USE [PachaDataFormation];
SELECT p.query_id, p.plan_id, p.is_forced_plan,
p.force_failure_count, p.last_force_failure_reason_desc
FROM sys.query_store_plan AS p
WHERE p.is_forced_plan = 1;
| Étape | Exécutions | force_failure_count | last_force_failure_reason_desc | Plan exécuté |
|---|---|---|---|---|
| Index présent, plan 4 forcé | 40 | 0 | NONE | 4 |
| Index supprimé | 10 | 1 | NO_INDEX | 1 |
| Index recréé | 10 | 1 | NO_INDEX | 4 |
Le compteur ne mesure pas l’ampleur de l’échec : la documentation de sys.query_store_plan dit qu’il n’augmente qu’à la recompilation, et dix appels sur le mauvais plan ne lui ont ajouté qu’une unité. Il ne revient pas non plus à zéro quand la cause disparaît : l’index recréé, le plan forcé s’applique de nouveau et le compteur affiche toujours 1, avec la raison NO_INDEX. Un compteur non nul dit qu’un échec a eu lieu un jour, pas que le forçage échoue maintenant ; pour le savoir, il faut regarder quel plan de la requête s’est exécuté en dernier (last_execution_time).
Le forçage est enregistré dans la base : il survit au redémarrage, part dans la sauvegarde et suit une restauration sur une autre instance, où il échouera de la même façon si un index y manque. Il ne suit pas la requête si son texte change : une virgule ajoutée par un déploiement crée un autre query_id, que rien ne force.
La surveillance
Les deux pannes vues plus haut ne se signalent pas : un magasin en lecture seule continue d’afficher ses rapports, et un plan forcé qui échoue laisse la requête tourner sur un autre plan. La requête suivante en relève les signes, sans rien modifier. Elle ne voit que l’état du moment : une coupure de trois minutes échappe à un relevé quotidien. Un travail de l’Agent qui l’exécute toutes les quelques minutes et garde ses résultats dans une table donne l’historique des passages en lecture seule.
USE [PachaDataFormation];
-- État du magasin : un écart entre l'état demandé et l'état réel est une alerte
SELECT DB_NAME() AS base, desired_state_desc, actual_state_desc, readonly_reason,
current_storage_size_mb, max_storage_size_mb,
CAST(current_storage_size_mb * 100.0 / NULLIF(max_storage_size_mb, 0) AS decimal(5,1)) AS pct_occupe,
query_capture_mode_desc, size_based_cleanup_mode_desc
FROM sys.database_query_store_options;
-- Plans forcés, et le dernier plan exécuté pour chaque requête forcée
SELECT p.query_id, p.plan_id AS plan_force, p.force_failure_count,
p.last_force_failure_reason_desc, p.last_execution_time AS derniere_execution_du_plan_force,
(SELECT TOP (1) p2.plan_id FROM sys.query_store_plan AS p2
WHERE p2.query_id = p.query_id ORDER BY p2.last_execution_time DESC) AS dernier_plan_execute
FROM sys.query_store_plan AS p
WHERE p.is_forced_plan = 1;
-- Indicateurs du Query Store qui ont déjà échoué (compteur cumulé depuis leur pose)
SELECT query_id, query_hint_text, query_hint_failure_count,
last_query_hint_failure_reason_desc
FROM sys.query_store_query_hints
WHERE query_hint_failure_count > 0;
Quand dernier_plan_execute diffère du plan forcé, même avec un compteur à 0, le forçage est suspect, et il reste à le confirmer : le dernier plan exécuté peut être antérieur au forçage, si la requête n’a pas tourné depuis, et la documentation prévient que le forçage peut produire un plan seulement semblable, enregistré sous un autre plan_id. Il faut alors comparer les dates d’exécution et les plans eux-mêmes. Sur une base dont le magasin capture aussi les secondaires lisibles, la comparaison se fait par réplica (replica_group_id), le forçage pouvant différer de l’un à l’autre.
readonly_reason est un masque de bits qui donne la cause de la lecture seule ; les deux valeurs rencontrées ici sont 65536, la taille maximale atteinte, et 262144, la mémoire des écritures en attente, une limite interne qui se lève seule (sys.database_query_store_options).
Les angles morts du magasin
Le magasin agrège par requête, par plan et par intervalle. Ce qui tombe entre ces mailles n’y est pas, et la question arrive souvent après l’incident.
Les valeurs des paramètres
Le plan enregistré porte la valeur avec laquelle il a été compilé (ParameterCompiledValue), jamais celles des appels suivants. Après un appel de la procédure avec '99999', les deux plans de la requête ne contenaient que '28334', la valeur de leur compilation, et aucun attribut ParameterRuntimeValue. Pour savoir quelle valeur rend un appel lent, il faut une session d’événements étendus ou le plan réel ; le magasin dit seulement que les durées d’un même plan s’étalent, par l’écart-type et le maximum de sys.query_store_runtime_stats.
Le bloqueur
Un UPDATE bloqué 5,4 secondes par une transaction ouverte sur les mêmes lignes apparaît bien, avec une attente de catégorie Lock de 5 400 ms dans sys.query_store_wait_stats. Rien n’y dit quelle session le bloquait, ni sur quelle ressource : le magasin ne garde que des catégories d’attente cumulées par plan. Le rapport de processus bloqués ou sys.dm_exec_requests pendant l’incident restent nécessaires.
Les exécutions interrompues
Une requête annulée par le délai d’attente du client (une seconde, ici) est enregistrée, avec le type d’exécution Aborted et sa durée jusqu’à l’annulation (988 ms). Une procédure qui échoue sur une division par zéro est enregistrée avec le type Exception, comme une requête ad hoc en échec. Le magasin compte les échecs, sans le message d’erreur : pour savoir quelle erreur, il faut le journal de l’application ou une session d’événements étendus sur error_reported.
USE [PachaDataFormation];
SELECT q.query_id, rs.execution_type_desc,
SUM(rs.count_executions) AS executions
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
WHERE rs.execution_type <> 0 -- 3 = Aborted, 4 = Exception
GROUP BY q.query_id, rs.execution_type_desc;
Les bases système
Le Query Store ne s’active ni sur master ni sur tempdb (Surveiller les performances avec le Query Store) : une requête est enregistrée dans la base où elle s’exécute, et une application qui se connecte à master et nomme ses tables en trois parties n’y laisse rien.
Les secondaires lisibles
SQL Server 2025 peut enregistrer dans le magasin du primaire les requêtes des secondaires lisibles d’un groupe de disponibilité. La page Query Store pour les réplicas secondaires la donne en préversion sur toutes les plateformes, désactivée par défaut, à activer base par base ; la page des nouveautés de SQL Server 2025 la dit active par défaut, et c’est la page dédiée qui a été suivie ici. Sans elle, la charge de lecture déportée sur un secondaire n’est pas dans le magasin. Elle n’a pas été essayée ici, faute de groupe de disponibilité.