Un Query Store saturé par des identifiants concaténés

Un cas réel : des appels sp_executesql à identifiant concaténé remplissent le Query Store jusqu’à la lecture seule. Diagnostic, capture CUSTOM, surveillance, correction.

Article coécrit avec Claude, qui a reproduit le cas en laboratoire et mené une partie des recherches.

Le Query Store enregistre les requêtes d’une base, leurs plans et leurs statistiques d’exécution, dans la base elle-même. Il est actif par défaut depuis SQL Server 2022 sur les bases nouvelles, et beaucoup de bases plus anciennes l’ont reçu à la demande d’un éditeur ou d’un DBA. Sa taille est plafonnée par MAX_STORAGE_SIZE_MB ; quand il atteint ce plafond, il passe en lecture seule (READ_ONLY) : il continue de répondre aux requêtes de lecture et aux rapports de SSMS, il n’enregistre plus rien.

Cet article suit un cas relevé chez un client, où quatre formes d’appel ont suffi à remplir un magasin d’un gigaoctet. L’article sur le Query Store en production décrit la saturation en général et le nettoyage par la taille ; celui-ci montre ce qui la provoque quand elle revient malgré le nettoyage, et comment on la corrige.

Les mesures

Le cas : SQL Server 2019 CU32 avec sa mise à jour de sécurité (15.0.4500.2), édition Standard, une base de 2,6 To, 16 cœurs. Les chiffres du client viennent de son Query Store et de son cache de plans ; les noms de la base et des tables sont remplacés.

La reproduction : un conteneur jetable du même build (15.0.4500.2, Standard, Linux) pour la saturation et les événements étendus, et SQL Server 2025 CU7 (17.0.4065.4) Enterprise Developer sous Linux, sur une copie de PachaDataFormation, pour l’identité des requêtes, le paramétrage forcé et la correction. Les deux versions ont donné les mêmes résultats sur les points mesurés des deux côtés, sauf la taille des plans en cache (14 Mo pour 300 plans en 2019, 24 Mo en 2025).

Les heures manquantes

Rien n’a signalé la panne. Le journal d’erreurs de SQL Server ne contenait que les messages d’initialisation du Query Store au démarrage. La page Best practices for monitoring workloads with Query Store le dit elle-même : « Query Store can silently change the operation mode ».

Le premier signe a été une comparaison avant et après une correction, dont les chiffres ne tenaient pas debout. Le magasin découpe son temps en intervalles, ici d’une heure, et chaque intervalle n’existe que si des statistiques y ont été écrites. Une heure passée en lecture seule n’a pas d’intervalle. Compter les intervalles par jour donne les heures dont il reste des statistiques :

USE PachaDataFormation;

SELECT CAST(start_time AS date) AS jour,
       COUNT(*) AS intervalles_enregistres
FROM sys.query_store_runtime_stats_interval
GROUP BY CAST(start_time AS date)
ORDER BY jour;

Chez le client, avec des intervalles d’une heure, la série sur dix jours donnait 22, 20, 18, 17, 16, 20, 17, 15, 12 heures, puis 3 le dernier jour, plus rien après 6 h UTC. Une heure absente peut aussi être une heure sans requête capturée, ou dont le nettoyage a supprimé les statistiques : le trou est un indice, à confronter à l’état du magasin (section suivante) et, si la session existe, aux événements étendus décrits plus bas. Ici, l’état était en lecture seule et les trous s’élargissaient de jour en jour.

Ces trous faussent tout calcul qui divise par la durée de la période. Une consommation moyenne calculée sur une fenêtre de huit heures dont un peu plus de deux seulement étaient enregistrées donnait 0,32 cœur, au lieu de 1,1 sur les heures présentes. Toute comparaison tirée du Query Store doit diviser par les heures enregistrées, et dire lesquelles.

L’état du magasin

L’état se lit dans sys.database_query_store_options :

USE PachaDataFormation;

SELECT desired_state_desc, actual_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb,
       query_capture_mode_desc, size_based_cleanup_mode_desc
FROM sys.database_query_store_options;

