SQL ad hoc et listes IN de littéraux : la pollution du cache de plans
Categories:
13 minutes à lire
Une liste IN (12, 47, 3) construite par concaténation donne un texte différent dès que les valeurs changent, et le paramétrage simple ne la touche jamais : chaque nouveau texte compile et laisse un plan de 80 Ko environ dans le cache. optimize for ad hoc workloads réduit la mémoire, pas les compilations. Le paramétrage forcé rend un plan par longueur de liste, jusqu’à 2 097 littéraux, et c’est cette longueur qui borne aussi le plan forcé et les indicateurs du Query Store. Côté code, une liste de paramètres de longueur arrondie ou une table temporaire gardent une estimation proche de la liste ; un paramètre table garde celle de son premier appel, et STRING_SPLIT et OPENJSON estiment 50 lignes quelle que soit la liste.
Le coût d’une liste de littéraux
Une application qui cherche les inscriptions d’une liste de contacts construit souvent sa requête en collant les identifiants dans le texte :
SELECT InscriptionId, DateCreation
FROM Inscription.Inscription
WHERE ContactId IN (16717, 32469, 48213);
Pour SQL Server, le texte est la clé du cache : deux lots qui diffèrent d’un caractère sont deux entrées, chacune avec son plan compilé. Les mesures de cet article viennent d’une copie jetable de PachaDataFormation sur SQL Server 2025 CU7, où Inscription.Inscription (151 236 lignes) a reçu un index sur ContactId, et Inscription.LigneFacture (250 198 lignes) un index sur InscriptionId. Le chapitre Optimiser les procédures stockées du livre décrit le cache des requêtes ad hoc en général ; cet article s’en tient aux listes.
Le paramétrage simple, que SQL Server applique par défaut, laisse ces listes telles quelles. Il ne vise qu’une petite classe de requêtes. Dans l’essai, WHERE InscriptionId = 42 sur la clé primaire était paramétré (une entrée Prepared en (@1 tinyint), réutilisée par InscriptionId = 43), WHERE ContactId = 42 ne l’était pas, et les listes IN sont restées en littéraux, même sur la clé primaire. Chaque liste sur ContactId laissait un plan Adhoc de 80 Ko pour trois à cinq valeurs, 96 Ko pour dix.
Sur 2 000 listes distinctes de cinq contacts, envoyées en lots séparés, le cache gardait 2 000 plans Adhoc utilisés une fois, pour 160 992 Ko, et le compteur d’instance SQL Compilations/sec avait pris environ 2 000 compilations (de 2 001 à 2 010 selon les séries, le compteur comptant aussi le reste de l’instance). Le prix se paie deux fois : en mémoire, le cache de plans prend sa place sur la même mémoire que les pages de données ; en processeur, chaque appel compile. La compilation grossit avec la liste : une liste de 20 000 identifiants a demandé 6,0 s de compilation pour 170 ms de processeur à l’exécution. La page de IN prévient qu’au-delà de plusieurs milliers de valeurs la requête peut échouer (erreurs 8623 ou 8632), et conseille de mettre la liste dans une table.
Mesurer la pollution
Les plans d’une même famille ont des textes différents mais le même query_hash, puisque seule une constante change. Regrouper les plans ad hoc du cache par query_hash fait sortir les familles :
WITH plans AS (
SELECT DISTINCT qs.query_hash, qs.plan_handle
FROM sys.dm_exec_query_stats AS qs
)
SELECT TOP (10)
p.query_hash,
COUNT(*) AS plans_en_cache,
SUM(cp.usecounts) AS utilisations,
SUM(CAST(cp.size_in_bytes AS bigint)) / 1024 AS ko_en_cache,
MIN(LEFT(st.text, 200)) AS exemple
FROM plans AS p
JOIN sys.dm_exec_cached_plans AS cp ON cp.plan_handle = p.plan_handle
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS st
WHERE cp.objtype = N'Adhoc'
GROUP BY p.query_hash
HAVING COUNT(*) > 1
ORDER BY ko_en_cache DESC;
Dans l’essai, la première ligne était la famille des 2 000 listes : 2 000 plans, 2 000 utilisations, 160 992 Ko. Un lot qui contient plusieurs instructions est compté dans la famille de chacune. Le cache ne garde que ce qu’il n’a pas encore évincé, et rien après un redémarrage : sur un serveur sous pression mémoire, la requête voit moins de plans que l’application n’en a produits. Elle part aussi de sys.dm_exec_query_stats, qui n’a pas de ligne pour les coquilles du paramétrage forcé (section suivante) : les deux relecteurs de cet article l’ont mesuré, et une famille paramétrée de force disparaît de la requête. Pour compter ces entrées, regrouper directement sys.dm_exec_cached_plans par objtype et cacheobjtype.
Le Query Store garde une trace plus longue, si son mode de capture retient ces requêtes (AUTO écarte les requêtes rares et peu coûteuses, ALL les garde toutes). Les bonnes pratiques du Query Store proposent de comparer le nombre de query_hash distincts au nombre de requêtes : un rapport proche de 1 signale des requêtes vraiment différentes, un rapport très inférieur à 1 des requêtes qui ne diffèrent que par leurs constantes.
USE [PachaDataFormation];
SELECT
COUNT(*) AS requetes,
COUNT(DISTINCT query_hash) AS query_hash_distincts,
CAST(COUNT(DISTINCT query_hash) * 1.0 / NULLIF(COUNT(*), 0) AS decimal(5, 3)) AS rapport
FROM sys.query_store_query
WHERE query_parameterization_type = 0;
La base de l’essai, en capture ALL, comptait 2 007 requêtes non paramétrées pour 8 query_hash.
optimize for ad hoc workloads
L’option d’instance optimize for ad hoc workloads ne garde, à la première compilation d’un lot ad hoc, qu’un stub ; le plan complet n’entre dans le cache qu’à la deuxième exécution du même texte.
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'optimize for ad hoc workloads', 1;
RECONFIGURE;
Dans l’essai, un stub de liste pesait 456 octets, quelle que soit la longueur de la liste, contre 81 920 pour le plan complet d’une liste de cinq valeurs, et les 2 000 listes ne prenaient plus que 890 Ko au lieu de 160 992. Une même liste exécutée trois fois laissait un plan complet utilisé deux fois : la première exécution ne garde que le stub, la deuxième compile de nouveau et garde le plan, la troisième le réutilise. La page de l’option dit qu’on trouve les stubs sous Compiled Plan dans la colonne cacheobjtype de sys.dm_exec_cached_plans ; sur SQL Server 2025, la colonne affichait Compiled Plan Stub.
L’option ne supprime aucune compilation : les 2 000 listes ont compilé 2 000 fois, et la série a duré autant (6,1 s contre 6,0 s sans l’option, médiane de cinq séries alternées, aller-retour du client compris). Elle a aussi un prix pour le diagnostic : un stub n’a pas de plan, et sys.dm_exec_query_plan rend NULL pour une requête qui n’a tourné qu’une fois.
Le paramétrage forcé
PARAMETERIZATION FORCED remplace par des paramètres les littéraux de toutes les requêtes de la base, à quelques exceptions près.
USE [PachaDataFormation];
ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED;
Les listes sont paramétrées, mais chaque longueur a son texte : une liste de trois valeurs devient (@0 int,@1 int,@2 int)select InscriptionId , DateCreation from Inscription . Inscription where ContactId in ( @0 , @1 , @2 ), une liste de cinq un autre texte à cinq paramètres. Dans l’essai, cinq listes de trois, cinq et dix valeurs ont donné trois plans Prepared, un par longueur. Une application dont les listes vont de 1 à 200 éléments peut donc garder jusqu’à 200 plans par requête, davantage si les types varient : un entier qui dépasse int est paramétré en numeric(38,0), ce qui donne un autre plan, comme un relecteur l’a mesuré. Cela reste loin des milliers de textes distincts.
Sur les 2 000 listes de cinq valeurs, le paramétrage forcé laissait un seul plan Prepared, mais aussi 2 000 entrées Adhoc de 16 Ko chacune : ce sont des coquilles, qui gardent le texte d’origine, le plan lui-même étant dans l’entrée Prepared. Le compteur SQL Compilations/sec montait encore d’environ 2 000, mais la série durait 2,5 s au lieu de 6,0 s : chaque appel paramètre son texte et retrouve le plan, sans l’optimiser de nouveau. Avec optimize for ad hoc workloads en plus, ces coquilles devenaient des stubs de 136 octets (265 Ko pour les 2 000), et la série durait 2,8 s, contre 2,5 s sans l’option.
La documentation donne les limites. Une instruction qui contient plus de 2 097 littéraux n’est pas paramétrée, ni une instruction qui porte l’indicateur RECOMPILE, ni celles des procédures stockées, qui n’en ont pas besoin. L’option vide le cache de plans de la base au moment où on la change. Elle rend l’optimiseur moins enclin à utiliser une vue indexée ou un index sur une colonne calculée, et un plan unique sert toutes les valeurs d’une même longueur de liste, avec les risques de la sensibilité aux paramètres. Les types changent aussi : l’essai a paramétré ContactId = 42 en int, là où le paramétrage simple avait choisi tinyint pour InscriptionId = 42.
Plan forcé et indicateurs du Query Store
Le Query Store identifie une requête par son texte, avec ses options de session. Chaque liste de littéraux y a donc son query_id, et un plan forcé ou un indicateur Query Store posé sur l’un de ces query_id ne s’applique qu’au texte exact qu’il désigne, que l’application n’enverra probablement plus. Dans l’essai, les 2 000 listes avaient produit 2 000 query_id pour le même query_hash.
Le paramétrage forcé ramène le problème à un query_id par longueur de liste. L’indicateur OPTION (MAXDOP 1) posé par sp_query_store_set_hints sur la requête paramétrée de cinq valeurs s’appliquait à une nouvelle liste de cinq valeurs (l’attribut QueryStoreStatementHintId apparaissait dans son plan), pas à une liste de six. Les deux versions, ad hoc et paramétrée, avaient le même query_hash, ce qui permet de retrouver la seconde à partir de la première.
Sans changer toute la base, un repère de plan de type TEMPLATE paramètre de force une seule forme de requête :
USE [PachaDataFormation];
DECLARE @modele nvarchar(max), @parametres nvarchar(max);
EXEC sys.sp_get_query_template
N'SELECT COUNT(*) FROM Inscription.Inscription WHERE ContactId IN (1, 2, 3, 4, 5);',
@modele OUTPUT,
@parametres OUTPUT;
EXEC sys.sp_create_plan_guide
@name = N'pg_inscription_contact_5',
@stmt = @modele,
@type = N'TEMPLATE',
@module_or_batch = NULL,
@params = @parametres,
@hints = N'OPTION (PARAMETERIZATION FORCED)';
Le modèle est le texte normalisé à cinq paramètres. Dans l’essai, trois listes de cinq valeurs, dont une écrite sans espaces après les virgules, ont partagé un plan Prepared ; une liste de six n’était pas concernée. La normalisation absorbe les espaces et la casse des mots-clés, pas celle des noms : dans l’essai, select et from en minuscules entraient dans le repère, count(*) en minuscules ou inscription.inscription y échappaient. Il faut un repère par longueur de liste, ce qui ne convient qu’à une requête dont les longueurs sont peu nombreuses. Pour le supprimer : EXEC sys.sp_control_plan_guide N'DROP', N'pg_inscription_contact_5';.
Les alternatives côté code
La correction durable est dans l’application, qui doit envoyer la liste comme une donnée et non comme du texte. Quatre formes ont été comparées sur la même requête, le montant hors taxe facturé pour les inscriptions d’une liste de contacts, avec 500 puis 20 000 identifiants :
SELECT SUM(lf.MontantHT)
FROM Inscription.Inscription AS i
JOIN Inscription.LigneFacture AS lf ON lf.InscriptionId = i.InscriptionId
WHERE i.ContactId IN (16717, 32469, 48213);
Le paramètre table (TVP) passe la liste comme une table typée, en un seul paramètre. Un appel par sp_executesql le montre en T-SQL ; une application le remplit depuis son code (en .NET, un SqlParameter de type SqlDbType.Structured). Les deux index sont ceux de l’essai, que PachaDataFormation n’a pas :
USE [PachaDataFormation];
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'ix_inscription_contact' AND object_id = OBJECT_ID(N'Inscription.Inscription'))
CREATE INDEX ix_inscription_contact ON Inscription.Inscription (ContactId);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'ix_lignefacture_inscription' AND object_id = OBJECT_ID(N'Inscription.LigneFacture'))
CREATE INDEX ix_lignefacture_inscription ON Inscription.LigneFacture (InscriptionId) INCLUDE (MontantHT);
IF TYPE_ID(N'dbo.ListeEntiers') IS NULL
CREATE TYPE dbo.ListeEntiers AS TABLE (Id int PRIMARY KEY);
GO
DECLARE @contacts dbo.ListeEntiers;
INSERT INTO @contacts (Id) VALUES (16717), (32469), (48213);
EXEC sys.sp_executesql
N'SELECT SUM(lf.MontantHT)
FROM Inscription.Inscription AS i
JOIN Inscription.LigneFacture AS lf ON lf.InscriptionId = i.InscriptionId
WHERE i.ContactId IN (SELECT Id FROM @ids);',
N'@ids dbo.ListeEntiers READONLY',
@ids = @contacts;
STRING_SPLIT et OPENJSON prennent la liste dans une seule chaîne, ce qui ne demande aucun type côté serveur :
USE [PachaDataFormation];
EXEC sys.sp_executesql
N'SELECT SUM(lf.MontantHT)
FROM Inscription.Inscription AS i
JOIN Inscription.LigneFacture AS lf ON lf.InscriptionId = i.InscriptionId
WHERE i.ContactId IN (SELECT CAST(value AS int) FROM STRING_SPLIT(@liste, '',''));',
N'@liste nvarchar(max)',
@liste = N'16717,32469,48213';
EXEC sys.sp_executesql
N'SELECT SUM(lf.MontantHT)
FROM Inscription.Inscription AS i
JOIN Inscription.LigneFacture AS lf ON lf.InscriptionId = i.InscriptionId
WHERE i.ContactId IN (SELECT Id FROM OPENJSON(@json) WITH (Id int ''$''));',
N'@json nvarchar(max)',
@json = N'[16717,32469,48213]';
La table temporaire, enfin, se remplit puis se joint, comme le conseille la page de IN. Les plans réels, cache de la base vidé avant chaque exécution, médiane de trois exécutions :
| Forme | 500 contacts : estimation de la liste | CPU | 20 000 contacts : estimation | CPU | 20 000 contacts : compilation |
|---|---|---|---|---|---|
| Littéraux | 500 | 72 ms | 20 000 | 170 ms | 5 981 ms |
| Paramètre table | 500 | 32 ms | 20 000 | 135 ms | 7 ms |
| Table temporaire | 500 | 69 ms | 20 000 | 140 ms | 15 ms |
STRING_SPLIT | 50 | 8 ms | 50 | 263 ms | 11 ms |
OPENJSON | 50 | 8 ms | 50 | 228 ms | 12 ms |
Le paramètre table et la table temporaire ont donné à l’optimiseur le vrai nombre de lignes : pour 20 000 contacts, il a choisi une jointure par hachage et une lecture complète de l’index sur LigneFacture. Ce tableau a été mesuré cache vidé, chaque forme compilant pour sa propre liste, et c’est là qu’il trompe pour le paramètre table : son plan est mis en cache avec le nombre de lignes du premier appel, puis réutilisé. Compilé pour trois contacts, le même texte a servi pour 20 000 avec une estimation de 3 lignes, des boucles imbriquées et 190 ms de processeur au lieu de 135. La table temporaire, elle, a été recompilée au second appel avec une estimation de 20 000, comme les deux relecteurs l’ont mesuré. STRING_SPLIT et OPENJSON estiment 50 lignes, quelle que soit la longueur de la chaîne, et l’optimiseur a choisi des boucles imbriquées dans les deux cas. Pour 500 contacts, ces boucles ont pris le moins de processeur des cinq formes ; pour 20 000, elles faisaient 20 000 recherches dans Inscription puis 47 806 dans LigneFacture, pour 72 206 lignes estimées à 26, et prenaient près de deux fois plus de processeur que le paramètre table ou la table temporaire. Le plan de la chaîne est construit pour 50 lignes, et reste le même quand les listes s’allongent : à vérifier sur les tailles que l’application envoie réellement.
Ces chiffres sont la médiane de trois exécutions par forme, sur une instance partagée : ils montrent les écarts de plan, et une mesure de débit demanderait davantage de répétitions.
Entity Framework Core est passé par ces trois solutions. Jusqu’à la version 7, il écrivait la liste en littéraux ; les versions 8 et 9 l’envoyaient en JSON, dépliée par OPENJSON, avec l’estimation fixe qui va avec ; EF Core 10 envoie un paramètre par valeur et complète la liste jusqu’à une longueur arrondie (dix paramètres pour huit valeurs, les deux derniers répétant le dernier), pour limiter le nombre de textes distincts tout en donnant la cardinalité à l’optimiseur. Le mode se règle globalement par UseParameterizedCollectionMode, et requête par requête en enveloppant la collection dans EF.Constant() ou EF.Parameter(). La même technique s’écrit à la main dans n’importe quel code : arrondir la longueur de la liste à quelques paliers et répéter la dernière valeur.
Quelle réponse pour quelle situation
- Pour la mémoire seulement, quand le code ne peut pas changer :
optimize for ad hoc workloads, sans effet sur les compilations. - Pour les compilations, quand le code ne peut pas changer et que les listes restent sous 2 097 valeurs : le paramétrage forcé, sur la base entière, ou par un repère
TEMPLATEpour quelques formes de requêtes, après avoir vérifié qu’aucune requête importante ne dépend d’une vue indexée ou d’un index sur une colonne calculée. - Pour corriger, côté code : des paramètres en nombre arrondi pour les listes courtes, une table temporaire pour les listes longues ou de taille très variable. Un paramètre table convient quand les listes ont des tailles du même ordre, ou avec
OPTION (RECOMPILE)dans la requête : dans l’essai, le second appel à 20 000 contacts a alors été compilé pour 20 000 lignes (7 ms de compilation, 133 ms de processeur).STRING_SPLITetOPENJSONconviennent tant que les listes restent courtes, puisque leur plan est construit pour 50 lignes. - Pour un plan forcé ou un indicateur du Query Store sur une requête à liste : seulement après paramétrage, et sur chaque longueur de liste qui compte.