Le plafond d'entrées du cache de plans
Categories:
15 minutes à lire
Article coécrit avec Claude, qui a reproduit le cas en laboratoire et mené une partie des recherches.
Le cache de plans range les plans des requêtes ad hoc et préparées dans un store, SQL Plans, dont la table de hachage compte 40 009 compartiments sur un serveur 64 bits. Au-delà de quatre fois ce nombre, soit 160 036 entrées, le store se met en pression et retire des plans, quelle que soit la mémoire du serveur. Chez un client, il était plein avec 6 Go sur 128 ; au labo, il oscille autour de 160 030 entrées pour 220 Mo, sans aucune pression mémoire de l’instance. Chaque nouvelle requête y chasse alors une entrée existante. Les stubs de optimize for ad hoc workloads comptent comme des entrées ; l’indicateur de trace 174 fait passer le plafond à 640 004 ; la vraie correction est de paramétrer la requête qui remplit le store.
Le cache de plans (plan cache) garde les plans d’exécution compilés pour les réutiliser. Il est fait de plusieurs stores (cache stores), que sys.dm_os_memory_cache_counters montre comme les autres caches de l’instance. Deux stores contiennent des plans :
- SQL Plans (
CACHESTORE_SQLCP), les plans des lots ad hoc (objtype = 'Adhoc') et des requêtes préparées ou paramétrées (Prepared), dont celles qu’envoiesp_executesql; - Object Plans (
CACHESTORE_OBJCP), les plans des procédures stockées, des fonctions et des déclencheurs (Proc,Trigger).
Chaque store retrouve ses entrées par une table de hachage. La page de sys.dm_exec_cached_plans donne sa taille, dans la description de la colonne bucketid : jusqu’à 40 009 compartiments (buckets) sur un système 64 bits pour SQL Plans et Object Plans. La taille d’un store a deux limites, l’une en mémoire, l’autre en nombre d’entrées. C’est la seconde qu’on oublie, parce qu’elle ne se voit pas dans les compteurs de mémoire.
Cet article part d’un cas relevé chez un client, qui fait suite à celui du Query Store saturé par des identifiants concaténés : la même application, la même cause, une autre victime. L’article sur les listes IN de littéraux mesure ce que coûtent ces plans à usage unique en mémoire et en compilations ; celui-ci s’attache au plafond.
Les mesures
Le cas : SQL Server 2019 CU32 avec sa mise à jour de sécurité (15.0.4500.2), édition Standard, 16 cœurs, 128 Go de mémoire. L’application lit une table d’historique de mesures ; 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), limité à 6 Go (MSSQL_MEMORY_LIMIT_MB = 6144, mémoire cible de 5,2 Go), avec une base de test qui contient une table de 288 000 lignes (2 000 identifiants de mesure, 144 relevés chacun) et rien d’autre. optimize for ad hoc workloads y est à 1, comme chez le client.
Le relevé du cache
Le premier relevé compte les plans par type :
SELECT objtype,
COUNT(*) AS plans,
SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS utilises_une_fois,
SUM(CAST(size_in_bytes AS bigint)) / 1048576 AS mo
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY plans DESC;
Chez le client, à 11 h 18 UTC :
objtype | Plans | Utilisés une fois | Mo |
|---|---|---|---|
Prepared | 137 999 | 26 270 | 5 673 |
Adhoc | 22 037 | 21 957 | 15,6 |
View | 182 | ||
Proc | 63 | ||
Trigger | 13 |
Le store SQL Plans pesait 6 151 Mo, le cache des données 92 855 Mo ; la mémoire engagée (106,9 Go) était égale à la mémoire cible, sur 128 Go de mémoire physique. Le journal d’erreurs, courant et précédent, ne contenait aucun message « cachestore flush » ni aucune trace d’un DBCC FREEPROCCACHE. Rien n’indiquait une pression mémoire.
Les deux premières lignes font 137 999 + 22 037 = 160 036 entrées, exactement quatre fois 40 009.
Le plafond d’entrées
La valeur n’est pas dans la documentation actuelle de SQL Server. Le guide d’architecture du traitement des requêtes, section sur le retrait des plans du cache, dit seulement que les threads utilisateurs retirent des plans pour faire respecter « maximum single cache size and maximum single cache entries ». Elle est dans l’article de support KB3026083, celui de l’indicateur de trace 174 : « The maximum number of entries that a plan cache can hold is four times the bucket count », et sur 64 bits, avec 40 009 compartiments, 160 036 entrées. Microsoft SQL Server 2012 Internals (Kalen Delaney et al., 2013, chapitre 12) et Pro SQL Server Internals (Dmitri Korotkevitch, 2014, chapitre 26) décrivent la même règle : au-delà de quatre fois la taille de la table de hachage, le store se met en pression mémoire locale, quelle que soit la taille des plans.
Au labo, une boucle a envoyé 200 000 requêtes ad hoc distinctes, chacune avec un identifiant différent dans son texte, et un relevé toutes les deux secondes a suivi le store. Avec optimize for ad hoc workloads, chaque requête ne laisse qu’un stub de plan de 456 octets :
| Requêtes envoyées (environ) | Entrées SQL Plans | Mo | Entrées retirées |
|---|---|---|---|
| 45 000 | 44 710 | 63 | 0 |
| 114 000 | 114 237 | 157 | 0 |
| 154 000 | 153 979 | 211 | 0 |
| 166 000 | 160 033 | 219 | 6 138 |
| 200 000 | 160 035 | 219 | 39 975 |
Le store s’arrête à 160 033 entrées et n’en sort plus : les relevés suivants oscillent entre 160 025 et 160 041. Le plafond est un seuil : passé quatre fois la taille de la table de hachage, le store se met en pression et retire des entrées, ce qui le ramène au voisinage du seuil. Chaque requête de plus en retire une autre, et le compteur removed_all_rounds_count de l’aiguille interne en suit le compte. Le store ne pesait alors que 219 Mo, sur 5,2 Go de mémoire cible. L’aiguille externe, celle qui répond à la pression mémoire de l’instance, n’a retiré aucune entrée.
Les deux aiguilles d’horloge (clock hands) se lisent dans sys.dm_os_memory_cache_clock_hands : HAND_INTERNAL fait respecter les limites du store lui-même, HAND_EXTERNAL répond à la pression de l’instance.
SELECT c.name, c.entries_count, c.pages_kb / 1024 AS mo,
h.clock_hand, h.rounds_count, h.removed_all_rounds_count
FROM sys.dm_os_memory_cache_counters AS c
JOIN sys.dm_os_memory_cache_clock_hands AS h
ON h.cache_address = c.cache_address
WHERE c.type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP');
Un store dont entries_count reste au voisinage de 160 036 et dont l’aiguille interne retire des entrées, alors que l’aiguille externe ne bouge pas, est plein par le nombre d’entrées.
Mesuré au labo :
- Les stubs de
optimize for ad hoc workloadscomptent comme des entrées. L’option divise par plusieurs dizaines la mémoire d’un plan ad hoc utilisé une fois (16 Ko ou plus pour le plan, quelques centaines d’octets pour le stub), elle ne recule pas le plafond : 160 000 stubs de 456 octets remplissent le store aussi bien que 160 000 plans complets. - Le plafond s’applique à chaque store séparément. Avec SQL Plans plein à 160 031 entrées, 3 000 procédures stockées exécutées ensuite ont ajouté 3 000 entrées dans Object Plans, sans rien retirer de SQL Plans. Je n’ai pas mesuré l’inverse : si un flux continu de requêtes ad hoc retire des plans de procédures déjà en cache. Un lecteur du billet d’Erin Stellato, cité plus bas, rapporte avoir vu le nombre de procédures en cache baisser chaque fois que SQL Plans atteignait 160 036 entrées.
La limite de taille
L’autre limite d’un store est sa taille. Delaney et Korotkevitch la calculent à partir de la limite de pression du cache de plans (plan cache pressure limit), qui dépend de la mémoire cible : 75 % des 4 premiers gigaoctets, 10 % de 4 à 64 Go, 5 % au-delà. Un store se met en pression locale quand il atteint 62,5 % de cette limite (règle donnée pour SQL Server 2012 et 2014). Microsoft donnait la limite de pression dans le livre blanc Plan Caching in SQL Server 2008, aujourd’hui archivé, et Erin Stellato la reprend sur son blog en 2019 ; la règle des 62,5 % ne se trouve que chez Delaney et Korotkevitch, et la documentation actuelle de SQL Server ne donne ni l’une ni l’autre.
Au labo, la formule se vérifie sur 2019. Avec une mémoire cible de 5 185 Mo, la limite de pression vaut 3 072 + 109 = 3 181 Mo, et 62,5 % de cette limite 1 988 Mo. Une boucle de 120 000 requêtes préparées, chacune avec un plan de 48 Ko, a fait plafonner le store à 1 972 Mo et 39 950 entrées, loin du plafond d’entrées.
Chez le client, la même formule donne, pour 106,9 Go de mémoire cible, une limite de pression d’environ 11,1 Go et une limite de store d’environ 7,0 Go. Le store pesait 6 151 Mo, 86 % de cette limite : c’est bien le nombre d’entrées qui l’arrêtait, mais la taille n’était pas loin derrière. Les deux limites s’approchent d’autant plus que les plans sont gros, et celle qui s’atteint d’abord dépend de la taille moyenne d’une entrée : chez le client, 42 Ko pour un plan préparé.
L’éviction
Le guide d’architecture décrit le retrait : chaque plan a un coût courant, qui part du coût de sa compilation (zéro pour un plan ad hoc) et que l’aiguille fait baisser à chaque passage, s’il n’est pas utilisé ; un plan est retiré quand l’aiguille le trouve à zéro. Chaque réutilisation rend au plan son coût de compilation ; celui d’un plan ad hoc augmente seulement, sans dépasser ce coût. Un plan souvent utilisé n’atteint donc pas zéro. Sur SQL Server 2025, la mesure ne suit pas exactement cette description : un plan préparé ou une procédure part aussi de zéro, et le coût double à chaque réutilisation jusqu’à 16, comme je le détaille dans Le cache de plans de SQL Server.
Au labo, des requêtes paramétrées tournaient pendant le remplissage, dans trois essais :
| Remplissage | Entrées retirées | Requêtes suivies, appelées toutes les |
|---|---|---|
| stubs ad hoc, au plafond d’entrées | 39 975 | 20 ms |
| plans préparés utilisés une fois, au plafond de taille | 80 055 | 20 ms, 200 ms, 2 s |
| plans préparés réutilisés au hasard sur 80 000 identifiants | 77 844 | 20 ms, 2 s, 20 s |
Aucune n’a perdu son plan : un seul plan, une seule creation_time du début à la fin. Les plans de remplissage, peu ou pas réutilisés, atteignaient zéro avant elles. Une limite de ces essais : le seul qui atteint le plafond d’entrées remplit le store de stubs ad hoc ; chez le client, ce sont des plans préparés. Sur 2025, le coût d’un plan préparé part aussi de zéro, mais je ne l’ai pas mesuré sur 2019, la version de ces essais. Le labo n’avait pas la mémoire pour tenir 160 036 plans préparés de 48 Ko sous la limite de taille.
Un store plein fait sûrement recompiler les requêtes qui le remplissent. Chaque identifiant nouveau coûte une compilation, plafond ou non : c’est le prix de l’absence de paramètres, et chez le client les quatre formes à identifiant concaténé laissaient 136 885 plans préparés le matin même, avec un seul query_plan_hash par forme. Un store plein y ajoute la recompilation des identifiants rappelés après l’éviction de leur plan. Dans le troisième essai, arrêté par la limite de taille vers 40 000 entrées, 39 000 identifiants ont d’abord été chargés, puis 200 000 appels ont été tirés au hasard parmi 80 000. Le compteur SQL Compilations/sec, qui compte toute l’instance, a pris 127 571 compilations sur l’ensemble de l’essai ; sans éviction, il en aurait compté environ 76 600 (les 39 000 du chargement et quelque 37 600 identifiants nouveaux tirés ensuite). L’écart, environ 50 000 compilations, soit une pour quatre appels, vient de l’éviction. C’est un ordre de grandeur : le même essai compte 77 844 retraits, et ces deux comptes ne se recoupent pas exactement.
Chez le client, pourtant, les plans des requêtes les plus fréquentes de l’application ont été recompilés en vague. Les entrées des six requêtes les plus appelées (de 900 à 71 000 exécutions par entrée, la plus fréquente autour de 150 000 appels par heure) avaient toutes été créées à la même seconde, 11:02:57. Le Query Store montrait une centaine de nouveaux plans pour ces requêtes entre 11:00:03 et 11:02:59, une entrée relevée à 10:56 avait disparu à 10:58, et un des plans comptait 1 017 compilations. Le labo n’a pas reproduit cette vague : des plans utilisés toutes les 20 secondes y ont survécu à près de 78 000 évictions. Le plafond n’est donc pas, à lui seul, une explication démontrée. La date de création elle-même ne tranche pas. creation_time, dans sys.dm_exec_query_stats, est l’heure de compilation du plan, pas celle de son entrée dans le cache. Au labo, une requête recompilée après une mise à jour de statistiques a gardé son plan_handle, et plan_generation_num est passé de 1 à 2, mais creation_time a pris l’heure de la recompilation, comme pour un plan neuf. Six entrées « créées » à la même seconde peuvent donc être six plans recompilés sur place, après le recalcul d’une statistique commune, aussi bien que six plans évincés puis recompilés. Pour départager les deux, relever sur les requêtes fréquentes le plan_handle et plan_generation_num, et suivre les deux aiguilles :
SELECT qs.plan_handle, qs.plan_generation_num, qs.creation_time,
qs.execution_count, LEFT(t.text, 80) AS debut_du_texte
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY qs.execution_count DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
Un plan_handle qui change veut dire que le plan est sorti du cache ; un plan_generation_num qui augmente sur le même plan_handle, qu’il a été recompilé sur place.
Le reniflage de paramètres
Quelle qu’en soit la cause, chaque nouvelle compilation d’une requête chaude rejoue le reniflage de paramètres (parameter sniffing) : le plan est construit pour les valeurs du premier appel qui suit. Chez le client, pour une même forme de requête, le même nombre de lectures logiques (environ 880) coûtait 4 ms ou de 25 à 30 ms de processeur par appel selon le plan du moment. Le plan changeait chaque nuit après la mise à jour des statistiques, et à chaque vague de recompilation en journée.
Le labo montre l’écart sur une requête sensible au paramètre. La table de test a reçu une colonne de site, avec un site qui contient la moitié des lignes et mille sites de 144 lignes, et un index sur cette colonne. Compilée pour un petit site, la requête cherche dans l’index de la colonne puis lit chaque ligne dans l’index cluster (Index Seek, Nested Loops, Clustered Index Seek) ; appelée ensuite pour le gros site avec ce plan, elle a lu 459 361 pages et pris 111 ms de processeur, contre 1 935 lectures avec le plan compilé pour lui. Un plan évincé ou recompilé tire au sort lequel des deux reste en place jusqu’à la compilation suivante.
Les remèdes
La correction est dans l’application : passer l’identifiant en paramètre, comme les dates. L’article sur le Query Store donne la réécriture ; chaque forme n’a plus qu’un plan, et le store retombe à quelques milliers d’entrées.
Si l’application ne peut pas changer tout de suite :
- L’indicateur de trace 174, au démarrage seulement, fait passer la table de hachage de SQL Plans et d’Object Plans de 40 009 à 160 001 compartiments, et le plafond, selon KB3026083, à 640 004 entrées. Au labo, il a aussi fait passer Bound Trees de 4 001 à 16 001 compartiments, ce que la page des indicateurs de trace ne dit pas. La même boucle de stubs, poussée à 700 000 requêtes, a plafonné à 639 999 entrées pour 874 Mo, et retiré 60 152 entrées. Le store peut alors grossir quatre fois plus, et la limite de taille redevient celle qu’on atteint : sur le serveur du client, avec des plans de 42 Ko, environ 170 000 plans préparés suffisent pour 7 Go. La page de dépannage de la mémoire, quand
CACHESTORE_SQLCPprend trop de mémoire, propose d’ailleurs de le désactiver pour voir si le problème disparaît. Je le réserve au cas où le plafond d’entrées est démontré et où la mémoire le permet. - Forcer par le Query Store le plan des requêtes chaudes évite ce tirage : le plan forcé revient à chaque compilation, quelle qu’en soit la cause.
DBCC FREESYSTEMCACHE ('SQL Plans')retire du store toutes les entrées inutilisées, donc les plans de toutes les requêtes ad hoc et préparées de l’instance qui ne s’exécutent pas à cet instant. L’application le remplit de nouveau ; c’est un pansement.
Ce qui ne marche pas :
optimize for ad hoc workloads: les stubs comptent comme des entrées, et l’option ne s’applique pas aux plans préparés desp_executesql.- Le paramétrage forcé (
PARAMETERIZATION FORCED) : il ne touche pas une requête envoyée parsp_executesqlavec une liste de paramètres, mesuré dans l’article sur le Query Store. - Ajouter de la mémoire : le plafond d’entrées ne dépend pas de la mémoire. La limite de taille, elle, monte avec la mémoire cible, mais d’environ 3 % de la mémoire ajoutée au-delà de 64 Go (62,5 % de 5 %).
La surveillance
Le contrôle tient en une requête, à lancer de temps en temps ou dans une sonde :
SELECT name, entries_count, pages_kb / 1024 AS mo
FROM sys.dm_os_memory_cache_counters
WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP');
entries_count au voisinage de 160 036 (ou 640 004 sous l’indicateur 174) veut dire que le store est plein, quelle que soit sa taille en mégaoctets. Le compteur de performance SQL Compilations/sec mesure le coût des requêtes sans paramètres ; un store plein y ajoute les recompilations. KB3026083 décrit un autre symptôme du plafond : une contention sur le spinlock SOS_CACHESTORE (sys.dm_os_spinlock_stats) et une charge processeur élevée, quand beaucoup de sessions insèrent des plans en même temps.
Pour reproduire le plafond, sur une instance de test seulement (la boucle remplit le store SQL Plans de l’instance et en chasse les plans ad hoc et préparés peu utilisés), ce script envoie 200 000 requêtes à identifiant concaténé dans PachaDataFormation. Il met d’abord optimize for ad hoc workloads à 1 : les stubs remplissent alors le store en quelques minutes pour quelques centaines de mégaoctets, alors que des plans complets de 16 Ko buteraient d’abord, sur une petite instance, sur la limite de taille. Noter la valeur d’origine de l’option avant de lancer le script :
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
GO
USE PachaDataFormation;
SET NOCOUNT ON;
DECLARE @i int = 1, @sql nvarchar(400);
WHILE @i <= 200000
BEGIN
-- un texte différent par identifiant : une entrée par appel
SET @sql = N'SELECT MAX(Nom) FROM Contact.Contact WHERE ContactId = '
+ CAST(@i AS nvarchar(10)) + N' AND Nom > '''' OPTION (MAXDOP 1)';
EXEC (@sql);
SET @i += 1;
END;
SELECT name, entries_count, pages_kb / 1024 AS mo
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP';
Remettre l’option à sa valeur d’origine après l’essai, et vider le store (DBCC FREESYSTEMCACHE ('SQL Plans')) si l’instance sert à autre chose.