Chez le client, l’état demandé était READ_WRITE, l’état réel READ_ONLY, avec readonly_reason à 65536 : le magasin occupait 1 158 Mo pour un plafond de 1 000 Mo, la valeur par défaut depuis SQL Server 2019. readonly_reason est un masque de bits, documenté dans la page de sys.database_query_store_options :

ValeurCause
1base en lecture seule
2base en mode mono-utilisateur
4base en mode d’urgence
8réplica secondaire
65536taille maximale atteinte (MAX_STORAGE_SIZE_MB)
131072nombre d’instructions distinctes au-delà de la limite de mémoire interne
262144mémoire des données en attente d’écriture sur disque au-delà de la limite interne, état temporaire
524288base sans espace disque

Le dépassement du plafond (1 158 Mo pour 1 000) n’est pas une anomalie. La page d’ALTER DATABASE SET précise que la taille n’est contrôlée qu’à l’écriture sur disque, toutes les DATA_FLUSH_INTERVAL_SECONDS (900 par défaut) : entre deux contrôles, le magasin grossit librement. Au contrôle, il passe en lecture seule ; le nettoyage par la taille (SIZE_BASED_CLEANUP_MODE = AUTO) supprime alors les requêtes les moins coûteuses et les plus anciennes, le magasin repasse en lecture-écriture, se remplit, et le cycle recommence. La page sur la gestion du Query Store prévient qu’il n’y a « no guarantee under heavy workloads ».

Pour savoir ce qui occupe la place, les tables internes du magasin se mesurent comme les autres :

USE PachaDataFormation;

SELECT it.name AS table_interne,
       SUM(ps.reserved_page_count) * 8 / 1024 AS mo,
       SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS lignes
FROM sys.internal_tables AS it
JOIN sys.dm_db_partition_stats AS ps ON ps.object_id = it.object_id
WHERE it.name LIKE N'plan_persist%'
GROUP BY it.name
ORDER BY mo DESC;

Chez le client, par table interne :

  • statistiques d’exécution : 598 Mo (1,47 million de lignes) ;
  • plans : 283 Mo ;
  • textes : 155 Mo ;
  • requêtes : 112 Mo.

Et 100 055 requêtes pour 99 980 textes et 100 565 plans : presque un texte, une requête et un plan par requête.

L’identité d’une requête

98 % des requêtes du magasin, 98 039 sur 100 055, appartenaient à quatre formes. Toutes lisaient une table d’historique de mesures par sp_executesql, avec les dates en paramètres et l’identifiant de la mesure concaténé dans le texte : WHERE id = 378291 AND ... pour trois d’entre elles, WHERE id IN (1664158) pour la quatrième.

Le Query Store identifie une requête par le texte exact de l’instruction, les options de session, l’objet qui la contient, le lot quand elle utilise une table temporaire, et le type de paramétrage ; l’article sur le query_id reprend ces règles une par une. Un identifiant différent donne un texte différent, donc un query_id différent. Le query_hash, lui, est calculé sur l’arbre logique de la requête, sans les littéraux (« Zobrist hash over the shape of the individual query », dit la page de sys.query_store_query) : il est le même pour tous les identifiants. Chez le client, une seule forme comptait 28 783 query_id pour un query_hash, les trois autres 27 993, 22 997 et 18 266.

La reproduction sur PachaDataFormation lit l’activité d’un contact sur un mois, dans Elearning.Activite. La table n’a pas d’index sur le contact ; l’exemple en crée un, pour que la boucle ne lise pas 7,8 millions de lignes à chaque appel. Il appelle 300 contacts, 31 fois chacun, avec le contact concaténé et les dates en paramètres, comme l’application du client :

USE PachaDataFormation;

CREATE INDEX ix_Activite_Contact_DateHeure
    ON Elearning.Activite (ContactId, DateHeure) INCLUDE (TypeEvenementCd);
GO

SET NOCOUNT ON;
DECLARE @contacts TABLE (n int IDENTITY, ContactId int);
INSERT @contacts (ContactId)
SELECT DISTINCT TOP (300) ContactId FROM Elearning.Activite ORDER BY ContactId;

DECLARE @tour int = 1, @n int, @sql nvarchar(max);
DECLARE @debut datetime2 = '20260101', @fin datetime2 = '20260201';
DECLARE @r TABLE (DateHeure datetime2, TypeEvenementCd char(4));

WHILE @tour <= 31
BEGIN
    SET @n = 1;
    WHILE @n <= 300
    BEGIN
        -- Le contact est concaténé dans le texte, les dates passent en paramètres
        SET @sql = N'SELECT DateHeure, TypeEvenementCd FROM Elearning.Activite WHERE ContactId = '
                 + CAST((SELECT ContactId FROM @contacts WHERE n = @n) AS nvarchar(10))
                 + N' AND DateHeure >= @Debut AND DateHeure < @Fin';
        INSERT @r EXEC sp_executesql @sql, N'@Debut datetime2, @Fin datetime2',
                                      @Debut = @debut, @Fin = @fin;
        DELETE @r;
        SET @n += 1;
    END;
    SET @tour += 1;
END;

Puis on compte ce que le magasin a enregistré, après avoir forcé l’écriture sur disque :

USE PachaDataFormation;

EXEC sys.sp_query_store_flush_db;

SELECT COUNT(DISTINCT q.query_id) AS query_id,
       COUNT(DISTINCT q.query_hash) AS query_hash,
       COUNT(DISTINCT p.query_plan_hash) AS query_plan_hash,
       MIN(q.query_parameterization_type_desc) AS parametrage
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS t ON t.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
WHERE t.query_sql_text LIKE N'%FROM Elearning.Activite WHERE ContactId = [0-9]%';
query_idquery_hashquery_plan_hashParamétrage
30011User

Trois cents requêtes, un seul plan : le littéral n’a pas changé la forme du plan. Chez le client aussi, chaque forme n’avait qu’un query_plan_hash pour 18 000 à 29 000 query_id. Un littéral peut se justifier quand la distribution des données varie au point qu’un plan unique serait mauvais pour certaines valeurs, ce que traite aussi l’optimisation des plans sensibles aux paramètres depuis SQL Server 2022 ; un seul plan pour toutes les valeurs ne dit pas qu’il est bon pour chacune, et c’est à mesurer avant de passer l’identifiant en paramètre.

Le cache des plans montre le même éclatement, en type Prepared. La requête compte les plans de toutes les bases de l’instance :

USE PachaDataFormation;

SELECT cp.objtype, COUNT(*) AS plans, SUM(cp.usecounts) AS recherches,
       SUM(cp.size_in_bytes) / 1024 AS ko
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.text LIKE N'%FROM Elearning.Activite WHERE ContactId = [0-9]%'
GROUP BY cp.objtype;

Au labo, 300 plans Prepared, trouvés 9 300 fois dans le cache (usecounts compte les recherches dans le cache ; les exécutions se lisent dans sys.dm_exec_query_stats), pour 24 Mo. Chez le client, 136 885 des 140 063 plans Prepared du cache venaient de ces formes, et l’ensemble des plans préparés occupait 5,6 Go. La compilation, elle, coûtait peu : 0,8 ms par plan.

Le rythme d’arrivée explique pourquoi la saturation est venue d’un coup. Pendant des semaines, quelques dizaines de requêtes nouvelles par jour ; puis 15 191, 3 827 (un jour passé en partie en lecture seule), 11 674, 22 225, et 42 470 le dernier jour avant 6 h UTC. À environ 12 Ko par requête dans le magasin, 40 000 requêtes par jour sur 30 jours de rétention demanderaient plus de 10 Go. Le déclencheur probable est une file de traitements bloquée, débloquée deux jours avant la saturation : chaque traitement relit l’historique d’un identifiant. Je ne l’ai pas démontré.

Le paramétrage forcé

Le premier réflexe est l’option de base PARAMETERIZATION FORCED, qui remplace les littéraux par des paramètres à la compilation. La page sur l’architecture du traitement des requêtes l’exclut pour les « Prepared statements that are already parameterized on the client-side application ». J’ai mesuré où passe la frontière. Quatre formes du même SELECT sont appelées pour 50 contacts différents ; chacune a son alias de colonne, pour qu’on la retrouve dans le magasin. Le bloc montre un appel de chaque forme pour un contact ; la mesure les répète pour 50 contacts, dans une copie de la base passée en PARAMETERIZATION FORCED et QUERY_CAPTURE_MODE = ALL :

USE PachaDataFormation;

DECLARE @r TABLE (DateHeure datetime2, TypeEvenementCd char(4));
DECLARE @id nvarchar(10) = N'3', @sql nvarchar(max);
DECLARE @debut datetime2 = '20260101', @fin datetime2 = '20260201';

-- A : contact concaténé, dates en paramètres
SET @sql = N'SELECT DateHeure, TypeEvenementCd AS forme_A FROM Elearning.Activite WHERE ContactId = '
         + @id + N' AND DateHeure >= @Debut AND DateHeure < @Fin';
INSERT @r EXEC sp_executesql @sql, N'@Debut datetime2, @Fin datetime2', @Debut = @debut, @Fin = @fin;

-- B : aucune liste de paramètres, tout en littéraux
SET @sql = N'SELECT DateHeure, TypeEvenementCd AS forme_B FROM Elearning.Activite WHERE ContactId = '
         + @id + N' AND DateHeure >= ''20260101''';
INSERT @r EXEC sp_executesql @sql;

-- C : un paramètre déclaré, que l'instruction n'utilise pas
SET @sql = N'SELECT DateHeure, TypeEvenementCd AS forme_C FROM Elearning.Activite WHERE ContactId = '
         + @id + N' AND DateHeure >= ''20260101''';
INSERT @r EXEC sp_executesql @sql, N'@inutile int', @inutile = 0;

-- D : lot ad hoc par EXEC
SET @sql = N'SELECT DateHeure, TypeEvenementCd AS forme_D FROM Elearning.Activite WHERE ContactId = '
         + @id + N' AND DateHeure >= ''20260101''';
INSERT @r EXEC (@sql);
Formequery_idParamétrage
A. sp_executesql, contact concaténé, dates en paramètres (le cas du client)50User
B. sp_executesql sans liste de paramètres, tout en littéraux1Forced
C. sp_executesql avec un paramètre déclaré que l’instruction n’utilise pas50User
D. EXEC (@sql), tout en littéraux1Forced

La frontière est la liste de paramètres de sp_executesql. Sans elle, l’appel est traité comme un lot ad hoc, et le paramétrage forcé s’applique. Avec elle, même pour un paramètre que l’instruction n’utilise pas, l’instruction est considérée comme paramétrée par le client, et le moteur ne touche plus aux littéraux qui restent. La forme C écarte l’autre exclusion de la même page, les instructions qui référencent des variables : rien n’y référence @inutile. Le résultat est le même sur SQL Server 2019 et 2025.

Sur la 2019, deux appels de même texte, l’un par sp_executesql sans liste de paramètres, l’autre par EXEC, ont donné un seul query_id, avec 100 exécutions : le magasin les traite comme la même requête. Le texte enregistré est celui que le moteur a réécrit, sans littéraux, avec les mots-clés en minuscules et des espaces autour des points et des opérateurs ; pour la forme B :

(@0 int,@1 varchar(8000))select DateHeure , TypeEvenementCd as forme_B from Elearning . Activite where ContactId = @0 and DateHeure > = @1

Un filtre LIKE N'%Elearning.Activite%' ne trouve donc pas ces requêtes dans sys.query_store_query_text ; il faut chercher Elearning . Activite, ou un alias de colonne comme ici. Un commentaire ne fait pas un bon marqueur non plus : placé après l’instruction, il est hors de son texte et n’apparaît pas dans le magasin ; placé à l’intérieur, il est conservé, sauf quand le paramétrage forcé réécrit le texte.

Une application qui passe ses dates en paramètres et concatène son identifiant échappe donc au paramétrage forcé, et au paramétrage simple aussi : en PARAMETERIZATION SIMPLE, la forme A a donné 50 query_id. Celle qui ne déclare aucun paramètre et concatène tout, dates comprises, serait regroupée par PARAMETERIZATION FORCED, même en passant par sp_executesql ; sur une requête aussi simple que celle-ci, le paramétrage simple l’a déjà regroupée (forme B : un seul query_id, de type Simple). Je n’ai pas essayé les repères de plan TEMPLATE, qui appliquent le paramétrage forcé à une seule forme de requête.

Le mode AUTO, le cache et le nettoyage

Le mode de capture AUTO, par défaut depuis SQL Server 2019, n’enregistre une requête qu’à partir de 30 exécutions, d’une seconde de processeur de compilation ou de 100 ms de processeur d’exécution sur sa période d’évaluation ; l’article sur les modes de capture mesure ces seuils. Il écarte les requêtes ad hoc lancées une fois. Ici, chaque identifiant était relu en moyenne 1 789 fois : toutes les requêtes passaient le seuil d’exécutions, alors qu’à elles toutes elles ne pesaient que 0,5 % du processeur enregistré.

L’option d’instance optimize for ad hoc workloads, active chez le client, vise les lots ad hoc exécutés une seule fois : elle ne garde qu’un stub de plan à la première compilation, et le plan complet à la deuxième. Les plans en cause ici sont de type Prepared, et chacun était exécuté plus de 1 300 fois.

Le nettoyage par la taille tournait : deux des formes perdaient environ 1 300 query_id en cinq minutes. Il n’a pas suivi des dizaines de milliers d’arrivées par jour.

Le mode CUSTOM

Le mode CUSTOM applique la règle d’AUTO avec des seuils choisis. Une requête est enregistrée dès qu’elle atteint l’un des trois, sur la période fixée par STALE_CAPTURE_POLICY_THRESHOLD. Le but ici était de relever le seuil d’exécutions assez haut pour écarter les requêtes par identifiant, sans perdre les requêtes qui coûtent.

Plutôt que de choisir les seuils au jugé, je les ai simulés sur le contenu du magasin. Pour chaque requête et chaque jour, la requête suivante prend le nombre d’exécutions et le processeur d’exécution, et pour chaque requête la compilation cumulée ; elle compte ensuite, pour chaque jeu de seuils, combien de requêtes auraient été gardées. Elle sépare les doublons, les requêtes dont le query_hash a plus de 100 query_id, des autres :

USE PachaDataFormation;
SET NOCOUNT ON;

DROP TABLE IF EXISTS #parjour;
SELECT p.query_id,
       CAST(i.start_time AS date) AS jour,
       SUM(rs.count_executions) AS executions,
       SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0 AS cpu_ms
INTO #parjour
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i
    ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
GROUP BY p.query_id, CAST(i.start_time AS date);

DROP TABLE IF EXISTS #requete;
SELECT q.query_id,
       MAX(j.executions) AS max_executions_jour,
       MAX(j.cpu_ms) AS max_cpu_ms_jour,
       SUM(j.cpu_ms) AS cpu_ms_total,
       -- Temps processeur de compilation : liaison et optimisation, en microsecondes
       MAX((q.avg_bind_cpu_time + q.avg_optimize_cpu_time) * q.count_compiles) / 1000.0 AS compilation_ms,
       CASE WHEN MAX(h.nb) > 100 THEN 1 ELSE 0 END AS doublon
INTO #requete
FROM sys.query_store_query AS q
JOIN #parjour AS j ON j.query_id = q.query_id
JOIN (SELECT query_hash, COUNT(*) AS nb
      FROM sys.query_store_query GROUP BY query_hash) AS h
    ON h.query_hash = q.query_hash
GROUP BY q.query_id;

SELECT s.jeu, s.executions, s.cpu_execution_ms, s.compilation_ms,
       SUM(CASE WHEN r.doublon = 1 THEN 1 ELSE 0 END) AS doublons,
       SUM(CASE WHEN r.doublon = 1 AND g.garde = 1 THEN 1 ELSE 0 END) AS doublons_gardes,
       SUM(CASE WHEN r.doublon = 0 THEN 1 ELSE 0 END) AS autres,
       SUM(CASE WHEN r.doublon = 0 AND g.garde = 1 THEN 1 ELSE 0 END) AS autres_gardees,
       CAST(100.0 * SUM(CASE WHEN r.doublon = 0 AND g.garde = 1 THEN r.cpu_ms_total ELSE 0 END)
            / NULLIF(SUM(CASE WHEN r.doublon = 0 THEN r.cpu_ms_total ELSE 0 END), 0)
            AS decimal(5,1)) AS pct_cpu_autres_garde
FROM (VALUES ('A (AUTO)', 30, 100, 1000), ('B', 500, 1000, 1000), ('C', 2000, 1000, 1000),
             ('D', 5000, 2000, 2000), ('E', 20000, 5000, 5000))
     AS s (jeu, executions, cpu_execution_ms, compilation_ms)
CROSS JOIN #requete AS r
CROSS APPLY (SELECT CASE WHEN r.max_executions_jour >= s.executions
                          OR r.max_cpu_ms_jour >= s.cpu_execution_ms
                          OR r.compilation_ms >= s.compilation_ms
                         THEN 1 ELSE 0 END) AS g (garde)
GROUP BY s.jeu, s.executions, s.cpu_execution_ms, s.compilation_ms
ORDER BY s.executions;

Le résultat chez le client :

JeuExécutionsCPU d’exécution (ms)Compilation (ms)Doublons gardés, sur 89 976 avec des statistiquesAutres gardées, sur 2 007CPU des autres gardé
A (AUTO)301001 00047 0581 903100 %
B5001 0001 0004 13482899,9 %
C (retenu)2 0001 0001 00082566199,9 %
D5 0002 0002 00051753599,9 %
E20 0005 0005 00022538699,8 %

Le jeu C écarte 99 % des doublons et garde 99,9 % du processeur des autres requêtes. Les requêtes qu’il perd sont celles qui ne coûtent rien et reviennent peu : 1 346 requêtes sur 2 007, pour un dixième de pour cent du processeur. Le jeu D retire 308 doublons de plus, mais perd 126 requêtes ordinaires de plus.

La simulation a une limite : elle ne voit que ce que le magasin a enregistré. Elle donne 47 058 doublons gardés pour le mode AUTO, alors que le magasin réel, en AUTO, contenait 89 976 doublons qui avaient des statistiques d’exécution. L’écart vient des exécutions que la simulation ne compte pas : celles qui ont précédé la capture de chaque requête, et celles des heures passées en lecture seule. Elles ont compté pour la capture réelle, pas pour la simulation. Au labo, sur le magasin saturé décrit plus bas, l’effet est extrême : chaque doublon y a été capturé en AUTO à sa trentième exécution, n’y garde que 2 exécutions sur 31, et la simulation n’en garde aucun sur 1 289, même au seuil de 30. Elle compte aussi par jour civil, alors que la documentation ne dit pas comment la période de STALE_CAPTURE_POLICY_THRESHOLD est découpée, et elle prend la compilation cumulée sur toute la vie de la requête, que le magasin ne détaille pas par jour : une requête qui compile 600 ms deux jours de suite passe le seuil de 1 000 ms dans la simulation, pas dans la capture. Sur ce point, elle garde trop de requêtes. Les nombres d’exécutions de la simulation sont des minima ; elle sert à classer des jeux de seuils, pas à prédire le volume, et le jeu retenu se vérifie ensuite sur la taille du magasin et sur les requêtes réellement capturées.

La commande appliquée :

ALTER DATABASE PachaDataFormation SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = CUSTOM,
    QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 2000,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 1000
    ),
    MAX_STORAGE_SIZE_MB = 2048
);

Le magasin est repassé en READ_WRITE tout de suite, readonly_reason à 0, à 1 154 Mo. Les requêtes des procédures stockées restent enregistrées en CUSTOM comme en AUTO, d’après la page sur la gestion du Query Store ; les seuils filtrent les requêtes ad hoc et préparées.

Les requêtes déjà présentes restent jusqu’à la fin de la rétention (STALE_QUERY_THRESHOLD_DAYS, 30 jours par défaut), sauf celles que le nettoyage par la taille retire avant. Pour libérer la place tout de suite, on peut les supprimer par query_hash, une à une, avec sp_query_store_remove_query. Le query_hash se lit dans sys.query_store_query pour l’une des requêtes de la forme ; celui de l’exemple est à remplacer par le vôtre :

USE PachaDataFormation;

-- Remplacez cette valeur par le query_hash de la forme à purger
DECLARE @hash binary(8) = 0x1A2B3C4D5E6F7A8B;
DECLARE @query_id bigint;

DECLARE requetes CURSOR LOCAL FAST_FORWARD FOR
    SELECT query_id FROM sys.query_store_query WHERE query_hash = @hash;
OPEN requetes;
FETCH NEXT FROM requetes INTO @query_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sys.sp_query_store_remove_query @query_id = @query_id;
    FETCH NEXT FROM requetes INTO @query_id;
END;
CLOSE requetes;
DEALLOCATE requetes;

Au labo, les 50 requêtes d’une forme ont été supprimées ainsi. Chez le client, ce serait plusieurs dizaines de milliers d’appels : à faire hors des heures chargées, par lots.

La surveillance

Puisque le passage en lecture seule ne laisse aucune trace, il faut aller le chercher. Un travail de l’Agent SQL Server sonde le magasin toutes les quinze minutes, au rythme des écritures sur disque, et écrit un message par xp_logevent, qui va dans le journal d’erreurs de SQL Server et, sous Windows, dans le journal des événements, où un outil de supervision peut le lire :

USE PachaDataFormation;

DECLARE @message nvarchar(2048);

SELECT @message = CASE
    WHEN actual_state <> desired_state
        THEN N'Query Store de ' + DB_NAME() + N' en ' + actual_state_desc
           + N', readonly_reason = ' + CAST(readonly_reason AS nvarchar(10))
    WHEN current_storage_size_mb >= 0.9 * max_storage_size_mb
        -- Message sans accent : sous Linux, xp_logevent abîme les caractères accentués
        THEN N'Query Store de ' + DB_NAME() + N' : '
           + CAST(current_storage_size_mb AS nvarchar(20)) + N' Mo sur '
           + CAST(max_storage_size_mb AS nvarchar(20))
    END
FROM sys.database_query_store_options;

IF @message IS NOT NULL
    EXEC master.dbo.xp_logevent 60000, @message, WARNING;

Le seuil de 90 % est celui où démarre le nettoyage par la taille, mais il n’est contrôlé qu’à l’écriture sur disque : si le magasin reste au-dessus d’un sondage à l’autre, le nettoyage ne le suit plus.

Une session d’événements étendus garde en plus l’historique. Le package qds a un événement pour chacune des causes de lecture seule propres au magasin (65536 à 524288) et pour chaque étape du nettoyage, dans le canal Operational :

ÉvénementCe qu’il signale
query_store_disk_size_over_limit, query_store_disk_size_below_limitpassage au-dessus et retour sous MAX_STORAGE_SIZE_MB (65536)
query_store_stmt_hash_map_over_memory_limit, query_store_stmt_hash_map_memory_below_read_write_targetlimite de mémoire des instructions (131072)
query_store_buffered_items_over_memory_limit, query_store_buffered_items_memory_below_read_write_targetlimite de mémoire des écritures en attente (262144)
query_store_database_out_of_disk_spacebase sans espace disque (524288)
query_store_size_retention_cleanup_started, _finished, _skippednettoyage par la taille démarré, terminé, ou sauté
query_store_severe_error_shutdownarrêt du magasin sur erreur grave

Aucune session livrée avec SQL Server ne les capte. Cette session les garde dans un fichier, et donne l’historique daté de chaque passage en lecture seule et de chaque nettoyage :

CREATE EVENT SESSION qds_historique ON SERVER
    ADD EVENT qds.query_store_disk_size_over_limit,
    ADD EVENT qds.query_store_disk_size_below_limit,
    ADD EVENT qds.query_store_stmt_hash_map_over_memory_limit,
    ADD EVENT qds.query_store_stmt_hash_map_memory_below_read_write_target,
    ADD EVENT qds.query_store_buffered_items_over_memory_limit,
    ADD EVENT qds.query_store_buffered_items_memory_below_read_write_target,
    ADD EVENT qds.query_store_database_out_of_disk_space,
    ADD EVENT qds.query_store_size_retention_cleanup_started,
    ADD EVENT qds.query_store_size_retention_cleanup_finished,
    ADD EVENT qds.query_store_size_retention_cleanup_skipped,
    ADD EVENT qds.query_store_severe_error_shutdown
    ADD TARGET package0.event_file (SET filename = N'qds_historique', max_file_size = 50)
    WITH (STARTUP_STATE = ON);

ALTER EVENT SESSION qds_historique ON SERVER STATE = START;

Au labo, sur la 2019, j’ai rejoué la charge dans une base jetable : des appels à identifiant concaténé, chaque identifiant 31 fois pour passer le seuil d’AUTO, un magasin limité à 5 Mo, une écriture sur disque toutes les 60 secondes et des intervalles d’une minute. La session a donné cette séquence, en heure UTC :

HeureÉvénementDétail
10:50:08query_store_disk_size_over_limit6 128 Ko pour un plafond de 5 120
10:50:08 à 10:50:17query_store_size_retention_cleanup_started, puis _finished711 requêtes supprimées, environ 2 Mo
10:50:17query_store_disk_size_below_limit3 952 Ko
10:51:07query_store_disk_size_over_limit5 296 Ko, le magasin passe en lecture seule
de 10:51:07 à 11:05:07query_store_size_retention_cleanup_skipped, à chaque écriture, quinze foistime_since_last_cleanup_seconds jusqu’à 898, min_repeat_period_seconds à 900
11:06:07 à 11:06:11query_store_size_retention_cleanup_started, puis _finished420 requêtes supprimées
11:06:11query_store_disk_size_below_limit4 016 Ko, retour en lecture-écriture

Le nettoyage par la taille ne repasse pas moins de 900 secondes après le précédent : l’événement _skipped le dit par ses deux champs, et la documentation n’en parle pas. La même période se mesure sur SQL Server 2022 et 2025, avec une écriture sur disque toutes les 60 secondes (second passage 900 secondes après le premier) comme toutes les 180 (au premier contrôle qui suit les 900 secondes). Un magasin qui dépasse de nouveau son plafond juste après un nettoyage reste donc en lecture seule jusqu’à un quart d’heure. Pendant ce temps, les intervalles de sys.query_store_runtime_stats_interval sautent de 10 h 51 à 11 h 06 : le même trou que chez le client, à l’échelle d’un banc.

La session enregistre sans prévenir personne ; l’alerte reste le sondage, et pour la taille, il voit le passage au plus un intervalle de sondage après l’écriture qui l’a provoqué.

La correction dans l’application

La correction durable est de passer l’identifiant en paramètre, comme les dates :

USE PachaDataFormation;

DECLARE @r TABLE (DateHeure datetime2, TypeEvenementCd char(4));

INSERT @r
EXEC sp_executesql
    N'SELECT DateHeure, TypeEvenementCd FROM Elearning.Activite
      WHERE ContactId = @ContactId AND DateHeure >= @Debut AND DateHeure < @Fin',
    N'@ContactId int, @Debut datetime2, @Fin datetime2',
    @ContactId = 3, @Debut = '20260101', @Fin = '20260201';

Pour la forme IN, la liste passe dans un paramètre de type table :

USE PachaDataFormation;

CREATE TYPE dbo.ListeContacts AS TABLE (ContactId int PRIMARY KEY);
GO

DECLARE @liste dbo.ListeContacts;
INSERT @liste VALUES (3), (4);

EXEC sp_executesql
    N'SELECT DateHeure, TypeEvenementCd FROM Elearning.Activite
      WHERE ContactId IN (SELECT ContactId FROM @Contacts) AND DateHeure >= @Debut',
    N'@Contacts dbo.ListeContacts READONLY, @Debut datetime2',
    @Contacts = @liste, @Debut = '20260101';

Appelées pour 50 contacts différents, chacune de ces deux formes ne donne qu’un query_id, avec 50 exécutions. Côté application, c’est un SqlParameter de plus, et pour la liste un paramètre de type Structured. La suggestion est partie chez l’éditeur. En attendant, la capture CUSTOM tient le magasin, et la surveillance dira si elle cesse de suffire.