# Le cache de plans de SQL Server

> Comment SQL Server garde ses plans compilés : _stores_, clé du cache, paramétrage, coût des entrées, éviction, limites, invalidation et vidage, de 2005 à 2025.
>
> Source : https://www.pachadata.com/docs/articles/sqlserver/internals/cache-de-plans/
> Auteur : Rudi Bruchez · publié le 2026-11-12 · mis à jour le 2026-11-15
> Version Markdown de la page, pour les outils et les LLM. La page HTML fait référence.


*Article coécrit avec Claude, qui a mené une grande partie des tests et des recherches.*

> TL;DR :
>
> Le cache de plans (*plan cache*) est la partie de la mémoire où SQL Server conserve les plans d'exécution, pour ne pas recompiler sans cesse les mêmes requêtes. Depuis SQL Server 2005, il est composé de quatre _stores_, dont deux contiennent des plans, `SQL Plans` pour les batchs ad hoc et préparés, `Object Plans` pour les procédures et les déclencheurs. Vous pouvez en voir le détail avec la vue `sys.dm_exec_cached_plans`.
> 
> Un plan ad hoc ou préparé est retrouvé par le texte exact de son batch entier, caractère par caractère ; un plan de procédure, de fonction ou de déclencheur, par l'identifiant de l'objet. Le moteur compare aussi une série d'attributs, la clé du cache, dont les options `SET` de la session et, quand un nom n'est pas qualifié, le schéma par défaut de l'utilisateur.
> 
> Pour qu'un plan soit paramétré et serve à plusieurs valeurs, celle-ci doit sortir du texte de la requête. Le moteur le fait lui-même par le paramétrage simple, actif par défaut mais limité aux requêtes dont le plan ne dépend pas de la valeur, ou par le paramétrage forcé, qu'on active par base et qui paramètre presque tout. Le client le fait par `sp_executesql` avec de vrais paramètres, ou en appelant des procédures stockées. Un identifiant concaténé dans le texte, même passé à `sp_executesql`, produit un plan par valeur.
> 
> `SQL Plans` et `Object Plans` ont chacun deux limites : un seuil de 160 036 entrées, quatre fois les 40 009 buckets de leur table de hachage, et une taille qui dépend de la mémoire cible. Une entrée a un coût, que l'horloge du cache fait baisser sous pression et qui remonte à chaque réutilisation.
> 
> Un plan quitte le cache par éviction ou par vidage : `DBCC FREEPROCCACHE`, `ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE`, et certaines reconfigurations, comme `max degree of parallelism`, qui vident le cache de toute l'instance. Une invalidation déclenche au contraire une recompilation dans la même entrée, avec le même `plan_handle`.


## Le cache de plans

Avant d'exécuter une requête, SQL Server la compile : il l'analyse, résout les noms des objets, puis l'optimiseur cherche un plan d'exécution.
Pour une requête simple, cela prend quelques millisecondes ; pour une requête de vingt tables ou une liste `IN` de 20 000 littéraux, plusieurs secondes.
Le cache de plans (*plan cache* en anglais, *procedure cache* dans les anciennes versions) garde le résultat de ce travail, le plan compilé, pour que l'exécution suivante de la même requête s'en serve directement.

> Histoire :
>
> L'idée remonte au premier prototype de SGBD relationnel. En 1981, l'équipe d'IBM qui avait construit System R et inventé le langage SQL raconte dans *A History and Evaluation of System R* (Donald D. Chamberlin et quinze coauteurs, *Communications of the ACM*, vol. 24, n° 10, octobre 1981, p. 632-646) comment les instructions SQL d'un programme étaient compilées une fois en routines rassemblées dans un module d'accès (*access module*), dont la liste des tables et des index utilisés était enregistrée dans le catalogue. Un `DROP INDEX` marquait les modules concernés comme invalides, et le module était recompilé au prochain appel, sans que l'utilisateur s'en aperçoive. Leur mesure justifie encore le cache : sur un IBM 370/168, pour une jointure de deux tables, l'analyse et le choix du chemin d'accès coûtaient environ 94 ms de processeur, la génération du code 19 ms, la lecture d'une ligne du résultat 8,7 ms.
> 
> Le plan d'exécution réutilisé, les dépendances enregistrées, l'invalidation par un changement de schéma, la recompilation paresseuse (_lazy_) à l'appel suivant : toutes choses que SQL Server et de nombreux SGBDR font encore aujourd'hui. Comme dans System R, le cache garde une structure exécutable en mémoire.


Dans les années 1990, SQL Server 6.5 ne gardait en cache que les plans des procédures stockées.
La version 7.0 a commencé à conserver aussi les plans des requêtes ad hoc et du SQL dynamique, et SQL Server 2005 a donné au cache sa forme actuelle : des _stores_ bâtis sur le cadre commun des caches de SQLOS, des vues de gestion dynamique pour les lire, et la recompilation instruction par instruction au lieu du batch entier, ou de toute la procédure stockée.
Les versions suivantes ont ajouté des réglages et des fonctionnalités supplémentaires :
- `optimize for ad hoc workloads` en 2008,
- `CLEAR PROCEDURE_CACHE` en 2016,
- l'optimisation des plans sensibles aux paramètres (PSP) en 2022,
- l'optimisation des paramètres facultatifs (OPPO)
- l'option de base `OPTIMIZED_SP_EXECUTESQL` en 2025).

Tout cela sans changer la structure fondamentale du cache.

Le cache est évidemment volatile : il disparaît au redémarrage du service, et un plan peut en sortir à tout moment. C'est ce qui le distingue du Query Store, qui conserve les plans et leurs statistiques d'exécution dans la base.

J'ai déjà traité plusieurs parties du sujet dans des articles séparés :

- [Le plafond d'entrées du cache de plans](https://www.pachadata.com/docs/articles/sqlserver/internals/cache-de-plans-plafond-entrees/) pour les limites et l'éviction,
- [La recompilation des plans](https://www.pachadata.com/docs/articles/sqlserver/internals/recompilation/) et [Les statistiques et les plans en cache](https://www.pachadata.com/docs/articles/sqlserver/internals/statistiques-et-plans-en-cache/) pour l'invalidation,
- [Les listes IN de littéraux](https://www.pachadata.com/docs/articles/sqlserver/performances/listes-in-ad-hoc/) pour le paramétrage et les _stubs_.

Je détaille ici ce que contient le cache, comment le moteur retrouve un plan, ce qu'il met en cache, ce qu'il retire et ce qui vide le cache.

Les mesures ont été effectuées essentiellement par Claude, sur des conteneurs jetables de SQL Server 2025 CU10 (17.0.5035.5) sous Linux, avec une mémoire limitée à 2 048 Mo, sauf mention contraire.
Les mesures principales ont été refaites sous Windows Server 2019, sur une instance 2025 CU9 (17.0.5015.4).
Toutes ont donné les mêmes valeurs que sous Linux.

![Les parties du cache de plans et ce qui agit sur elles.](cache-de-plans.puml.svg)

## Les _stores_

Le cache de plans n'est pas une structure unique. C'est un ensemble de _stores_ (*cache stores*) de SQLOS, la couche du moteur qui gère la mémoire et les processeurs. Chaque _store_ a sa table de hachage (_hash table_), son compteur d'entrées, son horloge d'éviction, et un nom de type qui commence par `CACHESTORE_`. Quatre _stores_ forment le cache de plans au sens de la documentation :

| _store_ | Type | Contenu |
|---|---|---|
| SQL Plans | `CACHESTORE_SQLCP` | plans des batchs ad hoc, des requêtes paramétrées automatiquement, de `sp_executesql` et des instructions préparées |
| Object Plans | `CACHESTORE_OBJCP` | plans des procédures stockées, des fonctions et des déclencheurs |
| Bound Trees | `CACHESTORE_PHDR` | arbres algébrisés (*bound trees*) des vues, des contraintes et des valeurs par défaut |
| Extended Stored Procedures | `CACHESTORE_XPROC` | nom et bibliothèque des procédures étendues, comme `sp_executesql` lui-même |

Les objets de compteurs de performance `SQLServer:Plan Cache` comptent une cinquième instance, `Temporary Tables & Table Variables` (`CACHESTORE_TEMPTABLES`), qui garde les tables temporaires mises en cache par les procédures. Elle a ses propres règles, que j'ai décrites dans [La durée de vie des tables temporaires mises en cache](https://www.pachadata.com/docs/articles/sqlserver/administration/duree-de-vie-tables-temporaires/).

Le texte des requêtes se trouve dans le cache _SQL Manager_, un objet mémoire de type `MEMOBJ_SQLMGR`. Il suit une autre règle d'éviction que les _stores_ : une entrée de texte reste tant qu'au moins un plan y fait référence.

La taille des tables de hachage se lit dans `sys.dm_os_memory_cache_hash_tables` :

```sql
SELECT name, type, buckets_count, buckets_in_use_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN (N'CACHESTORE_SQLCP', N'CACHESTORE_OBJCP',
               N'CACHESTORE_PHDR', N'CACHESTORE_XPROC');
```

Les tables de hachage `SQL Plans` et `Object Plans` ont 40 009 buckets, `Bound Trees` 4 001, `Extended Stored Procedures` 127.
Ce sont des nombres premiers, que les livres sur les _internals_ arrondissent à « environ 40 000 ».
Le bucket d'une entrée est calculé comme `(object_id × database_id) mod taille de la table`, d'après Sangeetha Shekar, de l'équipe de programmabilité de SQL Server, en 2007, et Kalen Delaney dans *Microsoft SQL Server 2012 Internals*.
Pour un plan ad hoc ou préparé, l'`object_id` est un hachage du texte du batch. Le produit est calculé sur un entier de 32 bits non signé, qui déborde : je l'ai vérifié sur 36 entrées (voir « Les identifiants d'un plan »).

[L'indicateur de trace (`trace flag`) 174](https://support.microsoft.com/fr-fr/servicing/sql/performance-query/kb3026083-fix-sos-cachestore-spinlock-contention-on-ad-hoc-sql-server-plan-cache-causes-high-cpu-usa) fait passer les deux tables de plans à 160 001 buckets, et j'ai mesuré qu'il fait aussi passer _Bound Trees_ à 16 001, ce qui n'est pas documenté (voir l'article sur le plafond).

> Pourquoi 40 009 buckets ? :
>
> 40 009 est le premier nombre premier supérieur à 40 000. Le nombre premier a un intérêt pour le calcul modulo : si la table avait 40 000 buckets et que `database_id` valait 2, le produit `object_id × database_id` serait toujours pair ; seuls les buckets pairs pourraient être atteints. Avec 40 009, multiplier par 2 ne restreint pas ainsi les résultats du modulo, car 2 et 40 009 sont premiers entre eux.
> 
> Le nombre premier limite ce type de concentration, sans garantir une répartition uniforme : celle-ci dépend aussi des identifiants hachés. L'exemple illustre le principe mathématique ; le calcul réel comporte le débordement sur 32 bits décrit plus haut. Je n'ai pas trouvé d'explication publiée par Microsoft sur le choix précis de 40 000 comme taille cible.


On trouve ce que contient le cache dans la DMV `sys.dm_exec_cached_plans`, une ligne par entrée :

```sql
SELECT p.cacheobjtype, p.objtype, p.usecounts, p.size_in_bytes,
       p.plan_handle, LEFT(t.text, 80) AS debut_du_texte
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
ORDER BY p.size_in_bytes DESC;
```

La colonne `cacheobjtype` donne la nature de l'entrée :
- `Compiled Plan` pour un plan compilé,
- `Compiled Plan Stub` pour un _stub_ (voir plus loin),
- `Parse Tree` pour un arbre de _Bound Trees_,
- `Extended Proc` pour une procédure étendue.

La colonne `objtype` donne l'origine : `Adhoc` (en un mot, et non « Ad hoc » comme l'écrit la page de documentation de `sys.dm_exec_cached_plans` sur Microsoft Learn), `Prepared`, `Proc`, `Trigger`, `View`, `Check`, `Default`, `UsrTab`, `SysTab`, `ReplProc`, `Rule`.

Après six batchs simples, le cache d'une base de test contenait par exemple des entrées `Adhoc` de 16 384 octets, une jointure ad hoc de 57 344 octets, des plans `Prepared` de 49 152 à 57 344 octets, une procédure de 98 304 octets, et deux `Extended Proc` de 8 192 octets, `sp_executesql` et `xp_msver`. La procédure `sp_executesql` est elle-même une procédure étendue mise en cache dans XPROC. Les entrées `Adhoc` de 16 384 octets ne contiennent pas de plan d'exécution : ce sont des *shell queries*, des entrées qui gardent le texte d'origine d'une requête paramétrée par le moteur et renvoient vers le plan paramétré (voir « Le paramétrage simple »).

> Dans quel bucket se trouve un plan ? :
>
> La colonne `bucketid` de [`sys.dm_exec_cached_plans`](https://learn.microsoft.com/fr-fr/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-cached-plans-transact-sql) donne le numéro du bucket de chaque entrée en cache :
> 
> ```sql
> SELECT p.plan_handle, p.bucketid, p.memory_object_address,
>        p.objtype, p.cacheobjtype, LEFT(t.text, 80) AS debut_du_texte
> FROM sys.dm_exec_cached_plans AS p
> OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
> ORDER BY p.bucketid, p.objtype;
> ```
> 
> Pour retrouver un plan précis, on peut filtrer sur son `plan_handle`. Le numéro du bucket est relatif à la table de hachage du _store_ : deux entrées de _stores_ différents peuvent avoir le même `bucketid`. La colonne `memory_object_address` désigne l'objet mémoire de l'entrée, pas l'adresse du bucket. La DMV `sys.dm_os_memory_cache_hash_tables` donne les statistiques des tables de hachage, mais pas l'adresse de chaque bucket.


### La taille d'une entrée

Longtemps j'ai pensé que les plans en cache sont gros parce qu'ils sont stockés en XML. Le plan en cache est en réalité une structure binaire : un arbre d'objets du moteur reliés par des pointeurs, rangé dans des objets mémoire de SQLOS. Le XML que montrent SSMS et `sys.dm_exec_query_plan` est fabriqué à la demande à partir de cette structure, puis jeté. Sur quinze plans de complexité croissante, j'ai demandé à Claude de comparer `size_in_bytes` à la taille du XML que rend `sys.dm_exec_query_plan` :

| Plan | `size_in_bytes` | XML en `nvarchar` | XML / taille |
|---|---|---|---|
| _shell query_ du paramétrage simple | 16 384 | 1 068 | 7 % |
| `COUNT(*)` d'une table | 49 152 | 6 360 | 13 % |
| jointure de deux tables | 81 920 | 15 670 | 19 % |
| jointure de trois tables | 139 264 | 18 744 | 13 % |
| jointure de neuf tables | 360 448 | 47 836 | 13 % |
| procédure de trois instructions | 204 800 | 15 528 | 8 % |
| `sp_executesql` | 49 152 | 5 822 | 12 % |

Le XML pèse de 2 à 30 % de l'entrée. Le lire ne change ni `size_in_bytes` ni `usecounts` : il n'est pas conservé.

Le débogueur le confirme : sur une exécution d'un plan en cache, la fonction qui fabrique le XML (`CSQLSource::FBuildXMLShowplan`, dans `sqllang.dll`) n'a pas été appelée ; une lecture de `sys.dm_exec_query_plan` l'a appelée une fois.
Les deux premières exécutions l'avaient appelée aussi, mais par `GenerateShowplanForQDS` : le Query Store, actif sur la base, demandait le plan pour le stocker, à la compilation et, vraisemblablement pour la même raison, à l'exécution suivante. Pour mesurer la taille de l'entrée, je l'ai rattachée aux objets mémoire qui la composent. `memory_object_address` désigne un objet `MEMOBJ_COMPILE_ADHOC` (pour tous les types, `Proc` compris), et chaque plan a son propre allocateur de pages : les objets de `sys.dm_os_memory_objects` qui partagent cet allocateur (`page_allocator_address`) font exactement `size_in_bytes`, à l'octet près, sur les dix-neuf entrées vérifiées.

```sql
WITH p AS (
    SELECT cp.plan_handle, cp.objtype, cp.size_in_bytes, mo.page_allocator_address
    FROM sys.dm_exec_cached_plans AS cp
    JOIN sys.dm_os_memory_objects AS mo
      ON mo.memory_object_address = cp.memory_object_address
    WHERE cp.cacheobjtype = N'Compiled Plan'
)
SELECT p.objtype, p.size_in_bytes, m.type, COUNT(*) AS nb_objets,
       SUM(m.pages_in_bytes) AS octets
FROM p
JOIN sys.dm_os_memory_objects AS m
  ON m.page_allocator_address = p.page_allocator_address
GROUP BY p.plan_handle, p.objtype, p.size_in_bytes, m.type
ORDER BY p.size_in_bytes DESC, octets DESC;
```

Une entrée contient ainsi plusieurs objets, dont les noms ne sont pas documentés ; leur rôle se déduit de leur comportement :

| Objet mémoire | Ce qu'il contient | Jointure de 2 tables | Jointure de 9 tables |
|---|---|---|---|
| `MEMOBJ_COMPILE_ADHOC` | le plan compilé | 32 Ko | 104 Ko |
| `MEMOBJ_STATEMENT` | une instruction compilée d'un module (procédure) | | |
| `MEMOBJ_XSTMT` | une instruction exécutable d'un batch de plusieurs instructions | | |
| `MEMOBJ_EXECUTE` | le contexte d'exécution (voir la section suivante) | 24 Ko | 104 Ko |
| `MEMOBJ_QUERYEXECCNTXTFORSE` | la partie du contexte propre au moteur de stockage | 24 Ko | 144 Ko |

`size_in_bytes` compte le plan et ses contextes d'exécution gardés en cache : pour la jointure de neuf tables, 104 Ko de plan et 248 Ko de contexte. Chaque objet alloue par pages de 8 Ko, d'où des tailles toujours multiples de 8 192. Seuls les _stubs_ de `optimize for ad hoc workloads` (456 et 136 octets) échappent à cette règle : ils n'ont pas d'objet mémoire à eux et vivent dans un objet `MEMOBJ_CACHESTORESQLCP` de 48 Ko qui appartient au _store_ lui-même et que plusieurs _stubs_ partagent.


> Les 16 Ko d'une *shell query* sont une réservation par pages, mais quelle part est consommée à l'intérieur ? Sur un conteneur jetable SQL Server 2025 CU10 (`17.0.5035.5`, Linux), j'ai examiné les deux enveloppes des requêtes `SELECT value FROM dbo.ShellProbe WHERE id=42` et `id=43`, qui partagent un plan `Prepared`. Chacune alloue une page pour `MEMOBJ_COMPILE_ADHOC` et une pour `MEMOBJ_EXECUTE`. La [DMV `sys.dm_os_memory_objects`](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-memory-objects-transact-sql) indique 8 192 octets par objet, mais son champ interne `bytes_used` vaut `NULL` dans cet essai. J'ai donc lu les registres de tailles et les pointeurs de l'allocateur en mémoire, en m'appuyant sur les symboles publics Microsoft.
> 
> Voici la répartition après les premières exécutions, en octets, identique pour les deux enveloppes :
> 
> | Objet | Allocations demandées | En-têtes et alignement | Espace encore disponible | Total |
> |---|---:|---:|---:|---:|
> | Compilation | 6 125 | 339 | 1 728 | 8 192 |
> | Exécution | 5 544 | 184 | 2 464 | 8 192 |
> | Ensemble | 11 669 | 523 | 4 192 | 16 384 |
> 
> L'allocateur consomme donc 74,4 % des deux pages ; 25,6 % restent disponibles dans ces pages. Les tailles demandées ne mesurent toutefois pas les seuls octets utiles : une structure peut contenir des champs inutilisés ou réserver de la capacité. Le plan préparé partagé, le cache des textes et les structures globales des stores ne sont pas compris dans ces 16 Ko. Ces chiffres concernent cette instruction et ce build, sans pression mémoire.
> 
> <!-- Méthode, symboles et relevés : recherche-9-labo-memoire-shell.md. -->


## Le plan compilé et le contexte d'exécution

Une entrée de `SQL Plans` ou d'`Object Plans` contient un plan compilé et ses contextes d'exécution. Cette distinction explique la mémoire du cache, ses compteurs et ce qui se passe quand cent sessions exécutent la même procédure.

### Le programme et ses exécutions

Le plan compilé (*compiled plan*) est l'équivalent d'un programme : la liste des opérateurs de chaque instruction du batch ou du module, la manière de les enchaîner, la liste des paramètres et la table des symboles (les variables déclarées, leur type). Il ne change pas d'une exécution à l'autre. Il ne contient ni les valeurs des paramètres de l'exécution en cours, ni l'état d'une exécution : seulement les valeurs qui ont servi à le compiler, visibles dans le plan XML (attribut `ParameterCompiledValue`). Il ne fixe pas non plus le degré de parallélisme, choisi à chaque exécution : un plan parallèle en cache peut s'exécuter en série.

Pour exécuter ce programme, il faut un espace de travail : les valeurs reçues pour les paramètres, celles des variables locales, l'instruction en cours, et, pour chaque opérateur du plan, son état du moment (la position d'un parcours d'index, les lignes déjà lues, la table de hachage en construction). C'est le contexte d'exécution (*execution context*, *MXC* dans les sources internes). La [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide#execution-context) le définit comme la structure de données « qui contient les données spécifiques à une exécution, comme les valeurs des paramètres ».

> code réentrant :
>
> SQL Server reprend ici une séparation classique, celle du code réentrant et de l'enregistrement d'activation (*activation record*). Quand Edsger Dijkstra a décrit en 1960 la mise en œuvre des procédures récursives d'Algol (*Recursive Programming*, *Numerische Mathematik*, vol. 2), il a séparé le code de la procédure, unique et jamais modifié, de l'enregistrement que reçoit chaque appel sur la pile, avec ses paramètres et ses variables locales. Un code qui ne modifie jamais ses propres instructions et range tout son état dans l'enregistrement de l'appel est dit réentrant : un nombre quelconque d'appels peuvent l'exécuter en même temps. Le plan compilé est réentrant au même sens : un nombre quelconque de sessions l'utilisent en même temps. Le contexte d'exécution est l'enregistrement d'activation d'une exécution : il ne sert qu'à une session à la fois.
> 
> Une différence avec la pile d'un programme : un enregistrement d'activation est détruit au retour de l'appel, alors qu'un contexte d'exécution est gardé. À la fin d'une exécution, le moteur ne libère pas le contexte, il le remet dans une liste de contextes libres attachée au plan compilé. L'exécution suivante prend un contexte dans cette liste au lieu d'en construire un. C'est une réserve d'objets (*object pool*), le procédé classique quand un objet coûte cher à construire et peu à garder : un contexte se reconstruit en quelques dizaines de kilo-octets d'allocations, mais autant les éviter à chaque appel.


Les contextes n'apparaissent pas dans `sys.dm_exec_cached_plans`, qui ne montre que l'entrée. On les voit dans `sys.dm_exec_cached_plan_dependent_objects`, une ligne de type `Executable Plan` par contexte, et on les compte par trois attributs de `sys.dm_exec_plan_attributes` :

- `misses_exec_context` : nombre de fois qu'une exécution n'a trouvé aucun contexte libre et a dû en construire un ;
- `hits_exec_context` : nombre de fois qu'une exécution a repris un contexte libre ;
- `free_exec_context` : nombre de contextes libres au moment de la lecture.

```sql
SELECT a.attribute, a.value
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
WHERE t.objectid = OBJECT_ID(N'PachaDataFormation.Contact.GetContactParNom')
  AND a.attribute LIKE N'%exec_context%';

SELECT d.cacheobjtype, d.usecounts, d.memory_object_address
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_cached_plan_dependent_objects(p.plan_handle) AS d
WHERE t.objectid = OBJECT_ID(N'PachaDataFormation.Contact.GetContactParNom');
```

### Un exemple pas à pas

Pour voir les contextes naître et resservir, j'ai écrit une procédure qui attend six secondes puis fait une jointure de deux tables, et je l'ai lancée dans quatre sessions en même temps, puis, une fois les quatre terminées, dans deux.

```sql
CREATE OR ALTER PROCEDURE dbo.pB1 AS
BEGIN
    WAITFOR DELAY '00:00:06';
    SELECT COUNT(*) AS n FROM dbo.J1 AS j JOIN dbo.J2 AS k ON k.id = j.fk WHERE j.v = 3;
END;
```

| Moment | `free` | `misses` | `hits` | Contextes (`Executable Plan`) | Ce qui s'est passé |
|---|---|---|---|---|---|
| avant | | | | 0 | la procédure n'est pas en cache |
| pendant la vague de quatre sessions | 0 | 4 | 0 | 4 | aucun contexte libre : chaque session en construit un |
| après la vague | 4 | 4 | 0 | 4 | les quatre contextes sont remis dans la liste des libres |
| pendant la vague de deux sessions | 2 | 4 | 2 | 4 | chaque session prend un contexte libre, aucun n'est construit |
| après | 4 | 4 | 2 | 4 | les quatre contextes sont toujours là |

Les adresses mémoire des quatre contextes sont restées les mêmes du début à la fin : la vague de deux a repris deux des quatre contextes existants. Le nombre de contextes gardés suit donc le pic de concurrence, quatre ici, et ne redescend pas quand la concurrence baisse. La mémoire non plus : pour cette jointure de deux tables, un contexte occupe 48 Ko (8 Ko de `MEMOBJ_EXECUTE` construits à l'entrée dans la procédure, puis 16 Ko de `MEMOBJ_XSTMT` et 24 Ko de `MEMOBJ_QUERYEXECCNTXTFORSE` quand le `SELECT` s'exécute), contre 32 Ko pour le plan compilé. Avec quatre contextes, l'entrée passe à 224 Ko, dont 192 Ko de contextes. Une procédure appelée par cent sessions à la fois garde cent contextes.

Un détail sur `usecounts`, qui compte les recherches réussies et non les exécutions. Si la procédure n'est pas en cache au moment de la vague, une seule session la compile ; les trois autres attendent la fin de cette compilation (voir « La recherche d'un plan ») et reçoivent le plan sans passer par une recherche réussie. Dans l'exemple ci-dessus, `usecounts` valait 1 après la vague de quatre, et 3 après celle de deux. Sur une procédure exécutée une fois avant la vague, il passe de 1 à 5.

### La destruction d'un contexte

Le livre blanc [*Plan Caching and Recompilation in SQL Server 2012*](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/dn148262%28v%3Dmsdn.10%29) de Greg Low dit qu'une erreur de gravité 11 ou plus détruit le contexte au lieu de le remettre dans la liste des libres. La mesure dit autre chose. J'ai exécuté trois fois de suite des procédures qui font une jointure, puis une instruction en erreur, puis un `SELECT` :

| Instruction | Erreur | `free` | `misses` | `hits` | Contexte |
|---|---|---|---|---|---|
| `SELECT 2` | aucune | 1 | 1 | 2 | réutilisé |
| `RAISERROR('x', 10, 1)` | message de gravité 10 | 1 | 1 | 2 | réutilisé |
| `RAISERROR('x', 16, 1)` | gravité 16 | 1 | 1 | 2 | réutilisé |
| `SELECT 1 / @z` avec `@z = 0` | 8134, gravité 16 | 0 | 3 | 0 | détruit à chaque fois |
| `THROW 50001, 'x', 1` | gravité 16 | 0 | 3 | 0 | détruit |
| doublon de clé primaire | 2627, gravité 14 | 0 | 3 | 0 | détruit |
| `SELECT 1 / @z` dans un `TRY`, capturée par `CATCH` | aucune côté client | 0 | 3 | 0 | détruit |
| `RAISERROR('x', 16, 1)` dans un `TRY` | aucune côté client | 0 | 3 | 0 | détruit |

La gravité ne suffit donc pas à prédire la destruction du contexte. Un `RAISERROR` hors d'un bloc `TRY`, même de gravité 16, n'interrompt pas l'exécution : c'est un message envoyé au client, et l'exécution continue. Une exception interrompt l'exécution en cours : erreur levée par le moteur, `THROW`, ou toute erreur qui fait passer dans un `CATCH`, même capturée, même de gravité 14. Le contexte interrompu n'est pas remis dans la liste, sans doute parce que l'état de ses opérateurs n'est plus garanti cohérent ; c'est une inférence, la cause n'est pas publiée. La conséquence pratique est mesurable : une procédure qui passe par son `CATCH` à chaque appel, parce qu'elle utilise une erreur attendue comme flux normal, reconstruit son contexte à chaque exécution.

> Les contextes d'un plan parallèle :
>
> Greg Low écrit aussi que les contextes des sections parallèles d'un plan ne sont pas mis en cache, alors que ceux des sections série le sont. Un plan parallèle est découpé en sections, délimitées par les opérateurs d'échange (*Parallelism*) : la section qui produit le résultat final s'exécute sur le thread de la session, les autres sur autant de threads supplémentaires que le degré de parallélisme. Chaque thread a besoin de son propre état d'opérateurs.
> 
> J'ai observé une procédure parallèle de degré 4, qui occupait 12 threads de travail, avant, pendant et après son exécution. Chaque session a un seul contexte (`Executable Plan`), quel que soit le nombre de threads. Mais l'objet `MEMOBJ_EXECUTE` de ce contexte passe de 16 Ko à 72 Ko pendant l'exécution, puis revient à 16 Ko à la fin : ces 56 Ko, construits à chaque exécution et rendus à la fin, sont selon toute vraisemblance l'état des sections parallèles. Seule la partie série reste en cache, ce qui va dans le sens de la phrase de Low. Avec `OPTION (MAXDOP 1)`, le contexte a dès la première exécution sa taille définitive. Un plan parallèle coûte donc à chaque exécution la construction de ses sections parallèles, même quand son contexte est réutilisé.


### Sous pression mémoire

D'après Sangeetha Shekar, de l'équipe de programmabilité de SQL Server, dans le billet [*9.0 Memory Pressure Limits*](https://learn.microsoft.com/en-us/archive/blogs/sqlprogrammability/9-0-memory-pressure-limits) du 16 janvier 2007, le moteur peut aussi, sous pression mémoire, récupérer jusqu'à la moitié de la mémoire des contextes d'un plan, même en cours d'utilisation : un contexte se reconstruit à peu de frais, un plan compilé non. Sur 2025, la mesure montre autre chose. J'ai suivi une entrée qui avait quatre contextes libres, sous la pression décrite plus loin : à la première vague de pression, les quatre contextes libres ont tous été libérés, et le plan compilé est resté. Un contexte en cours d'utilisation a gardé son objet `MEMOBJ_EXECUTE` et perdu son objet `MEMOBJ_QUERYEXECCNTXTFORSE`. Sous pression, le moteur rend donc d'abord la mémoire la moins chère à reconstruire.

## La recherche d'un plan

SQL Server retrouve un plan de deux façons, selon l'origine du batch. Dans les deux cas, il calcule d'abord un numéro de bucket dans la table de hachage du _store_, puis parcourt la chaîne de ce bucket à la recherche d'une entrée dont la clé correspond : l'objet, la base, et une série d'attributs de la session, comme les options `SET`, qui forment la clé du cache, décrite plus loin.

### Les modules, recherche par identifiant

Pour une procédure, une fonction ou un déclencheur, l'objet est connu par son `object_id`. La recherche se fait dans Object Plans avec cet identifiant, la base, et les attributs de la session. Si aucun plan ne convient, le moteur compile, et il le fait sous un verrou exclusif sur la ressource `COMPILE` de l'objet. Ce verrou garantit qu'une procédure n'est compilée qu'une fois, même si cent sessions l'appellent au même moment alors qu'elle n'est pas en cache.

Sur 2025 CU10, huit sessions synchronisées ont appelé une procédure qui n'était pas en cache, et dont la compilation prenait environ une seconde (200 jointures). Une seule session a compilé et inséré le plan. Les sept autres ont attendu 988 ms chacune sur `LCK_M_X`, que `sys.dm_os_waiting_tasks` montrait ainsi :

```text
session_id wait_type wait_duration_ms resource_description
54         LCK_M_X   302              objectlock objid=2101582525 subresource=COMPILE dbid=6 mode=X
57         LCK_M_X   300              objectlock objid=2101582525 subresource=COMPILE dbid=6 mode=X
...
```

L'`objid` de la ressource est l'`object_id` de la procédure. Une fois le verrou libéré, les sept sessions ont exécuté le plan de la première, sans le recompiler.

> cache stampede :
>
> Le problème que ce verrou résout a un nom en génie logiciel : la ruée sur le cache (*cache stampede*, ou *thundering herd*). Quand une entrée coûteuse manque et que beaucoup de demandeurs arrivent en même temps, chacun constate l'absence et la recalcule, et le système fait n fois le même travail au pire moment. La parade classique est la coalescence des requêtes (*request coalescing*, le paquet `singleflight` de Go, `golang.org/x/sync`) : le premier demandeur calcule, les autres attendent son résultat. Le verrou `COMPILE` est exactement cela.


### Les batchs, recherche par le texte

Pour un batch ad hoc ou préparé, il n'y a pas d'identifiant d'objet. Le moteur en fabrique un à partir du texte : un hachage de 32 bits du batch entier, qui sert d'`objectid` dans la clé et dans le `plan_handle` (voir « Les identifiants d'un plan »). Il cherche le texte dans le cache `SQL Manager`, puis, dans `SQL Plans`, une entrée de même `objectid` dont le texte et la clé correspondent. La correspondance est exacte, caractère par caractère : une espace, une majuscule ou un commentaire de plus font un autre texte, donc une autre entrée.

Sur ce chemin, il n'y a pas de verrou de compilation. Des sessions qui envoient le même batch au même moment, alors qu'il n'est pas en cache, le compilent chacune, et le cache garde chacune des compilations. Greg Low écrivait en 2012 qu'une seule des entrées reste en cache. Sur 2025 CU10, huit sessions synchronisées sur le même batch ad hoc ont laissé huit entrées du même texte, huit `plan_handle` différents, chacune avec `usecounts` à 1. Sept ne serviront plus jamais : l'exécution suivante retrouve l'une d'elles, et les autres attendent l'éviction. Huit sessions sur le même `sp_executesql` ont laissé de même huit entrées `Prepared`. C'est la ruée sur le cache, sans parade.

> Un même objectid, plusieurs entrées en cache :
>
> Le même texte produit bien le même `objectid`. Cet identifiant sert à retrouver les entrées candidates dans le cache ; il n'impose pas qu'une seule entrée puisse exister pour ce texte. La [documentation de `sys.dm_exec_plan_attributes`](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-plan-attributes-transact-sql) le décrit comme une composante de la clé de recherche. D'autres attributs, comme la base et les options `SET`, complètent cette clé.
> 
> Même si le texte, la base et toutes les options sont identiques, deux sessions peuvent suivre cet enchaînement :
> 
> 1. La session A cherche le plan dans le cache. Il est absent : elle commence à compiler.
> 2. Avant qu'A ait terminé et inséré son plan, la session B cherche le même plan. Elle constate elle aussi son absence et commence sa propre compilation.
> 3. Chaque session termine sa compilation et insère sa propre entrée dans le cache.
> 
> Les deux entrées ont alors le même `objectid` et la même clé de recherche, mais correspondent à deux compilations et à deux allocations distinctes, chacune avec son propre `plan_handle`. L'`objectid` ne joue donc pas le rôle d'une contrainte d'unicité. Sans sérialisation des compilations, l'enchaînement « chercher, compiler, insérer » peut laisser plusieurs copies pour la même clé. La [documentation de `sp_executesql`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql#optimized_sp_executesql) décrit explicitement ces copies lorsque `OPTIMIZED_SP_EXECUTESQL` est désactivé. C'est ce que montrent les huit compilations simultanées de l'essai ci-dessus.


SQL Server 2025 a ajouté la parade pour `sp_executesql`, et pour lui seul : l'option de base `OPTIMIZED_SP_EXECUTESQL`, désactivée par défaut.

```sql
USE PachaDataFormation;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_SP_EXECUTESQL = ON;
```

Avec l'option, les huit mêmes appels de `sp_executesql` ont laissé une seule entrée `Prepared`, avec `usecounts` à 8, et sept attentes `LCK_M_X` sur une ressource `COMPILE`. L'`objid` de cette ressource ne correspond à aucun objet de la base : c'est un identifiant tiré du texte, comme l'`objectid` des entrées `Prepared`. Un batch ad hoc n'en profite pas : huit entrées avec l'option comme sans elle. Je n'ai pas mesuré `EXECUTE (@sql)`, mais sa chaîne est recherchée comme un batch ad hoc (voir plus bas).

### Les instructions d'un batch

Un batch de plusieurs instructions a un seul texte, donc une seule entrée et un seul plan compilé, qui contient le plan de chaque instruction. Le moteur relève ensuite les statistiques d'exécution instruction par instruction, dans `sys.dm_exec_query_stats`, où chaque ligne est repérée par les positions de début et de fin de son instruction dans le texte du batch : `statement_start_offset` et `statement_end_offset`. Ces positions sont en octets, comptées à partir de 0, dans un texte `nvarchar` où chaque caractère occupe deux octets, accents compris. Pour retrouver la position d'un caractère, qui pour `SUBSTRING` compte à partir de 1, il faut donc diviser par deux et ajouter un.

Sur ce batch de trois instructions, envoyé tel quel :

```sql
-- Requête : élève, numéro d'été /*D1*/
SELECT COUNT(*) AS [élèves] FROM dbo.J1 j JOIN dbo.J2 k ON k.id = j.fk WHERE j.x <> 'é';
/* commentaire où l'on parle de café */ SELECT MAX(k.v) AS maxi FROM dbo.J2 k JOIN dbo.J3 m ON m.id = k.fk WHERE k.x <> 'à';
SELECT TOP (1) j.x FROM dbo.J1 j JOIN dbo.J3 m ON m.id = j.fk ORDER BY j.v DESC
```

`sys.dm_exec_query_stats` rend trois lignes, de positions 80 à 252, 338 à 502 et 508 à 664. Le premier `SELECT` commence au 41ᵉ caractère du texte : 80 / 2 + 1 = 41. `statement_end_offset` désigne le premier octet du dernier caractère de l'instruction : la première fait (252 - 80) / 2 + 1 = 87 caractères, le `SELECT` sans son point-virgule. Ni le commentaire de tête, ni le commentaire placé devant la deuxième instruction, ni les points-virgules n'appartiennent à une instruction. La requête qui extrait chaque instruction :

```sql
SELECT qs.statement_start_offset, qs.statement_end_offset, qs.execution_count,
       SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,
                 (CASE qs.statement_end_offset
                      WHEN -1 THEN DATALENGTH(t.text)
                      ELSE qs.statement_end_offset
                  END - qs.statement_start_offset) / 2 + 1) AS instruction
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY qs.sql_handle, qs.statement_start_offset;
```

La [documentation de `sys.dm_exec_query_stats`](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql#table-returned) précise que, pour les versions antérieures à SQL Server 2014, `statement_end_offset` vaut -1 pour indiquer la fin du batch, d'où le `CASE`. La mesure sur 2025 CU10 est cohérente avec cette restriction : aucune des 192 lignes de `sys.dm_exec_query_stats` de l'instance de test ne valait -1 ; la dernière instruction avait sa position réelle. La valeur -1 apparaît dans `sys.dm_exec_requests`, pour la dernière instruction en cours d'un batch quand ce n'est pas une requête (un `WAITFOR`, par exemple). Le `CASE` reste donc utile pour `sys.dm_exec_requests`, et ne coûte rien ailleurs. Pour un module, les positions sont celles de l'instruction dans le texte du `CREATE PROCEDURE`.

### Une recherche par niveau d'appel

La recherche se fait au moment où le code s'exécute, pour chaque niveau d'appel séparément. Prenons ce batch ad hoc, qui appelle une procédure, exécute une chaîne par `EXEC` et contient une requête que le moteur paramètre lui-même :

```sql
/*E-lot*/ EXEC dbo.pE 3;
EXEC (N'SELECT MAX(k.v) AS m FROM dbo.J2 k JOIN dbo.J3 m ON m.id = k.fk /*E-dyn*/');
SELECT v FROM dbo.T WHERE id = 2;
```

Une session d'événements étendus sur `sp_cache_hit`, `sp_cache_insert` et le début de chaque instruction donne, à la deuxième exécution du batch, cet ordre :

| Ordre | Événement | Objet |
|---|---|---|
| 1 | `sp_cache_hit` | le batch entier, `ADHOC` |
| 2 | début du batch | |
| 3 | début de `EXEC dbo.pE 3` | |
| 4 | `sp_cache_hit` | la procédure `pE`, `PROC`, par son `object_id` |
| 5 | début de l'instruction de la procédure | |
| 6 | début de `EXEC (N'...')` | |
| 7 | `sp_cache_hit` | la chaîne dynamique, `ADHOC`, par son propre texte |
| 8 | début de l'instruction dynamique | |
| 9 | début du `SELECT v FROM dbo.T` | aucune recherche |

Le batch est recherché une fois, avant son exécution. La procédure et la chaîne dynamique le sont chacune quand leur instruction s'exécute, comme si elles avaient été envoyées seules : une procédure appelée dix fois dans une boucle est recherchée dix fois. La requête paramétrée, elle, n'a été recherchée qu'une fois, à la compilation du batch : le plan du batch garde un lien direct vers son plan préparé, qui a gardé `usecounts` à 1 alors que l'instruction comptait deux exécutions. Le cache laisse ainsi quatre entrées : le batch, la procédure, la chaîne dynamique et le plan préparé.

> sp_cache_miss :
>
> Une remarque sur l'instrument : `sp_cache_miss` ne s'est jamais déclenché avant l'insertion d'un plan qu'on pouvait mettre en cache, seulement pour les batchs non mis en cache et pour les sessions qui attendaient un verrou de compilation. On ne mesure donc pas le taux d'échec du cache en comptant cet événement.


## Les identifiants d'un plan

Quatre identifiants servent à relier les vues du cache entre elles.

| Identifiant | Calculé sur | Sert à |
|---|---|---|
| `sql_handle` | le texte du batch entier, ou l'identité du module | retrouver le texte, par `sys.dm_exec_sql_text` |
| `plan_handle` | l'entrée de cache du batch | retrouver le plan, par `sys.dm_exec_query_plan`, et le retirer par `DBCC FREEPROCCACHE` |
| `query_hash` | l'arbre logique d'une instruction | regrouper les instructions qui ne diffèrent que par leurs littéraux |
| `query_plan_hash` | l'arbre physique du plan d'une instruction | regrouper les instructions qui ont le même plan |

### Le `sql_handle` et le `plan_handle`

Un `sql_handle` peut correspondre à plusieurs `plan_handle` : le même texte compilé sous deux jeux d'options `SET`, ou dans deux bases, donne deux entrées. Le `plan_handle` ne change pas quand une instruction du batch est recompilée. Il est unique parmi les plans présents, mais peut resservir plus tard pour un autre plan.

Aucun des deux formats n'est documenté. Mais on peut les décoder.
Les colonnes sont déclarées [`varbinary(64)`](https://learn.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql), un type de longueur variable pouvant contenir jusqu’à 64 octets. Les deux handles mesurés sur 2025 font 44 octets, sous Linux comme sous Windows. Le schéma ci-dessous en donne trois exemples relevés sur 2025 CU10 : le `plan_handle` et le `sql_handle` d'une jointure ad hoc exécutée dans la base 6, et le `sql_handle` d'une procédure. Les nombres y sont écrits octet de poids faible en tête (*little-endian*), comme en mémoire sur x64 : `0600` est la base 6, `DB9B4319` se lit `0x19439BDB`.

![Les 44 octets d'un plan_handle et d'un sql_handle, ad hoc et procédure.](handles.d2.svg)

Ce que l'on y lit :

- l'octet 0 du `plan_handle` désigne le _store_ : `06` pour SQL Plans (`Adhoc`, `Prepared`), `05` pour Object Plans (procédures, fonctions, déclencheurs) ;
- les octets 2 et 3 donnent la base, les octets 4 à 7 l'attribut `objectid` de la clé : l'`object_id` d'un module, ou, pour un batch, un hachage de 32 bits de son texte. Ce hachage n'est ni `CHECKSUM`, ni `BINARY_CHECKSUM`, ni le début du MD5 du texte : sa fonction n'est pas identifiée ;
- les octets 8 à 15 sont une adresse mémoire, voisine de l'entrée de `sys.dm_os_memory_cache_entries` (le plus souvent 0x830 octets avant elle), sans être ni `memory_object_address` ni l'adresse de l'entrée. C'est elle qui distingue deux entrées du même texte sous deux jeux d'options : ces deux `plan_handle` ne diffèrent que par ces huit octets. C'est aussi elle qui explique qu'un `plan_handle` puisse resservir. Dans un essai, l'ancien plan d'une procédure numérotée `dbo.pG;1`, invalidé, a laissé son `plan_handle` exact au nouveau plan de `dbo.pG;2`, de même `objectid`, alloué à la même adresse ;
- le `sql_handle` d'un batch contient, après `02000000`, le même `objectid`, puis exactement le résultat de `HASHBYTES('MD5', texte)` sur le texte en `nvarchar`, tel que le rend `sys.dm_exec_sql_text`. Il ne dépend que du texte : ni de la base, ni des options `SET` ;
- le `sql_handle` d'un module commence par `03`, puis contient la base, l'`object_id`, la date de dernière modification du module (`modify_date` de `sys.objects`, au format binaire de `datetime`) et le numéro de la procédure dans son groupe (`;1`). Un `ALTER PROCEDURE` change donc le `sql_handle`, ce qui sépare les statistiques des deux versions.

Le MD5 se vérifie sur n'importe quelle instance :

```sql
SELECT TOP (10)
       CONVERT(varchar(100), qs.sql_handle, 1) AS sql_handle,
       CONVERT(varchar(40), HASHBYTES('MD5', t.text), 1) AS md5_du_texte
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qs.sql_handle >= 0x02 AND qs.sql_handle < 0x03;
```

Les octets 8 à 23 de la première colonne (en comptant à partir de 0, comme dans le schéma) sont égaux à la seconde.

L'`objectid` et la base servent aussi à placer l'entrée dans la table de hachage. Sur 36 entrées sur 36, dont des procédures et un déclencheur, `bucketid` valait ((`objectid` × `dbid`) mod 2³²) mod 40 009 : le produit est calculé sur un entier de 32 bits non signé, qui déborde, puis réduit au nombre de buckets. Pour la jointure du schéma, (423 861 211 × 6) mod 2³² mod 40 009 = 35 190. Le même texte dans deux bases tombe donc dans deux buckets, alors que sous deux jeux d'options il tombe dans le même, où la recherche compare ensuite les attributs de la clé.

### Le `query_hash` et le hachage de Zobrist

`query_hash` et `query_plan_hash` sont apparus avec SQL Server 2008. Bart Duncan, de Microsoft, les a présentés la même année comme des empreintes dans son billet [*Query Fingerprints and Plan Fingerprints*](https://learn.microsoft.com/en-us/archive/blogs/bartd/query-fingerprints-and-plan-fingerprints-the-best-sql-2008-feature-that-youve-never-heard-of) : elles ignorent espaces, commentaires et littéraux, ne dépendent ni de la base ni de l'instance, et des collisions sont possibles. Trois textes qui ne diffèrent que par un littéral donnent ainsi trois entrées, trois `sql_handle`, et un seul `query_hash` :

| Littéral | `sql_handle` (début) | `query_hash` | `query_plan_hash` |
|---|---|---|---|
| 42 | `0x02000000EBE0B80E` | `0x72E9689BB8C07D61` | `0x2AAACFCC4662E420` |
| 43 | `0x0200000007CED70E` | `0x72E9689BB8C07D61` | `0x2AAACFCC4662E420` |
| 1 | `0x0200000077BF3138` | `0x72E9689BB8C07D61` | `0x2AAACFCC4662E420` |

La [documentation de `sys.query_store_query`](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-query-transact-sql) définit `query_hash` comme un hachage de Zobrist calculé sur l'arbre logique lié (*bound logical query tree*), l'arbre que produit l'analyse de la requête une fois les noms résolus, avant l'optimisation. Albert Zobrist l'a inventé en 1970 pour les programmes d'échecs ([*A New Hashing Method with Application for Game Playing*](https://research.cs.wisc.edu/techreports/1970/TR88.pdf), rapport technique 88, université du Wisconsin). Un programme d'échecs rencontre la même position par des chemins différents et veut la reconnaître sans la comparer pièce par pièce. Zobrist tire au hasard, une fois pour toutes, un nombre de 64 bits pour chaque couple (pièce, case) : 12 sortes de pièces sur 64 cases, 768 nombres. Le hachage d'une position est le OU exclusif (XOR, noté ⊕) des nombres des pièces présentes :

h(position) = R[cavalier blanc, g1] ⊕ R[roi blanc, e1] ⊕ … ⊕ R[roi noir, e8]

Le XOR a deux propriétés qui font tout l'intérêt de la méthode. Il est commutatif, donc le hachage ne dépend pas de l'ordre dans lequel on parcourt les pièces. Et chaque nombre est son propre inverse (x ⊕ x = 0), donc jouer `Cg1-f3` se répercute par deux XOR, sans tout recalculer : h' = h ⊕ R[cavalier blanc, g1] ⊕ R[cavalier blanc, f3]. Les nombres étant aléatoires, deux positions différentes n'ont la même valeur qu'avec une probabilité d'environ 2⁻⁶⁴.

Microsoft ne publie ni ce qui joue le rôle de la pièce et de la case dans un arbre de requête, ni la table des nombres. La structure se voit pourtant de l'extérieur, parce qu'un hachage de Zobrist a une propriété mesurable : il est linéaire pour le XOR. Si changer un élément de la requête revient à retirer un nombre et à en ajouter un autre, l'écart entre les deux hachages (leur XOR) ne dépend que de l'élément changé, et pas du reste de la requête. Les hachages se lisent sans exécuter la requête, dans le plan estimé (`SET SHOWPLAN_XML ON`, attribut `QueryHash` de l'élément `StmtSimple`). Sur une table `dbo.Z (a, b, c, d)`, sur 2025 CU10 :

| Requête | `query_hash` |
|---|---|
| `SELECT a FROM dbo.Z WHERE b = 1` | `0x7999CB2843E0BFA1` |
| `SELECT a FROM dbo.Z WHERE c = 1` | `0xA9FF2C4E40ED72CA` |
| `SELECT d FROM dbo.Z WHERE b = 1` | `0x0FAE3912CBD0ECBF` |
| `SELECT d FROM dbo.Z WHERE c = 1` | `0xDFC8DE74C8DD21D4` |

Remplacer `b` par `c` dans le prédicat donne le même écart, que la colonne projetée soit `a` ou `d` :

`0x7999CB2843E0BFA1` ⊕ `0xA9FF2C4E40ED72CA` = `0x0FAE3912CBD0ECBF` ⊕ `0xDFC8DE74C8DD21D4` = `0xD066E766030DCD6B`

On peut donc prédire le hachage de la quatrième requête sans le demander au moteur : `0x0FAE3912CBD0ECBF` ⊕ `0xD066E766030DCD6B` = `0xDFC8DE74C8DD21D4`. Une fonction de hachage ordinaire (MD5, ou un hachage polynomial du texte) n'aurait aucune raison de donner deux fois le même écart. Dix vérifications sur douze ont donné le même écart : changer la colonne projetée, la colonne du prédicat, l'opérateur (`=` contre `>`), la table de la colonne projetée, ou ajouter un prédicat, ajoute toujours la même quantité par XOR. C'est la signature d'un hachage de Zobrist. Les deux autres sont les exceptions décrites au paragraphe suivant : la contribution d'une colonne de prédicat dépend de la table, et deux prédicats joints par `AND` ne se déduisent pas des requêtes à un prédicat, parce que le `AND` change leur place dans l'arbre.

Reste la « case ». Aux échecs, la même pièce n'a pas la même valeur en g1 et en f3. Dans `query_hash`, la même colonne n'apporte pas la même contribution en première et en deuxième position de la liste `SELECT`. Le modèle h = B ⊕ P₁(x) ⊕ P₂(y), où P₁ et P₂ sont les contributions d'une colonne à chaque position, prédit exactement les hachages des permutations : h(`SELECT d, a`) = h(`SELECT a, a`) ⊕ h(`SELECT a, d`) ⊕ h(`SELECT d, d`), vérifié sur quatre combinaisons. La position fait donc partie de la case, et c'est pour cela que l'ordre compte partout : `WHERE b = 1 AND c = 2` et `WHERE c = 2 AND b = 1`, `SELECT a, d` et `SELECT d, a`, `Z JOIN Y` et `Y JOIN Z`, `y.b = z.b` et `z.b = y.b`, `b = 1` et `1 = b` ont chacun deux `query_hash` différents. Deux requêtes équivalentes écrites différemment ne se regroupent pas. Un détail montre que la case n'est pas seulement un rang : l'écart dû à la colonne d'un prédicat n'est pas le même dans deux tables différentes, alors que celui d'une colonne projetée l'est. Le « lieu » d'un nœud inclut donc au moins une partie de son contexte dans l'arbre ; je n'ai pas établi s'il s'agit de son chemin complet depuis la racine.

Ce qui entre dans la pièce se lit par les mêmes essais :

| Ignoré par `query_hash` | Compté |
|---|---|
| espaces, commentaires, casse, crochets | le nom de la table (`Z` contre `Z2`, même structure) |
| l'alias d'une colonne de sortie (`a AS x`) | le nom du schéma (`dbo.T`, `s1.T`, `s2.T`) |
| `dbo.Z` contre `Z` quand c'est la même table | le nom des colonnes (`b` contre `c`, même type) |
| la valeur et le type d'un littéral (`1`, `1000000`, `'x'`, `N'x'`, `1.5`) | le nom d'un alias de table (`t` contre `u`) |
| une variable à la place d'un littéral, et son type | une conversion explicite (`CAST(1 AS bigint)`) |
| la valeur de `TOP` | le nombre d'éléments d'une liste `IN` |
| l'`object_id` de la table, le `column_id` et le type des colonnes | tout indicateur écrit dans le texte, et sa valeur |

Le hachage est calculé sur les noms, pas sur les identifiants du catalogue. Dans deux bases où la même table `dbo.Z` a deux `object_id` différents, et même après l'avoir recréée avec ses colonnes dans un autre ordre ou une colonne en `bigint`, le `query_hash` est resté `0x7999CB2843E0BFA1`. C'est ce qui permet de comparer des `query_hash` d'une base ou d'une instance à l'autre. Les littéraux sont remplacés par un marqueur neutre, sans leur type ; leur valeur n'entre jamais dans le hachage.

### Le hachage dans le code du moteur

Le débogueur confirme la mesure. Les symboles publics de `sqllang.dll` contiennent une classe `CFingerprintUtilStatic`, dont le membre `m_randomTable` est un tableau de 31 × 256 entiers de 64 bits, tous distincts et non nuls (les premiers valent `0xA3AFCDBDC442EBF3`, `0xB577B9B52AF851A3`, `0xCA68ED0953AD020F`), et des fonctions `ZobristHashBytes`, `ZobristHashForBool` et `ZobristHashForNULL`. La table a une valeur aléatoire par couple (position d'un octet, valeur de cet octet) : c'est la table de Zobrist, appliquée non à des pièces sur des cases mais aux octets d'une chaîne, un nom de colonne par exemple. C'est pour cela que les noms comptent et pas les identifiants.

Pendant la première compilation d'une jointure, `gdb` a compté 73 appels de `ZobristHashBytes`, 58 de `ZobristHashForBool` et 47 de `ZobristHashForNULL`, venus de `CFingerprintUtilEx::GenFingerprint`, appelée par `PqoBuild`, la fonction qui construit l'entrée de l'optimiseur ; chaque opérateur logique y ajoute sa contribution par une méthode `ComputeHashsum` (ici celle de l'agrégat, `CLogOp_GbAgg`). À la réutilisation du plan, aucune de ces fonctions n'a été appelée : le `query_hash` est calculé à la compilation et gardé avec le plan, ce qui confirme qu'il ne sert pas à la recherche.

> Le hachage par tabulation :
>
> Le procédé a un nom en théorie du hachage : le hachage par tabulation (*tabulation hashing*). On découpe la clé en caractères, on lit pour chaque caractère et chaque position une valeur aléatoire dans une table, et on combine par XOR. Zobrist l'avait inventé pour les échecs ; J. Lawrence Carter et Mark Wegman l'ont étudié comme famille de hachage universel dès 1977 (STOC, publié en revue en 1979), et Mihai Pătrașcu et Mikkel Thorup ont montré en 2011 (*The Power of Simple Tabulation Hashing*, STOC) que cette forme simple se comporte, pour la plupart des usages, presque aussi bien qu'un hachage idéal, pour le prix de quelques lectures de table. Pour un moteur qui calcule une empreinte à chaque compilation, c'est le bon compromis : rapide, et combinable nœud par nœud.


`ComputeWstrCrc` calcule un CRC sur une chaîne Unicode avec la table du CRC-32 (elle commence par `0x00000000`, `0x77073096`, `0xEE0E612C`, celle du polynôme `0xEDB88320`). Parmi les fonctions de hachage suivies, elle est la seule appelée aussi souvent à la réutilisation qu'à la compilation : elle pourrait servir à la recherche. Je n'ai pas établi qu'elle produit l'`objectid` d'un batch : le CRC-32 du texte entier ne correspond pas, et la fonction ne lit que des tranches de seize caractères.

### Les indicateurs et le `query_hash`

La page de [`sys.query_store_query`](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-query-transact-sql) dit que les indicateurs de requête n'entrent pas dans le hachage. La mesure distingue deux sortes d'indicateurs. Ceux qu'on écrit dans le texte de la requête, clause `OPTION` ou indicateur de table, font partie de l'arbre logique et changent le `query_hash`, valeur comprise :

| Requête | `query_hash` |
|---|---|
| `SELECT a FROM dbo.Z WHERE b = 1` | `0x7999CB2843E0BFA1` |
| même requête avec `OPTION (MAXDOP 1)` | `0x1C7CC80AC7AECA90` |
| avec `OPTION (MAXDOP 2)` | `0xFA119C4795B4AF7E` |
| avec `OPTION (RECOMPILE)` | `0x227116E0CA973356` |
| avec `WITH (NOLOCK)` | `0x881E58708A60CB0F` |

Ceux que le Query Store pose sur une requête existante, par `sys.sp_query_store_set_hints`, ne le changent pas. J'ai posé `OPTION (MAXDOP 1)` par cette procédure sur une jointure de `query_hash` `0x3765C48B49EA3AD0` : la requête a été recompilée, son plan contient l'attribut `QueryStoreStatementHintId`, et son `query_hash` est resté le même dans `sys.query_store_query`, dans `sys.dm_exec_query_stats` et dans le plan XML. La phrase de la documentation vaut donc pour les indicateurs du Query Store, pas pour ceux du texte. Conséquence pratique : ajouter `OPTION (RECOMPILE)` dans le code d'une application crée une nouvelle requête pour le Query Store, avec un nouvel historique, alors que le même indicateur posé par le Query Store garde l'historique.

[Kendra Little a montré en 2024](https://kendralittle.com/2024/11/24/query-hash-values-meaningless-same-as-query-plan-hash-sql-server/), sur 2022 CU15, qu'après un plan forcé le `query_hash` d'une requête du Query Store pouvait prendre la valeur de son `query_plan_hash`. Je ne l'ai pas reproduit : sur 2025 CU10, une requête paramétrée avec deux plans, dont l'un forcé, a gardé son `query_hash` partout (Query Store, `sys.dm_exec_query_stats`, plan XML), et seul `query_plan_hash` a pris la valeur du plan forcé. Un seul scénario ne réfute pas son observation, qui vient peut-être d'un autre chemin (correction automatique des plans, autre build).

### Le `query_hash` et la recherche d'un plan

La recherche décrite plus haut passe par le texte exact du batch (son `objectid` haché, puis le texte) ou par l'`object_id` du module. Dans *SQL Server Execution Plans*, 3e édition (Redgate, 2018, p. 36, « Criteria for plan reuse »), Grant Fritchey décrit une recherche par `QueryHash`, suivie d'une comparaison du texte exact, des options `SET` et de la base. Les trois requêtes du tableau des littéraux ont le même `query_hash` et des entrées distinctes, mais ce constat ne réfute pas son explication : la comparaison du texte qu'il décrit suffirait à les séparer.

Pour chercher un plan à partir de son `query_hash`, il faudrait connaître ce hash avant de retrouver le plan. Or, dans l'essai au débogueur sur 2025 CU10, les fonctions qui le calculent ont été appelées lors de la première compilation, puis plus du tout lorsque le moteur a retrouvé et réutilisé ce plan. L'essai montre donc une réutilisation sans nouveau calcul du `query_hash` par ces fonctions. Il ne suffit pas à décrire tous les chemins de recherche, ni à conclure sur les versions couvertes par le livre de 2018.

L'usage du `query_hash` pour le diagnostic est distinct : une fois les plans compilés, on peut lire leurs empreintes dans `sys.dm_exec_query_stats` et regrouper les instructions de même forme logique, même si leurs textes et leurs entrées de cache diffèrent :

```sql
SELECT qs.query_hash,
       COUNT(DISTINCT qs.plan_handle) AS nb_entrees,
       SUM(qs.execution_count) AS executions,
       SUM(qs.total_worker_time) / 1000 AS cpu_ms,
       MIN(SUBSTRING(t.text, qs.statement_start_offset / 2 + 1, 200)) AS exemple
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
GROUP BY qs.query_hash
HAVING COUNT(DISTINCT qs.plan_handle) > 1
ORDER BY nb_entrees DESC;
```

Une ligne avec des centaines d'entrées pour un seul `query_hash` désigne une requête que l'application envoie avec ses valeurs dans le texte, et qui mériterait d'être paramétrée.

## La clé du cache

Un cache est pratiquement toujours un tableau associatif (un dictionnaire) : on y range une valeur sous une clé, et on la retrouve en présentant la même clé. La clé du cache (*cache key*) est l'ensemble des informations que l'application compare pour trouver une valeur dans le cache.
Le terme est général : la norme HTTP ([RFC 9111, *HTTP Caching*, section 2](https://www.rfc-editor.org/rfc/rfc9111.html#section-2), 2022) définit la « clé du cache » d'une réponse comme, au minimum, la méthode et l'URL de la requête.

Le principe qui fixe ce qu'il faut mettre dans la clé vient de la mémoïsation, que Donald Michie a nommée en 1968 (*Memo functions and machine learning*, *Nature*, vol. 218) : on peut garder le résultat d'une fonction et le resservir sans la recalculer, à condition que la clé contienne toutes les entrées dont ce résultat dépend. Un plan est le résultat d'une fonction, la compilation, dont les entrées sont le texte, mais aussi tout ce qui change le sens de ce texte ou le plan qu'on en tire : la base de contexte, les options `SET`, la langue, le niveau de compatibilité, le schéma qui sert à résoudre un nom.

Deux erreurs sont possibles.
- Oublier une entrée dans la clé fait resservir un plan faux : un plan compilé pour `s1.T` servirait à un utilisateur qui doit lire `s2.T`.
- Y mettre une entrée qui ne change pas le résultat crée des doublons inutiles : c'est le cas d'`ARITHABORT`, décrit plus loin.

HTTP a le même problème et la même solution : l'en-tête `Vary` d'une réponse ajoute à la clé les en-têtes de la requête dont la réponse dépend (la langue, l'encodage), exactement comme `set_options` ajoute les options de la session.

Un même texte peut donc avoir plusieurs plans en cache, un par combinaison d'attributs. Ces attributs se trouvent dans `sys.dm_exec_plan_attributes`, avec la colonne `is_cache_key` :

```sql
SELECT a.attribute, a.value
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
WHERE p.plan_handle = (SELECT TOP (1) plan_handle FROM sys.dm_exec_cached_plans)
  AND a.is_cache_key = 1;
```

Sur 2025 CU10, dix-huit attributs ont `is_cache_key` à 1 : `acceptable_cursor_options`, `compat_level`, `date_first`, `date_format`, `dbid`, `dbid_execute`, `is_replication_specific`, `language_id`, `merge_action_type`, `objectid`, `optional_clr_trigger_dbid`, `optional_clr_trigger_objid`, `optional_spid`, `parent_plan_handle`, `required_cursor_options`, `set_options`, `status`, `user_id`.

Kalen Delaney en comptait dix-sept pour SQL Server 2012 dans *Microsoft SQL Server 2012 Internals* (Microsoft Press, 2013, chapitre 12 « Plan caching and recompilation », section « Cache stores », tableau 12-1), sans `parent_plan_handle`. La [documentation de `sys.dm_exec_plan_attributes`](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-plan-attributes-transact-sql#table-returned) le dit pour `objectid`, `status` et `optional_spid`, sans donner la liste complète.

Les plus fréquents en pratique :

- `set_options`, un champ de bits des options `SET` de la session à la compilation ;
- `user_id`, le schéma par défaut quand le batch contient un nom d'objet non qualifié ;
- `dbid`, la base de contexte, celle du `USE`, et non celle des objets cités ;
- `language_id`, `date_format` et `date_first`, la langue et les réglages de date ;
- `compat_level`, le niveau de compatibilité de la base de contexte pour un batch, de la base de création pour un module ;
- `optional_spid`, la session, quand une procédure utilise une table temporaire qu'elle n'a pas créée : une entrée par session.

### Les options SET

J'ai exécuté le même texte, `SELECT MAX(v) AS m FROM T;`, dans la même session, sous huit contextes. Il a laissé huit entrées, avec un seul `sql_handle` :

| Contexte | `set_options` | `user_id` | `date_format` | `language_id` | `date_first` |
|---|---|---|---|---|---|
| `sqlcmd` par défaut, `sa` | 251 | 1 | 1 | 0 | 7 |
| `ANSI_NULLS OFF` | 219 | 1 | 1 | 0 | 7 |
| `QUOTED_IDENTIFIER OFF` | 187 | 1 | 1 | 0 | 7 |
| `ARITHABORT ON` | 4 347 | 1 | 1 | 0 | 7 |
| `DATEFORMAT dmy` | 251 | 1 | 2 | 0 | 7 |
| `LANGUAGE French` | 251 | 1 | 2 | 2 | 1 |
| utilisateur de schéma par défaut `s1` | 251 | 5 | 1 | 0 | 7 |
| utilisateur de schéma par défaut `s2` | 251 | 6 | 1 | 0 | 7 |

Les écarts de `set_options` se lisent bit à bit, avec la table de la page `sys.dm_exec_plan_attributes` : 32 pour `ANSI_NULLS`, 64 pour `QUOTED_IDENTIFIER`, 4 096 pour `ARITHABORT`. `LANGUAGE French` change aussi `date_format`, qui passe à 2 (`dmy`), et `date_first`, qui passe à 1 (lundi). Les options qui changent la clé, d'après le guide d'architecture du traitement des requêtes, sont `ANSI_DEFAULTS`, `ANSI_NULL_DFLT_OFF`, `ANSI_NULL_DFLT_ON`, `ANSI_NULLS`, `ANSI_PADDING`, `ANSI_WARNINGS`, `ARITHABORT`, `CONCAT_NULL_YIELDS_NULL`, `DATEFIRST`, `DATEFORMAT`, `FORCEPLAN`, `LANGUAGE`, `NO_BROWSETABLE`, `NUMERIC_ROUNDABORT` et `QUOTED_IDENTIFIER`. `XACT_ABORT` n'en fait pas partie.

En pratique, l'option qui dédouble le plus souvent les plans est `ARITHABORT`. SSMS ouvre ses sessions avec `ARITHABORT ON`, la plupart des pilotes avec `OFF`. Tant qu'`ANSI_WARNINGS` est à `ON`, `ARITHABORT` n'a aucun effet sur le résultat, mais il reste dans la clé : la requête copiée de l'application vers SSMS ne retrouve pas le plan de l'application, elle en compile un autre, avec les valeurs de paramètres du moment. C'est la cause la plus fréquente du « lent dans l'application, rapide dans SSMS » qu'Erland Sommarskog a décrit dans son article [*Slow in the Application, Fast in SSMS?*](https://www.sommarskog.se/query-plan-mysteries.html).

Sur `PachaDataFormation` :

```sql
USE PachaDataFormation;
GO
SET ARITHABORT OFF;
GO
SELECT COUNT(*) FROM Contact.Contact AS c JOIN Contact.Societe AS s ON s.SocieteId = c.SocieteId;
GO
SET ARITHABORT ON;
GO
SELECT COUNT(*) FROM Contact.Contact AS c JOIN Contact.Societe AS s ON s.SocieteId = c.SocieteId;
GO
SELECT p.plan_handle, p.usecounts, a.value AS set_options
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
WHERE t.text LIKE N'SELECT COUNT(*) FROM Contact.Contact AS c JOIN%'
  AND a.attribute = N'set_options';
```

Dans la version ODBC de `sqlcmd` utilisée ici, [l'option `-I`](https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility#query-execution-options) ouvre la connexion avec `QUOTED_IDENTIFIER ON` : les guillemets doubles délimitent alors des noms d'objets, par exemple `"MaTable"`, et les chaînes de caractères s'écrivent entre apostrophes. La dernière requête rend deux lignes, deux `plan_handle`, `set_options` 251 et 4 347 sous `sqlcmd -I` (davantage si d'autres sessions ont exécuté le même texte sous d'autres options). Sans `-I`, ce client ouvre sa session avec `QUOTED_IDENTIFIER OFF`, et les deux valeurs deviennent 187 et 4 283 : le client choisit les options, donc la clé.
Pour une procédure, `ANSI_NULLS` et `QUOTED_IDENTIFIER` sont enregistrées avec l'objet à sa création : elles ne dédoublent pas son plan.

> Les options en tête des scripts générés par SSMS :
>
> C'est pour conserver ces réglages que SSMS place `SET ANSI_NULLS` et `SET QUOTED_IDENTIFIER` avant le `CREATE` ou l'`ALTER PROCEDURE` dans le script qu'il génère, par exemple :
> 
> ```sql
> SET ANSI_NULLS ON;
> GO
> SET QUOTED_IDENTIFIER ON;
> GO
> CREATE PROCEDURE dbo.MaProcedure
> AS
>     SELECT 1;
> GO
> ```
> 
> SQL Server enregistre ces deux options avec la procédure lors de sa création ou de sa modification, puis utilise ces réglages pour son code statique lors des appels suivants. Les instructions `SET` en tête du script permettent donc de recréer la procédure avec les mêmes réglages, quelle que soit la configuration initiale de la session qui exécute le script. Ce comportement est décrit dans la [documentation de `CREATE PROCEDURE`, section « Interoperability »](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql#interoperability).


Ce dédoublement a trois coûts, qu'on mesure sur un même texte exécuté quatre fois dans la même session, sous `SET ANSI_DEFAULTS ON`, puis `OFF`, puis `ON`, puis `OFF` :

| Entrée | `set_options` | `usecounts` | `query_hash` | `query_plan_hash` | Exécutions dans `sys.dm_exec_query_stats` |
|---|---|---|---|---|---|
| 1 (`ON`) | 251 | 2 | `0x3765C48B49EA3AD0` | `0x636520DF98647923` | 2 |
| 2 (`OFF`) | 266 | 2 | `0x3765C48B49EA3AD0` | `0x636520DF98647923` | 2 |

Le premier coût est une compilation complète de plus : une session d'événements étendus a relevé deux `sql_statement_post_compile` et deux `sp_cache_insert` pour ce texte, puis un `sp_cache_hit` par exécution suivante. Le deuxième est la mémoire d'une deuxième entrée.
Le troisième coût gêne le diagnostic : les quatre exécutions du même texte sont réparties entre deux entrées de `sys.dm_exec_query_stats`, chacune rattachée à un `plan_handle` différent. Chaque entrée ne compte que ses deux exécutions et leurs consommations de CPU et de lectures. Un rapport qui regroupe par `plan_handle` affiche donc deux lignes de deux exécutions ; pour retrouver les quatre exécutions et leur coût total, il faut réunir les statistiques des deux plans. La charge d'une même requête peut ainsi paraître plus faible si l'on examine chaque plan séparément.

Le [guide d'architecture du traitement des requêtes, section « Cache multiple plans for the same query »](https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide#cache-multiple-plans-for-the-same-query) décrit cet exemple autrement. Il dit que la deuxième entrée « ne déclenche pas de recompilation » et que « les deux entrées font référence au même plan », puisqu'elles ont le même `query_plan_hash`. C'est exact au sens étroit : aucun événement `sql_statement_recompile`, puisqu'une recompilation, au sens du moteur, remplace le plan d'une entrée existante. Mais le lecteur en conclut que le dédoublement ne coûte rien, alors qu'il coûte une compilation, une entrée et des statistiques séparées. Le même `query_plan_hash` dit seulement que l'optimiseur est arrivé deux fois à la même forme de plan.

Pour trouver ces doublons dans un cache réel, il faut chercher les `sql_handle` qui ont plusieurs `plan_handle`, et afficher les attributs de la clé qui les séparent. Voici le script de mon dépôt tsql-scripts :


Plans multiples pour un même texte (https://github.com/rudi-bruchez/tsql-scripts/blob/a570746cba1259a5fc83fca8f525e060d6f55dea/diagnostics/Memory/plan-cache-duplicates.sql) :

```sql
-----------------------------------------------------------------
-- Find batch texts with multiple cached plans and compare cache keys
--
-- rudi@babaluga.com, go ahead license
-----------------------------------------------------------------
-- Read-only, instance-wide. Requires VIEW SERVER STATE before SQL Server
-- 2022, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.
-- Different databases can legitimately have separate plans for the same text.
-- Source: https://www.pachadata.com/docs/articles/sqlserver/internals/cache-de-plans/

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH plans AS (
    SELECT DISTINCT qs.sql_handle, qs.plan_handle
    FROM sys.dm_exec_query_stats AS qs
), duplicates AS (
    SELECT sql_handle, COUNT(*) AS plan_count
    FROM plans
    GROUP BY sql_handle
    HAVING COUNT(*) > 1
)
SELECT d.plan_count,
       DB_NAME(CAST(pa.dbid AS int)) AS database_name,
       CAST(pa.set_options AS int) AS set_options,
       CAST(pa.user_id AS int) AS user_id,
       CAST(pa.language_id AS int) AS language_id,
       cp.usecounts,
       LEFT(t.text, 70) AS text_start
FROM duplicates AS d
JOIN plans AS p ON p.sql_handle = d.sql_handle
JOIN sys.dm_exec_cached_plans AS cp ON cp.plan_handle = p.plan_handle
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) AS t
CROSS APPLY (
    SELECT dbid, set_options, user_id, language_id
    FROM (SELECT attribute, value FROM sys.dm_exec_plan_attributes(cp.plan_handle)) AS s
    PIVOT (MAX(value) FOR attribute IN (dbid, set_options, user_id, language_id)) AS pv
) AS pa
ORDER BY d.plan_count DESC, text_start, set_options
OPTION (RECOMPILE, MAXDOP 1);
```


Sur mon instance de test, elle a trouvé les trois causes : des `set_options` différents (l'exemple ci-dessus), des schémas par défaut différents (`user_id` 1, 5 et 6 pour un même SQL dynamique, voir la section suivante), et des bases différentes.
Ce dernier cas n'est pas un gaspillage : le `sql_handle` ne contient pas la base, et le même texte exécuté dans deux bases doit avoir deux plans.
Regardez la colonne `database_name` avant de conclure.

### Le schéma par défaut

Quand un batch ad hoc ou préparé nomme un objet sans son schéma, `FROM T` au lieu de `FROM dbo.T`, le moteur doit résoudre le nom à partir du schéma par défaut de l'utilisateur, et le plan n'est valable que pour ce schéma. L'attribut `user_id` contient alors ce schéma. Quand tous les noms sont qualifiés, il vaut -2, et l'entrée se partage entre tous les utilisateurs.

La [documentation de `sys.dm_exec_plan_attributes`](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-plan-attributes-transact-sql#table-returned) dit que toute autre valeur que -2 « représente l'identifiant de l'utilisateur qui soumet la requête ». La mesure dit autre chose. Deux utilisateurs différents (`principal_id` 7 et 8), de même schéma par défaut (`schema_id` 6), ont partagé une seule entrée, avec `user_id` à 6. C'est le schéma par défaut, comme l'avaient écrit [Sangeetha Shekar en 2007](https://learn.microsoft.com/en-us/archive/blogs/sqlprogrammability/6-0-best-programming-practices), [Aaron Bertrand en 2014](https://sqlperformance.com/2014/11/t-sql-queries/multiple-plans-identical-query) et [Erland Sommarskog](https://www.sommarskog.se/query-plan-mysteries.html). Le nombre d'entrées suit donc le nombre de schémas par défaut distincts parmi les utilisateurs qui exécutent le batch. Dans une base où tout le monde a `dbo` comme schéma par défaut, un nom non qualifié ne crée pas d'entrée supplémentaire, mais il oblige le moteur à une résolution de plus, et il suffit d'un utilisateur au schéma par défaut différent pour dédoubler les plans.

Les procédures suivent une autre règle. Dans le code statique d'une procédure, une référence de table sans schéma, dans la même base, se résout dans le schéma de la procédure, puis dans `dbo`. L'équipe SQL Server décrit cette règle dans [*Name resolution, default schema, implicit schema Part I*](https://techcommunity.microsoft.com/blog/sqlserver/name-resolution-default-schema-implicit-schema-part-i/383052/), publié initialement le 3 avril 2006, au passage « Active Default Schema » ; elle y distingue notamment le SQL dynamique, qui utilise le schéma par défaut de l'utilisateur courant. La [documentation *Execute a Stored Procedure*, section « User-defined stored procedures »](https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure#user-defined-stored-procedures), précise aussi la recherche dans le schéma de la procédure englobante, puis dans `dbo`, pour un appel de procédure sans schéma. Pour le vérifier, j'ai créé trois tables `T` de contenus différents, `dbo.T` (2 lignes), `s1.T` (3 lignes) et `s2.T` (4 lignes), et des procédures qui lisent `FROM T` sans schéma, appelées par trois utilisateurs de schémas par défaut différents :

```sql
CREATE OR ALTER PROCEDURE dbo.P AS SELECT COUNT(*) AS lignes FROM T;
GO
CREATE OR ALTER PROCEDURE s1.P AS SELECT COUNT(*) AS lignes FROM T;
GO
CREATE OR ALTER PROCEDURE dbo.Pdyn AS EXEC (N'SELECT COUNT(*) AS lignes FROM T');
GO
CREATE OR ALTER PROCEDURE dbo.Pspx AS EXEC sp_executesql N'SELECT COUNT(*) AS lignes FROM T';
GO
-- s3.P : même procédure dans un schéma s3 qui n'a pas de table T
EXECUTE AS USER = 'u1'; EXEC dbo.P; EXEC s1.P; EXEC dbo.Pdyn; EXEC dbo.Pspx; REVERT;
```

| Appelant (schéma par défaut) | `dbo.P` | `s1.P` | `s3.P` | `dbo.Pdyn` (`EXEC`) | `dbo.Pspx` (`sp_executesql`) |
|---|---|---|---|---|---|
| `u1` (`s1`) | `dbo.T` | `s1.T` | `dbo.T` | `s1.T` | `s1.T` |
| `u2` (`s2`) | `dbo.T` | `s1.T` | `dbo.T` | `s2.T` | `s2.T` |
| `u3` (`dbo`) | `dbo.T` | `s1.T` | `dbo.T` | `dbo.T` | `dbo.T` |

Dans le code statique d'une procédure, `FROM T` désigne la même table pour tous les appelants. Par exemple, `dbo.P` lit toujours `dbo.T`, et `s1.P` lit toujours `s1.T`, même lorsque l'appelant a `s2` comme schéma par défaut. Pour `s3.P`, le moteur cherche `s3.T` puis, comme cette table n'existe pas, utilise `dbo.T`.

Les trois utilisateurs peuvent donc partager le plan de chaque procédure. Dans cet essai, chaque procédure a une seule entrée de cache, avec `usecounts` à 3. L'attribut `user_id` de cette entrée contient l'identifiant du schéma de la procédure : 1 pour `dbo`, 5 pour `s1`, 7 pour `s3`.

Avec le SQL dynamique de `dbo.Pdyn` et de `dbo.Pspx`, le choix de la table dépend en revanche de l'appelant : `FROM T` désigne `s1.T` pour `u1`, `s2.T` pour `u2` et `dbo.T` pour `u3`. Chaque procédure conserve son propre plan, mais le batch dynamique qu'elle exécute a besoin d'une entrée de cache par schéma par défaut. Pour chacun des deux textes dynamiques, l'essai a donc laissé trois entrées, avec `user_id` à 5, 6 et 1. Un nom sans schéma dans le SQL dynamique peut ainsi faire lire des tables différentes et multiplier les plans en cache.

## Le paramétrage

Un plan n'est réutilisé que si le texte est identique. Une application qui écrit ses valeurs dans le texte de la requête, `WHERE InscriptionId = 42` puis `WHERE InscriptionId = 43`, produirait donc une entrée par valeur. Trois mécanismes permettent de partager un plan entre valeurs : le paramétrage simple, fait par le moteur ; le paramétrage forcé, qu'on active par base ; et les requêtes paramétrées par le client, `sp_executesql` ou les instructions préparées.

### Les plans triviaux

Un plan qui ne laisse aucun choix à l'optimiseur entre dans le cache comme les autres, une entrée par texte.
Ce qui change, c'est le prix de sa compilation, et la place qu'il tient dans le paramétrage simple.

Avant de chercher un plan parmi les possibilités, l'optimiseur passe par une étape de plan trivial (*trivial plan*) : s'il n'existe qu'un plan raisonnable, il le prend sans calculer de coûts.
Une recherche par égalité sur la clé de l'index cluster en est l'exemple type : un seul accès possible, un `Clustered Index Seek`. Le plan XML l'indique par l'attribut `StatementOptmLevel="TRIVIAL"` ; sinon, il vaut `FULL`, et l'optimisation passe par ses phases de recherche (`search 0`, `search 1`, `search 2`), que compte `sys.dm_exec_query_optimizer_info`. Sur `PachaDataFormation`, où `Inscription.Inscription` a son index cluster sur `InscriptionId` et aucun index sur `ContactId` :

| Requête | `StatementOptmLevel` | Phase | Mémoire de compilation | Entrées laissées |
|---|---|---|---|---|
| `SELECT DateCreation FROM Inscription.Inscription WHERE InscriptionId = 42` | `TRIVIAL` | aucune | 144 Ko | une _shell query_ de 16 Ko et un plan `Prepared` de 56 Ko |
| la même avec `= 43` | | aucune optimisation | | une _shell query_ de plus |
| `... WHERE ContactId = 42` | `FULL` | `search 1`, `GoodEnoughPlanFound` | 240 Ko | une entrée `Adhoc` |
| la même avec `= 43` | `FULL` | `search 1`, à nouveau | 240 Ko | une deuxième entrée `Adhoc` |
| jointure de deux tables sur la clé | `FULL` | `search 1` | 296 Ko | une entrée `Adhoc` de 88 Ko |

Les deux compilations ont pris 0 à 1 ms ; une jointure avec `GROUP BY`, 2 ms. Le plan trivial coûte peu à compiler, mais il coûte surtout une seule fois, parce que c'est lui que le paramétrage simple sait partager : la valeur 43 a réutilisé le plan préparé de la valeur 42. La requête sur `ContactId`, sans index sur cette colonne, n'a pourtant qu'une stratégie raisonnable, le parcours complet. Mais l'index `nix_temp_01`, qui contient `DateCreation`, offre une autre façon de lire la colonne demandée, et ce choix fondé sur le coût suffit à faire passer l'optimiseur en `FULL`. Sur une copie de la table sans cet index, la même requête a un plan `TRIVIAL`. Ce n'est donc pas la valeur qui compte, mais l'existence d'une alternative : dès que l'optimiseur doit comparer des coûts, le plan n'est plus trivial, le paramétrage simple renonce, et chaque valeur coûte une optimisation et une entrée.

### Le paramétrage simple

Le paramétrage simple (*simple parameterization*), appelé autoparamétrage jusqu'à SQL Server 2000, est actif par défaut. Le moteur remplace les littéraux d'une requête par des paramètres, compile la forme paramétrée et la met en cache comme un plan `Prepared`. La requête d'origine laisse une deuxième entrée, de type `Adhoc`, que le livre blanc de Greg Low appelle une *shell query*, une requête « enveloppe ». Elle ne contient pas l'arbre d'opérateurs du plan partagé : elle conserve des informations sur la requête d'origine et un renvoi vers le plan préparé, visible dans son plan XML par l'attribut `ParameterizedPlanHandle`, ajouté en 2008. Elle sert à retrouver vite le plan paramétré la prochaine fois que le même texte arrive, sans refaire le paramétrage. Elle a fait 16 Ko dans toutes mes mesures : 8 Ko pour les structures de compilation de l'enveloppe et 8 Ko pour son contexte d'exécution.

> Shell Query :
>
> Un plan trivial coûte peu à compiler, mais sa préparation demande encore du travail. Pour retrouver un plan préparé à partir d'un texte ad hoc, SQL Server doit analyser ce texte, remplacer les littéraux par des paramètres, normaliser l'instruction et la reconvertir en une représentation SQL utilisable pour la recherche dans le cache. La *shell query* évite de répéter ces étapes lorsque le même texte, avec les mêmes littéraux, revient.
> 
> Dans l'exemple ci-dessous, les textes avec `InscriptionId = 42` et `InscriptionId = 43` ont chacun leur enveloppe, mais renvoient au même plan `Prepared` avec `InscriptionId = @1`. Le plan préparé permet le partage entre plusieurs valeurs ; chaque enveloppe permet de retrouver rapidement ce plan à partir du texte d'origine. Le caractère trivial du plan et l'enveloppe économisent donc du travail à des étapes différentes. Cela ne signifie pas que tout plan trivial nécessite une enveloppe : il s'agit ici du paramétrage effectué par le moteur.
> 
> Paul White détaille ce rôle dans [*Simple Parameterization and Trivial Plans, Part 5*, section « Shell Plans Revisited »](https://sqlperformance.com/2022/06/sql-optimizer/simple-parameterization-and-trivial-plans-part-5), publié le 8 juin 2022. Il précise que refaire les étapes de paramétrage pour retrouver le plan préparé coûte moins que produire même un plan trivial, mais reste plus coûteux que l'accès par l'enveloppe, un écart qui compte pour des instructions simples exécutées très fréquemment.


> Contenu des deux pages :
>
> Une enveloppe sans arbre d'opérateurs utilise quand même des structures de compilation et d'exécution assez volumineuses. Dans la [mesure sur SQL Server 2025 CU10 décrite plus haut](#la-taille-dune-entrée), les symboles publics identifient un `CCompPlan` de 2 448 octets et un `CStmtPrepQuery` de 2 496 octets dans la partie compilée. Le contexte contient notamment un `CXStmtPrepQuery` de 2 192 octets et un autre bloc de 2 120 octets dont je n'ai pas déterminé la classe. Les deux pages de 8 Ko ne sont donc bien utilisées : environ 12 Ko sont consommés par l'allocateur, dont les en-têtes et l'alignement, et environ 4 Ko restent disponibles.
> 
> Après 100 exécutions supplémentaires de chaque texte, j'ai trouvé deux allocations de plus dans la partie compilée, de 176 et 40 octets. Avec l'alignement, elles consomment 224 octets supplémentaires, toujours dans la même page : l'occupation passe à 75,8 % des 16 Ko. Leur fonction et la cause de leur apparition ne sont pas déterminées. Ces observations ne permettent pas de chiffrer la mémoire qu'une implémentation différente pourrait économiser.


```sql
USE PachaDataFormation;
GO
SELECT DateCreation FROM Inscription.Inscription WHERE InscriptionId = 42;
GO
SELECT DateCreation FROM Inscription.Inscription WHERE InscriptionId = 43;
GO
SELECT DateCreation FROM Inscription.Inscription WHERE InscriptionId = 4242;
GO
SELECT p.objtype, p.size_in_bytes, p.usecounts, LEFT(t.text, 90) AS debut_du_texte
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.text LIKE N'%Inscription%Inscription%InscriptionId%'
  AND t.text NOT LIKE N'%dm_exec_cached_plans%';
```

Sur 2025 CU10, ces trois requêtes laissent trois _shell queries_ `Adhoc` de 16 384 octets et deux plans `Prepared` de 57 344 octets : `(@1 tinyint)SELECT [DateCreation] FROM [Inscription].[Inscription] WHERE [InscriptionId]=@1`, utilisé deux fois, et le même texte avec `@1 smallint`, pour 4242. Le type du paramètre suit la valeur : la plus petite taille d'entier qui la contient. Une application dont les identifiants passent de 255 à 256 puis à 32 768 compile ainsi trois plans préparés. Le texte paramétré est normalisé, crochets autour des noms et espaces retirés, ce qui permet à des textes légèrement différents de retrouver le même plan préparé.

Le moteur ne tente le paramétrage simple que sur une petite classe de requêtes. L'annexe A du livre blanc de Greg Low liste ce qui l'empêche : plusieurs tables dans le `FROM`, `IN`, `UNION`, `TOP`, `DISTINCT`, `GROUP BY`, une sous-requête, une expression de table commune, des prédicats liés par `OR`, un indicateur de requête, plus de 1 000 paramètres. Une jointure n'est donc jamais paramétrée en mode simple. Et même quand la forme le permet, la tentative n'aboutit que si elle est jugée sûre (*safe*).
Paul White a montré en 2022, dans sa série *Simple Parameterization and Trivial Plans*, que le critère est l'étape de plan trivial de l'optimiseur : la tentative est sûre si l'instruction préparée obtient un plan trivial, c'est-à-dire un plan qui ne dépend pas de la valeur.
Sinon, seule la _shell query_ reste en cache, sans plan préparé, et le plan affiché garde pourtant une liste de paramètres, ce qui trompe.
Dans `PachaDataFormation`, `InscriptionId = 42`, sur la clé primaire d'`Inscription.Inscription`, est paramétré ; `ContactId = 42` sur la même table, dont le plan n'est pas trivial (voir « Les plans triviaux »), reste `Adhoc`, comme je l'ai mesuré dans l'article sur les listes `IN`.

Les compteurs `Auto-Param Attempts/sec`, `Safe Auto-Params/sec`, `Unsafe Auto-Params/sec` et `Failed Auto-Params/sec` de l'objet `SQLServer:SQL Statistics` comptent ces tentatives.

### Le paramétrage forcé

Le paramétrage forcé (*forced parameterization*), apparu avec SQL Server 2005, s'active par base :

```sql
ALTER DATABASE PachaDataFormation SET PARAMETERIZATION FORCED;
```

Le moteur paramètre alors presque tous les littéraux, sans tenir compte du plan trivial, sauf exceptions que liste le guide d'architecture : la liste du `SELECT`, `TOP`, le motif d'un `LIKE`, le style de `CONVERT`, les arguments d'indicateurs, les corps de modules, les instructions déjà paramétrées par le client, les requêtes de plus de 2 097 littéraux, celles qui ont `OPTION (RECOMPILE)`. Les chaînes deviennent des `varchar(8000)` ou `nvarchar(4000)`, et des `varchar(max)` ou `nvarchar(max)` au-delà, les entiers des `int`, et un entier hors des limites de `int`, dans un prédicat de comparaison, un `numeric(38,0)`. Changer l'option vide les plans de la base, sauf ceux en cours d'exécution. J'en ai décrit la frontière dans [Le Query Store saturé par des identifiants concaténés](https://www.pachadata.com/docs/articles/sqlserver/performances/query-store-identifiants-concatenes/) : un `sp_executesql` qui a une liste de paramètres, même inutilisée, n'est pas repris par le paramétrage forcé.

> Type du littéral :
>
> Pourquoi le paramétrage déduit-il le type du littéral plutôt que de reprendre celui de la colonne ? Le choix commence dans le parseur, avant que les noms des tables et des colonnes soient résolus dans les métadonnées. À ce stade, le moteur connaît le texte de l'instruction, mais pas encore le type de la colonne comparée. Il reconnaît un littéral entier, puis, dans le contexte d'une comparaison directe, réduit son type au plus petit entier capable de contenir sa valeur. Paul White décrit cet ordre dans [*Simple Parameterization and Trivial Plans, Part 4*, sections « Statement Parsing » et « Initial Data Types »](https://sqlperformance.com/2022/05/sql-optimizer/simple-parameterization-and-trivial-plans-part-4), publié le 20 mai 2022.
> 
> Même pour une colonne `Id` de type `int`, le paramétrage simple peut donc produire :
> 
> | Comparaison | Type du paramètre inféré |
> |---|---|
> | `Id = 42` | `tinyint` |
> | `Id = 4242` | `smallint` |
> | `Id = 424242` | `int` |
> 
> Ces signatures différentes peuvent donner trois plans `Prepared` distincts, même si leurs opérateurs d'exécution sont identiques. Paul White montre ce dédoublement dans [la partie 2, sections « Parameter Data Types » et « Data Type Inference »](https://sqlperformance.com/2022/03/sql-performance/simple-param-trivial-plans-2), publiée le 29 mars 2022. La même partie explique qu'un paramétrage explicite, avec un type déclaré correspondant à celui de la colonne, évite cette variation : un paramètre `@Id int` garde le même type pour les trois valeurs.
> 
> Reprendre le type de la colonne pour cette comparaison demanderait donc une autre organisation du processus de compilation. D'après la partie 4 de Paul White, ces règles d'inférence remontent à SQL Server 7.0 et sont conservées pour compatibilité. Il s'agit d'un choix historique d'implémentation. La réduction en `tinyint` ou `smallint` décrite ici concerne le paramétrage simple ; le paramétrage forcé applique les règles différentes indiquées dans le paragraphe précédent.


Le paramétrage forcé laisse toujours une _shell query_ `Adhoc` par texte d'origine. Il réduit le nombre de compilations complètes et la taille des plans, et laisse une entrée par texte.

### Les requêtes paramétrées par le client

`sp_executesql`, les instructions préparées par `sp_prepare` et les requêtes paramétrées des pilotes donnent un plan `Prepared` dont le texte commence par la liste des paramètres, `(@id int)SELECT ...`. Cette liste fait partie du texte haché : deux appels qui déclarent le même paramètre avec deux longueurs différentes ont deux plans.
Avec `SqlCommand.Parameters.AddWithValue` en .NET, le pilote déduit les caractéristiques du paramètre à partir de la valeur fournie. Pour une chaîne courte, il choisit `nvarchar` et, si la taille n'est pas fixée, reprend la longueur de la chaîne, comme l'indique la [documentation de `SqlParameter.Size`](https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlparameter.size). Il ne consulte pas la définition de la colonne pour choisir cette taille. Ainsi, `"Li"` donne `@nom nvarchar(2)` et `"Martin"` donne `@nom nvarchar(6)`. Le texte du `SELECT` est identique, mais les déclarations des paramètres diffèrent : ces appels peuvent donc donner deux plans `Prepared` distincts. Déclarer explicitement le type et la taille correspondant à la colonne stabilise cette signature, conformément aux recommandations de la [documentation de configuration des paramètres avec `Microsoft.Data.SqlClient`](https://learn.microsoft.com/en-us/sql/connect/ado-net/configure-parameters).

L'exemple C# suivant suppose une colonne `dbo.Client.Nom` de type `nvarchar(100)`. Il affiche les caractéristiques des paramètres avant toute exécution : aucune connexion SQL Server n'est nécessaire pour observer l'inférence.

```csharp
using System;
using System.Data;
using Microsoft.Data.SqlClient;

// Exemple : dbo.Client.Nom est une colonne nvarchar(100).
const string sql = "SELECT COUNT(*) FROM dbo.Client WHERE Nom = @nom;";

// AddWithValue déduit la taille de chaque valeur.
foreach (string nom in new[] { "Li", "Martin" })
{
    using var commande = new SqlCommand(sql);
    SqlParameter parametre = commande.Parameters.AddWithValue("@nom", nom);
    Console.WriteLine($"{nom} : {parametre.SqlDbType}({parametre.Size})");
}

// Le type et la taille correspondent maintenant à la colonne.
foreach (string nom in new[] { "Li", "Martin" })
{
    using var commande = new SqlCommand(sql);
    SqlParameter parametre = commande.Parameters.Add("@nom", SqlDbType.NVarChar, 100);
    parametre.Value = nom;
    Console.WriteLine($"{nom} : {parametre.SqlDbType}({parametre.Size})");
}
```

Sortie obtenue avec `Microsoft.Data.SqlClient` 7.1.1 :

```text
Li : NVarChar(2)
Martin : NVarChar(6)
Li : NVarChar(100)
Martin : NVarChar(100)
```

Dans la première boucle, les deux commandes seraient envoyées avec des tailles de paramètres différentes. Dans la seconde, les valeurs changent mais la déclaration reste `@nom nvarchar(100)`, ce qui permet de réutiliser le plan si les autres conditions de partage du cache sont réunies. Le nombre `100` vient ici de la définition de la colonne ; pour une colonne `varchar(100)`, il faudrait déclarer `SqlDbType.VarChar` avec la taille `100`.

### Les procédures stockées et les appels RPC

Le plan d'une procédure stockée est conservé dans `Object Plans`, et retrouvé par l'identifiant de la procédure. Shekar recommandait en 2007 d'appeler les procédures par un appel RPC du pilote plutôt que par un batch `EXEC dbo.Proc 1, 1000` : le batch lui-même est un texte ad hoc, avec ses littéraux.
Dans l'essai sur SQL Server 2025 CU10, le batch composé uniquement de l'appel `EXEC dbo.Proc ...` n'a pas été conservé en cache, avec la raison `ZeroCostPlan` ; le plan de la procédure, lui, l'a été. Ce batch d'appel se distingue d'une *shell query* de paramétrage simple, qui renvoie vers un plan `Prepared`. Cette mesure ne permet pas de dater un changement de comportement, ni de généraliser à tous les batchs contenant un `EXEC`.

> RPC :
>
> Un appel RPC (*Remote Procedure Call*) transmet au serveur le nom de la procédure et ses paramètres typés. Avec un batch texte `EXEC dbo.Proc 1, 1000`, SQL Server doit d'abord analyser l'instruction, reconnaître l'appel et identifier la procédure avant de rechercher son plan. Le RPC évite ce travail sur le batch d'appel, même lorsque ce batch n'aurait pas été conservé en cache. Le moteur doit toujours retrouver le plan de la procédure et, si nécessaire, le compiler.
> 
> La [documentation Microsoft sur les verrous de compilation, section « Stored procedure is invoked as a Language event »](https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-blocking-caused-compile-locks#stored-procedure-is-invoked-as-a-language-event), recommande toujours le RPC pour cette raison. Avec `Microsoft.Data.SqlClient`, on utilise `CommandType.StoredProcedure` et le nom de la procédure comme texte de commande. Voici un exemple pour une procédure `dbo.Proc` qui accepte deux paramètres `int`, `@id` et `@seuil`, avec une connexion `connexion` déjà ouverte :
> 
> ```csharp
> using var commande = new SqlCommand("dbo.Proc", connexion);
> commande.CommandType = CommandType.StoredProcedure;
> commande.Parameters.Add("@id", SqlDbType.Int).Value = 1;
> commande.Parameters.Add("@seuil", SqlDbType.Int).Value = 1000;
> commande.ExecuteNonQuery();
> ```
> 
> Le pilote construit l'appel RPC à partir de ces informations. Le bénéfice concerne le traitement de l'appel ; il ne garantit pas un plan unique pour toutes les options de session et ne supprime pas le *parameter sniffing*.


Le partage d'un plan entre valeurs a un prix : l'optimiseur peut utiliser les valeurs des paramètres connues lors de la compilation, souvent celles de la première exécution. C'est le *parameter sniffing*. Le plan obtenu peut être excellent pour une valeur rare et mauvais pour une valeur fréquente, ou l'inverse, lorsqu'il est réutilisé.

> Optimisation paramétrique :
>
> Cette sensibilité aux valeurs rejoint un problème plus général étudié au moins dès 1992 sous le nom d'optimisation paramétrique (*parametric query optimization*) : préparer plusieurs plans adaptés à différentes régions de l'espace des paramètres, puis choisir à l'exécution. Les références sont :
> 
> - Yannis E. Ioannidis, Raymond T. Ng, Kyuseok Shim et Timos K. Sellis, [*Parametric Query Optimization*](https://www.vldb.org/conf/1992/P103.PDF), actes de la 18e conférence VLDB, Vancouver, 1992, p. 103-114. Dans le résumé, p. 103, les auteurs recherchent plusieurs plans, « each one of which is optimal for a subset of all possible values of the run-time parameters ». Leur étude porte principalement sur le nombre de pages de buffer disponibles pour une requête, et ne décrit donc pas spécifiquement le *parameter sniffing* de SQL Server. Les mêmes auteurs en ont publié une version longue, [*Parametric query optimization*](https://www.vldb.org/journal/VLDBJ6/70060132.pdf), *The VLDB Journal*, vol. 6, no 2, 1997, p. 132-151, [DOI 10.1007/s007780050037](https://doi.org/10.1007/s007780050037).
> - Sumit Ganguly, [*Design and Analysis of Parametric Query Optimization Algorithms*](https://www.vldb.org/conf/1998/p228.pdf), actes de la 24e conférence VLDB, New York, 1998, p. 228-238. Le résumé, p. 228, décrit des plans candidats « each optimal for some region of the parameter space ». La section 1.1 inclut explicitement la sélectivité, la taille des tables et la mémoire disponible parmi les paramètres possibles.


SQL Server 2022 répond en partie à la sensibilité aux valeurs avec [*Parameter Sensitive Plan optimization*](https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization), qui permet plusieurs plans à variantes pour une même instruction paramétrée éligible, décrits plus loin.

## L'option optimize for ad hoc workloads

Une application qui envoie des milliers de requêtes ad hoc différentes, chacune exécutée une fois, remplit le cache de plans qui ne resserviront jamais. L'option d'instance `optimize for ad hoc workloads`, apparue avec SQL Server 2008, change ce qui est mis en cache à la première exécution d'un batch : un _stub_ (*compiled plan stub*) au lieu du plan complet. Le _stub_ contient la clé du cache et un pointeur vers le texte, sans plan. À la deuxième exécution du même texte, le moteur recompile et remplace le _stub_ par le plan complet. Depuis SQL Server 2019, le même réglage existe par base, `ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON`.

```sql
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
```

Mesuré sur 2025 CU10, pour une jointure ad hoc non paramétrable :

| Exécution | `cacheobjtype` | `size_in_bytes` | `usecounts` |
|---|---|---|---|
| 1 | `Compiled Plan Stub` | 456 | 1 |
| 2 | `Compiled Plan` | 65 536 | 1 |
| 3 | `Compiled Plan` | 65 536 | 2 |

Le _stub_ est une entrée de cache sans plan d'exécution. Il contient la clé du cache (les mêmes attributs qu'un plan complet : `set_options`, `dbid`, `user_id`, `objectid`), un renvoi vers le texte et les champs que le _store_ tient pour chaque entrée, dont son coût. `sys.dm_exec_query_plan` ne rend rien pour lui. Contrairement à un plan, il n'a pas d'objet mémoire à lui : il est alloué dans un objet `MEMOBJ_CACHESTORESQLCP` de 48 Ko qui appartient au _store_ et que plusieurs _stubs_ partagent, ce qui explique sa taille qui n'est pas un multiple de 8 Ko, et le `memory_object_address` commun à plusieurs _stubs_.

À la première exécution, le moteur ne fait pas l'économie de la compilation : il compile le batch entier, l'exécute avec ce plan, puis libère le plan et ne garde que le _stub_. L'option économise de la mémoire, et coûte du processeur. À la deuxième exécution du même texte sous la même clé, la recherche trouve le _stub_, et le moteur compile de nouveau, cette fois pour garder le plan. Une requête exécutée deux fois est donc compilée deux fois.

Les compteurs le montrent, relevés autour de trois exécutions de la même jointure :

| Exécution | Entrée | Optimisations | Événements |
|---|---|---|---|
| 1 | `Compiled Plan Stub`, 456 octets | 1 | `plan_not_cached` (raison `SQLStr`), aucun `sp_cache_insert` |
| 2 | `Compiled Plan`, 73 728 octets, autre `plan_handle` | 1 | `sp_cache_insert` |
| 3 | même plan, `usecounts` 2 | 0 | `sp_cache_hit` |

Le compteur `SQL Re-Compilations/sec` ne bouge pas : remplacer un _stub_ par un plan n'est pas une recompilation. La première exécution laisse une ligne dans `sys.dm_exec_query_stats`, rattachée au `plan_handle` du _stub_. Quand le plan complet arrive, sous un autre `plan_handle`, sa ligne repart à une exécution : la statistique de la première exécution est perdue avec le _stub_.

La taille du _stub_ ne dépend pas de la longueur du texte : 456 octets pour un texte de 182 comme de 1 424 octets, parce que le texte est dans le cache SQL Manager. Le _stub_ de la _shell query_ d'une requête paramétrée en mode simple ne fait que 136 octets ; le plan préparé, lui, est complet dès la première exécution, l'option ne le concerne pas. Enfin, `usecounts` repart à 1 quand le plan remplace le _stub_ : il ne compte pas l'exécution qui avait créé le _stub_. La taille des _stubs_ a varié selon les versions : environ 300 octets d'après Delaney pour 2012, 424 dans *SQL Server Execution Plans* (2018), 456 dans *SQL Server 2025 Query Performance Tuning* de Grant Fritchey et dans mes mesures sur 2019 et 2025, sous Linux et sous Windows.

L'option ne s'applique qu'aux plans compilés après son activation, ne vide pas le cache, et ne concerne pas les plans `Prepared` de `sp_executesql` : un identifiant concaténé dans une requête paramétrée par le client donne un plan complet de 48 Ko à chaque valeur, option ou non. La page de l'option dit que les _stubs_ se trouvent en cherchant « Compiled Plan » dans `cacheobjtype` ; c'est `Compiled Plan Stub`. Elle propose aussi, pour l'appliquer aux plans déjà en cache, `ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE`, qui ne vide qu'une base alors que l'option vaut pour l'instance. Ces deux points font l’objet de la [PR #10501](https://github.com/MicrosoftDocs/sql-docs/pull/10501).


> Note :
>
> Un *stub* reste une entrée dans la table de hachage de `SQL Plans`, avec son propre `bucketid`. Mes essais sur SQL Server 2019, 2022 et 2025 le confirment : le *stub* de 456 octets et le plan complet qui le remplace sont dans le même bucket. L'option réduit la mémoire allouée par entrée, mais les *stubs* comptent toujours dans le nombre d'entrées du store.
> 
> Les 40 009 correspondent au nombre de buckets, chacun pouvant contenir plusieurs entrées. Sans l'indicateur de trace 174, le seuil est de 160 036 entrées, soit quatre fois le nombre de buckets, d'après [KB3026083](https://support.microsoft.com/fr-fr/servicing/sql/performance-query/kb3026083-fix-sos-cachestore-spinlock-contention-on-ad-hoc-sql-server-plan-cache-causes-high-cpu-usa). Une charge produisant beaucoup de textes distincts peut donc atteindre ce seuil même avec des *stubs* : `optimize for ad hoc workloads` réduit la pression liée à la taille des plans, sans supprimer celle liée au nombre d'entrées.


La [documentation Microsoft de l'option, section « Recommendations »](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option#recommendations), précise qu'on ne peut pas consulter le plan d'exécution à partir du cache lorsqu'il ne contient que le *stub*. Elle recommande l'option pour les serveurs OLTP où les plans ad hoc à usage unique occupent une part importante de la mémoire du moteur.

Erland Sommarskog recommande plus largement la valeur `1` dans [*Slow in the Application, Fast in SSMS?*, section « Auto-parameterisation »](https://www.sommarskog.se/query-plan-mysteries.html), pour limiter la mémoire consommée par les applications qui ne paramètrent pas leurs instructions. Il écrit : « There are no known downsides with setting the option to 1. »
Je suis d'accord avec lui, j'ai tendance à mettre cette option à `1` sur tous les serveurs.

## Les plans non mis en cache

Certains plans sont compilés sans être mis en cache. L'événement étendu `plan_not_cached` en donne la raison, parmi 70 valeurs sur 2025 (map `plan_not_cached_reason_enum`, à lire dans `sys.dm_xe_map_values`). J'ai suivi `sp_cache_insert` et `plan_not_cached` sur une série de batchs :

| batch | Mis en cache | Raison de `plan_not_cached` |
|---|---|---|
| jointure avec `OPTION (RECOMPILE)` | non | `SensInfo`, puis `WithRecompile` |
| procédure créée `WITH RECOMPILE` | non | le batch `EXEC` : `ZeroCostPlan` ; la procédure : `WithRecompile` (état 3) |
| `EXEC dbo.Proc 7 WITH RECOMPILE` | non | le batch : `ZeroCostPlan` |
| `SELECT 12 AS x` | non | `ZeroCostPlan` |
| `SELECT v FROM dbo.T WHERE 1 = 0` | oui, 40 960 octets | aucune |
| `SELECT TOP (0) v FROM dbo.T` | oui, 40 960 octets | aucune |
| littéral de 7 000 caractères | oui, 81 920 octets | aucune |
| littéral de 9 000 caractères | non | `ObjWithBlob` |
| batch avec `CREATE TABLE #t`, `INSERT`, `SELECT`, `DROP` | oui, 131 072 octets | deux `WithRecompile` |
| `SELECT ... INTO dbo.copie` | oui, 73 728 octets | aucune |
| `DROP TABLE dbo.copie` | non | `ZeroCostPlan` |

`OPTION (RECOMPILE)` sur une requête ad hoc ne laisse aucune entrée. Dans une procédure, c'est différent : le plan de la procédure reste en cache, et l'instruction recompilée reste visible dans `sys.dm_exec_query_stats` avec sa dernière exécution, ce que j'avais mesuré pour l'article sur la recompilation.

Le « coût nul » des sources a un sens précis. Selon mes observations, il ne s'agit pas d'un plan dont le coût est évalué à zéro. Une requête dont le plan estimé coûte zéro mais qui nomme une table, `WHERE 1 = 0` ou `TOP (0)`, est mise en cache.
La raison `ZeroCostPlan` vise les batchs dont la compilation ne touche aucun objet : `SELECT 12`, un `DROP`, le batch `EXEC proc`. C'est ce que j'ai observé avec les cas ci-dessus.

> SensInfo et SQLStr :
>
> Selon mes observations, les noms de `SensInfo` et de `SQLStr` sont trompeurs. `SensInfo` évoque une information sensible (*sensitive information*), mais apparaît pour `OPTION (RECOMPILE)` sur une requête quelconque : une table utilisateur, une table système, `SELECT 1 AS x OPTION (RECOMPILE)` sans aucune table.
> 
> `SensInfo` est donnée une fois, au niveau du batch, quand le batch ne contient qu'une instruction et que cette instruction est exclue du cache : une requête `OPTION (RECOMPILE)` seule, mais aussi un `CREATE PROCEDURE`, un `ALTER TABLE` ou un `USE` seuls dans leur batch. L'instruction reçoit ensuite sa propre raison, `WithRecompile`. Dès que le batch contient une autre instruction, un `DECLARE` ou un second `SELECT`, il reçoit `ZeroCostPlan` à la place de `SensInfo`. Aucune source ne décrit cette raison ; la règle vient de ces essais.
> 
> `SQLStr`, de même, ne veut pas dire que le batch n'est pas en cache : elle apparaît pour un batch qui lit une table temporaire créée par un batch précédent, suivie dans la même milliseconde d'un `sp_cache_insert` du même batch, et pour le _stub_ de `optimize for ad hoc workloads`.
> 
> Les deux situations observées sur SQL Server 2025 CU10 se distinguent ainsi :
> 
> | Situation | Événements observés | Résultat dans le cache |
> |---|---|---|
> | Une requête lit une table temporaire créée par un batch précédent | `plan_not_cached` avec `SQLStr`, puis immédiatement `sp_cache_insert` | Un plan complet est conservé et réutilisé à l'exécution suivante |
> | Première exécution avec `optimize for ad hoc workloads` activé | `plan_not_cached` avec `SQLStr`, sans insertion du plan complet | Seul le *stub* est conservé |
> 
> La raison `SQLStr` ne suffit donc pas à déterminer l'état final du cache. Mes essais ne permettent pas d'identifier précisément l'objet ou l'étape interne qu'elle désigne.


La map contient 70 raisons sur 2025 CU10. Plusieurs nomment des produits qui partagent le code du moteur : `StretchOverViewOrTVF`, `DataTierOverViewOrTVF`, `PolarisSALSp`, `SynapseBlockingRules`, `TridentFrontEndInstance` (Fabric), `CopyInto`, `Cetas`. D'autres désignent des cas internes que leur nom suffit à comprendre : `ShowPlanOn`, `BulkInsert`, `DropStmt`, `LargeConst`, `LargeBatch`, `FilteredIndex`, `CEFeedbackHint`, `CursorIntQuery`.

Un littéral de plus de 8 000 octets empêche la mise en cache de tout le batch, d'après les livres blancs de 2005 et 2012, la longueur étant mesurée après le repli des constantes. Le littéral de 9 000 caractères a la raison `ObjWithBlob`, et non `LargeConst`, qui existe aussi dans la map. D'après Shekar et Paul White, un batch qui contient une instruction sensible (`CREATE LOGIN`, une clé, un certificat, `ALTER DATABASE`) n'est pas mis en cache, même si elle n'est pas exécutée. D'après les livres blancs, ne sont pas mis en cache non plus les plans des requêtes qui créent ou mettent à jour des statistiques, les contextes d'exécution d'un `BULK INSERT` et, depuis 2012, les plans compilés sous `SHOWPLAN_XML`, `NOEXEC` ou `PARSEONLY`, donc les plans estimés que demande SSMS. Sur 2025 CU10, `SHOWPLAN_XML` et `PARSEONLY` ne laissent rien, mais un batch compilé sous `NOEXEC` laisse une entrée, que l'exécution suivante réutilise.

L'événement `uncached_sql_batch_statistics` ne dit pas ce que son nom laisse croire. Il s'est déclenché pour des batchs mis en cache comme pour des batchs refusés (`RECOMPILE`, littéral de 9 000 caractères), mais pas pour les batchs de coût nul : il semble accompagner la première compilation d'un batch qui touche un objet.

## Le coût d'une entrée

Chaque entrée d'un _store_ a un coût, que l'éviction utilise pour choisir ce qu'elle retire. Il se lit dans `sys.dm_os_memory_cache_entries`, colonnes `original_cost` et `current_cost`, en joignant sur l'adresse de l'objet mémoire :

```sql
SELECT p.objtype, p.usecounts, p.size_in_bytes,
       e.original_cost, e.current_cost
FROM sys.dm_exec_cached_plans AS p
JOIN sys.dm_os_memory_cache_entries AS e
  ON e.memory_object_address = p.memory_object_address
WHERE p.cacheobjtype = N'Compiled Plan';
```

La jointure ne vaut que pour les plans complets : les _stubs_ partagent leur `memory_object_address`, et chacun rend plusieurs lignes. La colonne `entry_data` de `sys.dm_os_memory_cache_entries`, qui contient un fragment XML pour d'autres caches, est `NULL` pour SQL Plans et Object Plans.


Voici un exemple autonome avec deux tables temporaires. Le commentaire `exemple_cout_entree_cache` permet de retrouver uniquement le batch étudié. `GO 6`, reconnu par SSMS et `sqlcmd`, exécute six fois le même batch avant de lire son entrée dans le cache. Pour cette mesure, `optimize for ad hoc workloads` est désactivé et l'instance ne subit pas de pression mémoire.

```sql
-- Mesure avec optimize for ad hoc workloads = 0, sans pression mémoire.
USE tempdb;
GO
CREATE TABLE #CoutA (id int NOT NULL PRIMARY KEY, valeur int NOT NULL);
CREATE TABLE #CoutB (id int NOT NULL PRIMARY KEY, valeur int NOT NULL);
INSERT #CoutA VALUES (1, 10), (2, 20);
INSERT #CoutB VALUES (1, 100), (2, 200);
GO
SELECT SUM(a.valeur + b.valeur) AS total
FROM #CoutA AS a
JOIN #CoutB AS b ON b.id = a.id;
-- exemple_cout_entree_cache
GO 6

SELECT p.objtype, p.usecounts, p.size_in_bytes,
       e.original_cost, e.current_cost
FROM sys.dm_exec_cached_plans AS p
JOIN sys.dm_os_memory_cache_entries AS e
  ON e.memory_object_address = p.memory_object_address
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE p.cacheobjtype = N'Compiled Plan'
  AND t.text LIKE N'%-- exemple_cout_entree_cache%'
  AND t.text NOT LIKE N'%sys.dm_exec_cached_plans%';
GO
DROP TABLE #CoutA, #CoutB;
```

Résultat obtenu sur SQL Server 2025 CU10 (`17.0.5035.5`, Linux) après les six exécutions, chacune renvoyant `total = 330` :

| `objtype` | `usecounts` | `size_in_bytes` | `original_cost` | `current_cost` |
|---|---:|---:|---:|---:|
| `Adhoc` | 6 | 73 728 | 16 | 16 |

Le nombre `16` est ici le coût utilisé pour la conservation de l'entrée dans le cache. Il ne représente ni une durée en millisecondes, ni le coût estimé d'exécution affiché dans le plan. La taille du plan est une autre mesure, `size_in_bytes`. La section « La montée à chaque réutilisation » montre comment le coût de cache est passé de `0` à `16` au fil des exécutions.

### Les sources

Le guide d'architecture du traitement des requêtes décrit le coût ainsi : à l'insertion, le coût courant vaut le coût de compilation, zéro pour un plan ad hoc ; à chaque réutilisation, il est remis au coût de compilation, et celui d'un plan ad hoc augmente, sans dépasser le coût de compilation ; sous pression mémoire, le moteur diminue le coût des plans inutilisés et retire ceux qui atteignent zéro. Les autres sources ne s'accordent ni sur la mesure ni sur la baisse :

| Source | Coût de compilation | Plan ad hoc réutilisé | Baisse sous pression |
|---|---|---|---|
| Arun Marathe (2005), Greg Low (2008, 2012) | somme de *ticks*, au plus 31 : un pour deux E/S (19 au plus), un pour deux changements de contexte (8 au plus), un pour 16 pages de mémoire (4 au plus) | non précisé | un tick de moins à chaque accès après 50 % du buffer pool |
| Delaney (2012) | mêmes plafonds, un tick par E/S et par changement de contexte | plus 1 à chaque réutilisation | un tick de moins à chaque accès après 50 % du buffer pool ; divisé par deux sous pression mémoire |
| Korotkevitch (2014) | comme Delaney | plus 1 à chaque réutilisation | un de moins à chaque passage du lazy writer |
| Shekar (2007) | deux élevé à la somme des contributions, plafonds 19, 8 et 4 | remis au coût d'origine à la première réutilisation, coût maximal 4 | divisé par deux |
| Slava Oks (2005) | non précisé | non précisé | diminué chaque fois que l'aiguille passe sur une entrée inutilisée, retrait à 0 |

Plusieurs livres, de *Professional SQL Server 2005 Performance Tuning* (2008) à [*SQL Server Execution Plans*, 3e édition](https://www.red-gate.com/simple-talk/featured/sql-server-execution-plans-third-edition-by-grant-fritchey/) de Grant Fritchey (2018, p. 35, « Plan aging »), décrivent encore un « âge » égal au coût multiplié par le nombre d'utilisations et décrémenté par le lazy writer. C'est le modèle de SQL Server 2000, remplacé en 2005 par l'horloge.

Dans [*SQL Server 2025 Query Performance Tuning*, 7e édition](https://doi.org/10.1007/979-8-8688-1865-3_2) (Apress, 2026), Grant Fritchey décrit toujours un champ d'âge initialisé au coût de génération du plan, rétabli à ce coût lors des réutilisations, et examiné périodiquement par le lazy writer (chapitre 2, « Aging of the Execution Plan », p. 37-38). Il ne reprend pas la multiplication par le nombre d'utilisations. Le coût initial lié à la compilation ne correspond cependant pas aux valeurs de la DMV mesurées ci-dessous sur SQL Server 2025. Ce rapprochement demande de confirmer que le champ décrit par le livre et les colonnes de la DMV représentent bien le même coût interne.

### La montée à chaque réutilisation

Sur 2025 CU10, sans aucune pression mémoire, j'ai exécuté douze fois une jointure ad hoc et une procédure, en relevant les deux coûts après chaque exécution :

| Exécution | 1 | 2 | 3 | 4 | 5 | 6 | 7 à 12 |
|---|---|---|---|---|---|---|---|
| `original_cost` et `current_cost` | 0 | 1 | 2 | 4 | 8 | 16 | 16 |

La suite est la même pour un plan `Adhoc`, un plan `Prepared` de `sp_executesql` et un plan `Proc`, et la même pour un plan ad hoc de 288 Ko qui joint neuf tables : elle double et plafonne à 16, quelle que soit la taille du plan. `original_cost` grandit avec `current_cost` et lui reste égal. Sur `PachaDataFormation`, une jointure entre `Contact.Contact` et `Contact.Societe` exécutée sept fois était aussi à 16.

Dans les essais présentés ici, les colonnes `original_cost` et `current_cost` partent de zéro pour les plans `Adhoc`, `Prepared` et `Proc`, puis augmentent avec les réutilisations. La même suite apparaît pour des plans de tailles différentes. Les mêmes valeurs se retrouvent sous Windows, sur 2025 CU9.

Ce résultat diffère de la description du [guide Microsoft, section « Remove execution plans from the plan cache »](https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide#remove-execution-plans-from-the-plan-cache), qui réserve le coût initial nul aux plans ad hoc et décrit, pour les autres plans, un coût initial de compilation rétabli à chaque réutilisation. C'est un candidat à une correction de documentation. Avant de proposer une formule de remplacement, il reste à vérifier les versions antérieures et à confirmer la correspondance entre les coûts exposés par la DMV et le coût de compilation décrit dans le guide. Ces essais ne suffisent pas à établir une règle générale d'indépendance entre le coût et la taille d'un plan.

### La baisse sous pression

> Note :
>
> Une « aiguille » (*clock hand*) est un pointeur qui parcourt les entrées d'un cache store. À chaque passage, le moteur peut diminuer le coût d'une entrée inutilisée ou la retirer si son coût est déjà nul. Nous verrons plus loin comment cela fonctionne.


Pour voir le coût baisser, il faut que l'horloge tourne. J'ai placé dans un cache vide des jointures ad hoc exécutées une à sept fois, donc de coût 0, 1, 2, 4, 8 et 16, un plan préparé et une procédure de coût 16, puis j'ai inséré des milliers de batchs ad hoc distincts en relevant les coûts toutes les vingt insertions. Vers 540 Mo de SQL Plans, avec `max server memory` à 2 048 Mo, le moniteur de ressources a signalé une mémoire physique basse au niveau du processus (`RESOURCE_MEMPHYSICAL_LOW` dans `sys.dm_os_ring_buffers`), et l'aiguille externe a tourné sur tous les _stores_ à la fois :

| Tour de l'aiguille externe | M16 | M8 | M4 | M2 | M1 | M0 | Procédure à 16 |
|---|---|---|---|---|---|---|---|
| avant | 16 | 8 | 4 | 2 | 1 | 0 | 16 |
| 1 | 8 | 4 | 2 | 1 | 0 | retiré | 8 |
| 2 | 4 | 2 | 1 | 0 | retiré | | 4 |
| 3 | 2 | 1 | 0 | retiré | | | 2 |
| 4 | 1 | 0 | retiré | | | | 1 |
| 5 | 0 | retiré | | | | | 0 |
| 6 | retiré | | | | | | retiré |

Chaque passage de l'aiguille divise le coût courant par deux, en division entière, et retire l'entrée qu'il trouve à zéro. `original_cost` ne bouge pas pendant la baisse. Une entrée à 16 survit donc à cinq passages, une entrée à usage unique à aucun. Une procédure, dans Object Plans, suit la même suite. Une entrée en cours d'exécution n'est jamais retirée, même à coût 0 : une requête suivie a traversé soixante-dix tours de l'aiguille sans être retirée. Une réutilisation pendant la pression remet le coût à 16, qu'il soit tombé à 8 ou à 2 : l'entrée retrouve d'un coup toute sa marge. Dans un dernier essai où la pression a tout emporté en moins d'une seconde, l'événement étendu `query_cache_removal_statistics` a daté le retrait de chaque entrée suivie : M0, puis M1, M2, M4, M8, à deux millisecondes d'intervalle, puis toutes celles à 16 ensemble. Les entrées sortent dans l'ordre de leur coût.

C'est la division par deux de Shekar (2007), déjà décrite pour la pression externe par Delaney dans *Inside Microsoft SQL Server 2005*, et non le décrément d'un tick que décrivent Low et Korotkevitch.

### Le code du moteur

Le moteur a des symboles publics, et on peut voir ses fonctions au débogueur. Sous Linux, j'ai attaché `gdb` au processus de l'instance de test, lu les adresses des fonctions dans les fichiers de symboles (PDB) que Microsoft publie, et compté les appels pendant une vague de pression. Trois fonctions de `sqldk.dll`, la bibliothèque de SQLOS, expliquent ce qui précède :

- `CacheClockAlgorithm::CalculateCost` calcule le coût d'une entrée à partir des *ticks* de compilation et d'un terme lié au nombre de pages mémoire. Ce terme est calculé en divisant le nombre de pages par 16, avec une division entière, puis en plafonnant le résultat à 4 : 32 pages donnent 2, et 64 pages ou davantage donnent 4. Dans le code machine, la division par 16 est un décalage de quatre bits vers la droite. Ce sont ce que décrivent Marathe et Low ; je n'ai pas établi comment ils se combinent avec la suite 0, 1, 2, 4, 8, 16 mesurée à chaque réutilisation ;
- `TCacheStore::MoveInternal`, le pas de l'aiguille, lit pour chaque entrée un mot de 64 bits de sa structure d'horloge (`ClockInfo`). Elle compare la moitié basse à 1 et divise la moitié haute par deux (instruction `shr eax, 1`) avant de la réécrire par une écriture atomique (`lock cmpxchg`). Tout concorde avec une moitié haute qui serait le coût courant et une moitié basse qui marquerait l'entrée en cours d'utilisation, ce qui expliquerait la division par deux mesurée et le fait qu'une entrée en cours d'utilisation n'est jamais retirée ; lu dans le code, pas vérifié dans la mémoire d'une entrée ;
- la chaîne d'appel, lue sur les piles, part du moniteur de ressources : `ResourceMonitorTask`, `CheckIndicators`, `NotifyMemoryConsumers`, `TCacheStore::Notify`, `StartExternalPressure`, `CacheClockHand::Move`, puis `MoveInternal`. Pendant un passage qui a retiré 2 752 entrées de SQL Plans, `MoveInternal` a été appelée 460 fois : elle traite plusieurs entrées par appel.


> Note :
>
> Le passage de `MoveInternal` rapproche plusieurs techniques classiques de programmation système :
> 
> - Le vieillissement d'un compteur (*aging*) réduit progressivement le poids des utilisations anciennes. Décaler un entier non signé d'un bit vers la droite revient à le diviser par deux, en abandonnant le reste : `16`, `8`, `4`, `2`, `1`, `0`. Dans l'algorithme classique de remplacement de pages, on introduit aussi un bit d'utilisation récente à gauche du compteur. Le [cours de Redha Gouicem à RWTH Aachen, section « LRU Approximation With Aging »](https://teaching.os.rwth-aachen.de/BuS/BuS-lecture-full.html#page-replacement-lru-approximation-with-aging), décrit ce mécanisme. La règle de réutilisation observée dans SQL Server diffère de cette insertion d'un bit.
> - Le regroupement de plusieurs champs dans un seul entier permet de vérifier et de modifier ensemble un compteur et un état. PostgreSQL emploie cette technique dans son gestionnaire de buffers : [`buf_internals.h`](https://github.com/postgres/postgres/blob/REL_18_STABLE/src/include/storage/buf_internals.h) regroupe un compteur de références, un compteur d'usage et des indicateurs dans un mot de 32 bits. Le commentaire du code explique que ce regroupement permet certaines mises à jour atomiques par une boucle de comparaison et échange.
> - La comparaison et l'échange atomiques (*compare-and-swap*, CAS) permettent de remplacer une valeur seulement si elle correspond encore à la valeur attendue. Si un autre thread l'a modifiée entre la lecture et la tentative d'écriture, l'échange échoue ; une boucle peut alors relire la valeur et recommencer le calcul. Cela évite d'écraser une modification concurrente. Mara Bos explique cette technique et l'instruction x86 `lock cmpxchg` dans [*Rust Atomics and Locks*, chapitre 7, « x86 compare-and-exchange instruction »](https://mara.nl/atomics/hardware.html#x86-compare-and-exchange-instruction).
> 
> Ces références expliquent les techniques générales. Elles ne documentent pas `ClockInfo` : l'interprétation de ses deux moitiés comme coût courant et état d'utilisation reste une hypothèse issue de la lecture du code SQL Server.


Le retrait lui-même passe par la destruction du plan (`CCacheObject::Destroy`, puis le destructeur `~CCompPlan`), qui déclenche l'événement `query_cache_removal_statistics`. Il ne passe pas par la fonction qui déclenche `sp_cache_remove` : celle-ci n'est appelée que par les retraits explicites, `DBCC FREEPROCCACHE`, `DBCC FREESYSTEMCACHE`, les options de base. Une session d'événements sur `sp_cache_remove` ne voit donc aucune éviction : pendant un essai où `query_cache_removal_statistics` s'est déclenché 8 241 fois, elle n'a rien reçu. (`query_cache_removal_statistics` est aussi émis quand un plan non mis en cache est libéré en fin de batch).


> Plan cache PLE :
>
> SQL Server ne fournit pas de compteur natif documenté équivalent à *Page life expectancy* (PLE) pour le cache de plans. L'objet de performances [`SQLServer:Plan Cache`](https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-plan-cache-object) expose notamment le nombre d'objets, la taille et le taux de succès des recherches, mais aucune durée de résidence.
> 
> Les vues et les événements permettent néanmoins de mesurer plusieurs aspects :
> 
> | Information recherchée | Moyen disponible |
> |---|---|
> | Depuis quand un plan de procédure est en cache | `cached_time` dans [`sys.dm_exec_procedure_stats`](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-procedure-stats-transact-sql) |
> | Depuis quand une instruction a été compilée | `creation_time` dans [`sys.dm_exec_query_stats`](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-query-stats-transact-sql) ; une recompilation renouvelle cette date, qui ne mesure donc pas nécessairement la durée de résidence de l'entrée |
> | Quand un plan disparaît | Capturer les retraits, notamment avec `query_cache_removal_statistics`, et les rapprocher d'un suivi préalable des plans, en tenant compte des déclenchements pour les plans non mis en cache décrits ci-dessus |
> 
> Pour construire un indicateur général, on peut relever périodiquement les `plan_handle`, avec leur première et leur dernière observation. On estime ainsi la durée de résidence des plans observés, en séparant par exemple les plans à usage unique des plans réutilisés. Les plans déjà présents au début du suivi ont un âge initial inconnu ; ceux encore présents à la fin n'ont pas terminé leur séjour. Les plans créés puis retirés entre deux relevés échappent entièrement à cette mesure.
> 
> La moyenne de l'âge des seuls plans actuellement présents serait trompeuse : elle ignore ceux rapidement évincés. Quelques vieux plans peuvent ainsi masquer un renouvellement important du cache. Le coût courant décrit dans cette section n'est pas davantage une durée en secondes : il détermine la résistance d'une entrée aux passages des aiguilles.


### Les conséquences

En pratique, un plan devient plus difficile à retirer à chaque réutilisation, jusqu'à la cinquième, et un plan à usage unique est à zéro, donc retiré au premier passage de l'aiguille. Le coût ne dit pas combien le plan a coûté à compiler, mais combien de passages il peut encore traverser, sur une échelle de puissances de deux. Mes essais sur 2019 vont dans ce sens : des plans appelés toutes les 20 secondes ont survécu à près de 78 000 évictions.

Comme nous l'avons vu dans un blog précédent, ce schéma a un nom dans la littérature des systèmes d'exploitation : le vieillissement (*aging*), décrit par Andrew Tanenbaum pour le remplacement de pages. Chaque page a un compteur ; à chaque tick d'horloge, le compteur est décalé d'un bit vers la droite, ce qui le divise par deux, et le bit d'utilisation de la page est ajouté à gauche. Une page utilisée récemment et souvent a un grand compteur ; une page oubliée voit le sien fondre par moitiés. SQL Server fait la même chose, avec deux différences : le coût double à chaque réutilisation au lieu de recevoir un bit, et la division n'a lieu que quand une aiguille passe, c'est-à-dire sous pression.

## L'éviction

Le cadre des caches de SQLOS retire les entrées par un algorithme d'horloge. Chaque _store_ a deux aiguilles (*clock hands*), visibles dans la [vue `sys.dm_os_memory_cache_clock_hands`](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-os-memory-cache-clock-hands-transact-sql) :

- l'aiguille interne (`HAND_INTERNAL`) tourne quand le _store_ lui-même dépasse une de ses limites, nombre d'entrées ou taille ; elle est déplacée par les threads de travail qui insèrent dans le cache, ce qui allonge leur requête ;
- l'aiguille externe (`HAND_EXTERNAL`) tourne quand toute l'instance manque de mémoire ; elle est déplacée par le Resource Monitor.

Le [guide Microsoft](https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide#remove-execution-plans-from-the-plan-cache) distingue la pression globale, traitée par le Resource Monitor, de la pression propre à un cache, traitée par les threads de travail.

À chaque passage sur une entrée inutilisée, l'aiguille divise son coût courant par deux ; une entrée à zéro est retirée (voir « La baisse sous pression »). Une entrée en cours d'utilisation n'est jamais retirée, sauf à la marquer par `DBCC FREESYSTEMCACHE ... WITH MARK_IN_USE_FOR_REMOVAL`, qui la retire dès qu'elle se libère. D'après Delaney, un tour met à jour 16 entrées au plus par _store_ ; Shekar ajoute que, sous pression locale, ce nombre double à chaque mouvement de l'aiguille jusqu'à 1 024.

```sql
SELECT c.name, h.clock_hand, h.clock_status, h.rounds_count,
       h.removed_all_rounds_count, h.removed_last_round_count
FROM sys.dm_os_memory_cache_clock_hands AS h
JOIN sys.dm_os_memory_cache_counters AS c
  ON c.cache_address = h.cache_address
WHERE c.type IN (N'CACHESTORE_SQLCP', N'CACHESTORE_OBJCP');
```

Les essais montrent dans quelles situations chaque aiguille intervient :

- Sur SQL Server 2019, SQL Plans a atteint son plafond d'entrées. L'aiguille interne a retiré 39 975 entrées, d'après `removed_all_rounds_count`. Le compteur de l'aiguille externe est resté à zéro : seul SQL Plans avait besoin de libérer de la place.
- Sur SQL Server 2025 sous Windows, SQL Plans a atteint sa limite de taille (voir « Les limites d'un _store_ »). L'aiguille interne a divisé les coûts par deux, comme l'externe : `16`, `8`, `4`, `2`, `1` en cinq tours.
- Les deux aiguilles modifient le même coût pour une entrée donnée. Dans cet essai Windows, un passage de l'aiguille externe a fait baisser le coût de `1` à `0`. L'aiguille interne a ensuite retiré l'entrée.
- L'aiguille interne agit uniquement sur son store. Pendant les cinq tours de celle de SQL Plans, une procédure conservée dans Object Plans est restée au coût de `16`.
- Sur SQL Server 2025 dans un conteneur Linux limité à 3 Go, le résultat était différent. Avec `max server memory` à 2 048, 1 500 ou 800 Mo, la croissance de SQL Plans a déclenché une pression mémoire du processus (`RESOURCE_MEMPHYSICAL_LOW`) avant que ce store atteigne sa propre limite. L'aiguille externe a alors parcouru tous les stores, tandis que l'interne n'a effectué aucun tour.

Les vagues de pression n'ont pas toutes la même intensité. Dans mes essais, une vague pouvait provoquer un ou deux passages, puis la mémoire se stabilisait. La suivante pouvait en provoquer sept en moins d'une seconde. La mémoire cible est ainsi descendue de 1,7 Go à 500 Mo avant de remonter.

> Littérature :
>
> Le mécanisme observé dans SQL Server peut être rapproché de plusieurs algorithmes de remplacement de pages et de gestion de caches.
> 
> #### L'horloge et GCLOCK
> 
> L'algorithme d'horloge parcourt les entrées dans un ordre circulaire. Un bit indique si chacune a été utilisée récemment : le parcours donne une seconde chance aux entrées utilisées et peut retirer les autres. Ce mécanisme approche le comportement de LRU (*Least Recently Used*) sans devoir réordonner une liste à chaque accès. Fernando J. Corbató le décrit pour Multics dans [*A Paging Experiment with the Multics System*](https://multicians.org/paging-experiment.pdf), publié en 1969, p. 217-228.
> 
> GCLOCK généralise cette idée en remplaçant le bit par un compteur. Dans une forme courante, chaque passage diminue le compteur de un, jusqu'à permettre le retrait de l'entrée. Victor F. Nicola, Asit Dan et Daniel M. Dias analysent cette famille dans [*Analysis of the Generalized Clock Buffer Replacement Scheme for Database Transaction Processing*](https://doi.org/10.1145/133057.133084), SIGMETRICS, 1992.
> 
> #### Le vieillissement
> 
> Diviser un compteur par deux fait progressivement perdre du poids aux utilisations anciennes. C'est le principe du vieillissement (*aging*) décrit par Andrew S. Tanenbaum et Herbert Bos dans [*Modern Operating Systems*](https://www.pearson.com/en-us/pearsonplus/p/9780137618880.html), 5e édition, chapitre 3. Le [cours de Redha Gouicem à RWTH Aachen](https://teaching.os.rwth-aachen.de/BuS/BuS-lecture-full.html#page-replacement-lru-approximation-with-aging) explique le décalage du compteur vers la droite et l'ajout d'un bit d'utilisation récente à gauche.
> 
> Dans mes essais SQL Server, on retrouve la division par deux au passage d'une aiguille. La mise à jour lors d'une réutilisation suit toutefois une autre règle que l'ajout de ce bit, comme le montre la suite mesurée plus haut.
> 
> #### Le coût de rechargement
> 
> Les entrées d'un cache ne coûtent pas toutes aussi cher à recréer. GreedyDual prend en compte le coût des défauts de cache : Neal E. Young le présente dans [*On-Line Caching as Cache Size Varies*](https://www.cs.ucr.edu/~neal/publication/Young91Online.pdf), SODA, 1991. Pei Cao et Sandy Irani étendent cette approche aux caches web avec GreedyDual-Size dans [*Cost-Aware WWW Proxy Caching Algorithms*](https://www.usenix.org/conference/usits-97/cost-aware-www-proxy-caching-algorithms), USITS, 1997, p. 193-206, en tenant aussi compte de la taille des objets.
> 
> Cette question se pose également pour les plans : un retrait oblige à compiler à nouveau lors d'un prochain appel, et le coût de compilation varie selon la requête. Cette analogie ne suffit cependant pas à déduire la formule utilisée par SQL Server.
> 
> #### Le rapprochement avec SQL Server
> 
> Les observations réunissent plusieurs caractéristiques :
> 
> - un parcours circulaire des entrées, comme dans les algorithmes d'horloge ;
> - un compteur qui diminue par division entière par deux, comme dans le vieillissement ;
> - un coût qui remonte lors des réutilisations, ce qui donne davantage de chances aux entrées réutilisées de rester en cache.
> 
> Ces rapprochements sont mon interprétation des mesures et du code observé. Ils ne permettent pas d'affirmer que SQL Server implémente GCLOCK, le vieillissement classique ou GreedyDual à l'identique. De même, le terme « LRU » employé par [Slava Oks](https://learn.microsoft.com/en-us/archive/blogs/slavao/sqlos-caching) et [Sangeetha Shekar](https://techcommunity.microsoft.com/blog/sqlserver/7-0-costing-cache-entries/383210) désigne ici une politique approchée : il ne faut pas en déduire que le moteur retire systématiquement le plan dont la dernière utilisation est la plus ancienne.


## Les limites d'un _store_

Un _store_ a deux limites, qui déclenchent toutes deux l'aiguille interne. J'en ai fait le détail dans l'article sur le plafond d'entrées ; en voici l'essentiel.

La première est un nombre d'entrées : quatre fois le nombre de buckets de la table de hachage, soit 160 036 entrées pour SQL Plans et pour Object Plans, chacun séparément. C'est la valeur que donne [KB3026083](https://support.microsoft.com/fr-fr/servicing/sql/performance-query/kb3026083-fix-sos-cachestore-spinlock-contention-on-ad-hoc-sql-server-plan-cache-causes-high-cpu-usa). Lorsque le nombre d'entrées atteint ce seuil, le moteur commence à en retirer pour libérer de la place. De nouvelles entrées peuvent encore arriver pendant ce nettoyage, si bien que le nombre observé peut dépasser légèrement le seuil : sur SQL Server 2019, le _store_ oscillait entre 160 025 et 160 041 entrées. Les _stubs_ de `optimize for ad hoc workloads` comptent comme des entrées, si bien qu'une charge ad hoc peut atteindre le plafond avec quelques centaines de mégaoctets. L'indicateur de trace 174, au démarrage, fait passer le plafond à 640 004 entrées (160 001 buckets), depuis 2012 SP1 CU14, 2012 SP2 CU5, 2014 RTM CU6 et 2014 SP1 CU1. La KB l'a introduit pour une contention sur le spinlock `SOS_CACHESTORE`, qui apparaît quand beaucoup de sessions insèrent des plans dans un _store_ plein, ou en même temps dans le même bucket.

> Load factor :
>
> Le rapport entre le nombre d'entrées et le nombre de buckets s'appelle le facteur de charge (*load factor*), noté `α`. Le [NIST](https://xlinux.nist.gov/dads/HTML/loadfactor.html) le définit ainsi : `α = nombre d'entrées / nombre de buckets`.
> 
> Le seuil de SQL Server correspond à un facteur de charge de `4` : 160 036 entrées pour 40 009 buckets, soit quatre entrées par bucket en moyenne. Ce rapport concerne l'ensemble du store : certains buckets peuvent contenir davantage d'entrées, tandis que d'autres en contiennent moins.
> 
> Pour une table avec chaînage et une bonne répartition des clés, le coût moyen d'une recherche est de l'ordre de `O(1 + α)`. Borner le facteur de charge permet de garder des recherches courtes lorsque le nombre total d'entrées augmente. Robert Sedgewick et Kevin Wayne proposent justement comme dimensionnement courant `M ≈ N / 4`, avec `M` buckets pour `N` entrées, dans leurs [supports d'*Algorithms*, section 3.4, diapositive 20](https://www.cs.princeton.edu/courses/archive/spring14/cos226/lectures/34HashTables%2B35SearchingApplications-2x2.pdf#page=5). Ce choix correspond lui aussi à quatre entrées par bucket en moyenne.
> 
> D'autres implémentations retiennent un seuil différent. Dans [le code de Tcl, `tclHash.c`](https://core.tcl-lang.org/tcl/file?ci=tip&name=generic/tclHash.c), `REBUILD_MULTIPLIER = 3` déclenche l'agrandissement de la table à environ trois entrées par bucket. La théorie explique l'intérêt de limiter le facteur de charge ; le seuil précis relève d'un compromis entre :
> 
> - le temps nécessaire pour parcourir les entrées d'un bucket ;
> - la mémoire des structures d'une table comportant davantage de buckets ;
> - les coûts de synchronisation entre threads lors des accès concurrents.
> 
> Dans SQL Server, [KB3026083](https://support.microsoft.com/fr-fr/servicing/sql/performance-query/kb3026083-fix-sos-cachestore-spinlock-contention-on-ad-hoc-sql-server-plan-cache-causes-high-cpu-usa) documente la contention sur `SOS_CACHESTORE` et l'augmentation du nombre de buckets avec l'indicateur 174. Le rapport de quatre entrées par bucket est conservé.
> 
> Je n'ai trouvé aucune source Microsoft expliquant le choix précis de `4`. Un compromis ajusté par des mesures paraît plausible, mais son origine empirique reste une hypothèse. La recommandation de Sedgewick et Wayne montre que cette valeur apparaît ailleurs ; elle n'établit pas l'origine du choix de Microsoft.


La seconde limite est une taille. La limite de pression du cache de plans dépend de la mémoire cible visible :

| Version | Limite de pression |
|---|---|
| SQL Server 2000 | 4 Go |
| 2005 RTM et SP1 | 75 % de 0 à 8 Go, plus 50 % de 8 à 64 Go, plus 25 % au-delà |
| 2005 SP2 et suivantes | 75 % de 0 à 4 Go, plus 10 % de 4 à 64 Go, plus 5 % au-delà |
| avec l'indicateur de trace 8032 | retour à la formule de 2005 RTM |

Un _store_ entre en pression locale à 62,5 % de cette limite, d'après Delaney et Korotkevitch, la seule source de ce chiffre. Pour une mémoire cible de 5 185 Mo, la limite d'un _store_ vaut ainsi (3 072 + 109) × 62,5 % = 1 988 Mo, et j'ai mesuré un palier à 1 972 Mo sur 2019. Pour une mémoire cible de 128 Go, la limite d'un _store_ est d'environ 7,6 Go : au-delà de 4 Go, chaque Go de mémoire cible ajouté n'agrandit la limite d'un _store_ que de 64 Mo, et de 32 Mo au-delà de 64 Go.

La formule n'a pas été republiée par Microsoft depuis le livre blanc de 2012, et je ne l'ai vérifiée qu'une fois, à 5 Go, sur 2019. Sur 2025, le conteneur Linux ne permet pas de la voir : la pression externe arrive toujours avant (voir « L'éviction »), et la mémoire cible y est plafonnée par la couche d'abstraction de SQL Server sous Linux (SQLPAL), à 1 735 Mo pour un `max server memory` de 2 048 Mo.

Sous Windows, la limite se voit. Sur une instance 2025 CU9 dans une machine virtuelle de 4 Go, où la mémoire cible est égale à `max server memory`, j'ai rempli SQL Plans de batchs ad hoc distincts jusqu'à ce qu'une aiguille tourne. À 1 280 et à 1 536 Mo, c'est l'aiguille interne qui a tourné, sans aucune notification du moniteur de ressources, et SQL Plans a plafonné ainsi :

| Mémoire cible | Formule : 75 % × 62,5 % | Palier mesuré de SQL Plans | Palier / formule |
|---|---|---|---|
| 1 280 Mo | 614 400 Ko | 583 648 Ko | 95,0 % |
| 1 536 Mo | 737 280 Ko | 700 300 à 700 500 Ko (trois passes) | 95,0 % |

La forme de la formule tient, proportionnelle à la mémoire cible au millième près, mais le palier se trouve à 95 % de la valeur qu'elle annonce, soit 44,5 % de la mémoire cible.

Deux lectures sont possibles : une marque basse, où l'aiguille commence à retirer un peu avant la limite pour ne pas l'atteindre, ou un seuil de 19/32 au lieu de 20/32 (62,5 %). Au palier, l'aiguille retire un plan par plan inséré, et un tour complet retire environ 10 400 entrées, tout le _store_. À 1 024 Mo, en revanche, la pression externe est arrivée la première, vers 430 Mo de SQL Plans, comme sous Linux. L'indicateur de trace 8032 ne se met qu'au démarrage (`DBCC TRACEON (8032, -1)` est refusé à chaud), et sous 4 Go de mémoire cible les deux formules donnent toutes deux 75 % : avec `-T8032`, le palier n'a pas bougé, ce qui ne dit rien de son effet au-delà de 4 Go.

Le changement de 2005 SP2 a une histoire : en 64 bits, sous 2005 RTM ou SP1, les plans à usage unique pouvaient occuper la moitié de la mémoire du serveur, d'après Jonathan Kehayias et Ted Krueger ([*Troubleshooting SQL Server: A Guide for the Accidental DBA*](https://www.red-gate.com/simple-talk/resources/books/simple-talk-published-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/), Redgate, 2011, chapitre 4, section « Significantly larger procedure cache », p. 142). La [documentation de l'indicateur 8032](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql#tf8032) avertit que son retour à l'ancienne formule peut nuire aux autres consommateurs de mémoire.

## La mémoire du cache

Les vues et le compteur de performances ne mesurent pas la même chose. Après 2 000 batchs ad hoc distincts et trois procédures, sur 2025 CU10 :

| Source | SQL Plans | Object Plans | Bound Trees |
|---|---|---|---|
| `sys.dm_exec_cached_plans`, somme de `size_in_bytes` | 121 240 Ko | 376 Ko | 4 928 Ko |
| `sys.dm_os_memory_clerks`, `pages_kb` | 128 712 Ko | 384 Ko | 5 104 Ko |
| `sys.dm_os_memory_cache_counters`, `pages_kb` | 128 784 Ko | 384 Ko | 5 104 Ko |
| `sys.dm_os_memory_cache_entries`, somme de `pages_kb` | 48 536 Ko | 200 Ko | 4 928 Ko |
| compteur `Cache Pages`, en pages de 8 Ko | 82 776 Ko | 8 Ko | 360 Ko |

Le clerk de mémoire et `sys.dm_os_memory_cache_counters` concordent, et dépassent d'environ 6 % la somme des `size_in_bytes`. L'écart se retrouve exactement. Dans une seconde mesure, après 2 000 batchs ad hoc, le clerk de SQL Plans valait 137 696 Ko, et la somme de trois termes donnait 137 672 Ko : 130 192 Ko de `size_in_bytes`, 4 976 Ko d'objets `MEMOBJ_CACHESTORESQLCP` (la mémoire propre du _store_, où vivent les _stubs_ et les structures des entrées) et 2 504 Ko d'objets `MEMOBJ_SQLMGR`, le cache SQL Manager qui garde les textes.

Dans ces mesures, la somme des `pages_kb` de `sys.dm_os_memory_cache_entries` représentait seulement 36 à 40 % de la mémoire comptabilisée par le clerk. Ce champ décrit la taille de l'entrée lors de son insertion dans le cache. Dans mes essais, il conservait cette valeur même lorsque la mémoire utilisée augmentait ensuite.

Une requête préparée avec `sp_prepare` permet de voir la différence :

| Moment du relevé | `pages_kb` (Ko) | `size_in_bytes` (octets) |
|---|---:|---:|
| Après la préparation, avant toute exécution | 48 | 57 344 |
| Après la première exécution | 48 | 139 264 |

La première exécution a construit le contexte d'exécution : la mémoire supplémentaire apparaît dans `size_in_bytes`, tandis que `pages_kb` reste à 48. Dans la mesure du store, les contextes d'exécution représentaient environ la moitié de la mémoire totale. Cela explique une grande partie de l'écart.

Pour choisir la mesure adaptée :

- `sys.dm_os_memory_cache_entries.pages_kb` donne la taille enregistrée à l'insertion, utilisée par SQLOS pour gérer l'entrée ;
- `sys.dm_exec_cached_plans.size_in_bytes` donne la mémoire du plan et des contextes d'exécution conservés avec lui, y compris leur croissance après l'insertion ;
- le clerk ou `sys.dm_os_memory_cache_counters` donne la mémoire de l'ensemble du store, avec ses structures de gestion.

Les compteurs de performances peuvent aussi être décalés dans le temps. Dans ces essais, `Cache Pages` et `Cache Object Counts` avaient quelques secondes de retard sur les vues. Pour suivre la taille et le nombre d'entrées d'un store, j'utilise donc `sys.dm_os_memory_cache_counters` :

```sql
SELECT name, type, entries_count, entries_in_use_count, pages_kb / 1024 AS mo
FROM sys.dm_os_memory_cache_counters
WHERE type IN (N'CACHESTORE_SQLCP', N'CACHESTORE_OBJCP',
               N'CACHESTORE_PHDR', N'CACHESTORE_XPROC');
```

Le cache de plans est hors du buffer pool, mais dans la mémoire que limite `max server memory`, comme le dit le [guide d'architecture de la mémoire](https://learn.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide#changes-to-memory-management-starting-with-sql-server-2012), tandis que la [page de cette option](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options) parle du seul buffer pool : à 1 024 Mo, 60 000 requêtes ad hoc font monter le cache de plans à environ 400 Mo et descendre le buffer pool d'autant. Le cache peut aussi peser plus lourd que les données : sur une instance de 4 Go, j'ai vu SQL Plans à 150 Mo et Bound Trees à 153 Mo, à eux deux près de deux fois le cache de données (158 Mo). J'ai proposé de corriger la portée de `max server memory` dans la [PR #10471](https://github.com/MicrosoftDocs/sql-docs/pull/10471). Bound Trees est souvent oublié dans ces comptes ; il contient les arbres des vues et des contraintes, et occupe de la mémoire comme les plans.

## L'invalidation et la recompilation

Un plan en cache peut devenir faux quand un objet dont il dépend change, ou moins bon quand les données ont changé. Avant de s'en servir, le moteur vérifie s'il reste valable. S'il le juge invalide, il recompile l'instruction concernée, et seulement elle, depuis SQL Server 2005 : le nouveau plan remplace l'ancien dans la même entrée. Le `plan_handle` ne change pas, `plan_generation_num` augmente de 1, `usecounts` continue, `execution_count` repart à 1 dans `sys.dm_exec_query_stats`, et `creation_time` dans `sys.dm_exec_query_stats` prend l'heure de la recompilation. Une recompilation ne retire donc rien du cache.

```sql
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.plan_generation_num DESC;
```

Un `plan_handle` qui change d'un relevé à l'autre 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. `creation_time` ne départage pas les deux, puisqu'elle donne l'heure de compilation, pas celle de l'entrée dans le cache.

L'événement étendu `sql_statement_recompile` donne la cause, dans le champ `recompile_cause`. La map `statement_recompile_cause` compte 34 valeurs sur 2025 CU10, contre 23 sur 2022 CU26 et 14 dans le livre blanc de 2012 ; la page de l'ancienne classe d'événements `SQL:StmtRecompile` n'en connaît que 11. J'ai provoqué les plus courantes :

| Action | Cause relevée |
|---|---|
| `SET CONCAT_NULL_YIELDS_NULL OFF` dans le corps d'une procédure | `Set option change`, à la première exécution seulement |
| `CREATE TABLE #t` puis `INSERT` et `SELECT` dans une procédure | `Deferred compile`, pour l'`INSERT` et le `SELECT`, à la première exécution |
| `ALTER TABLE ... ADD` sur une table utilisée | `Schema changed` |
| 6 000 lignes modifiées sur 10 000, puis une jointure avec `GROUP BY` | `Statistics changed` |
| les mêmes modifications, puis un `COUNT(*)` au plan trivial | aucune recompilation |
| `UPDATE STATISTICS ... WITH FULLSCAN` sans modification préalable | aucune recompilation |
| table temporaire qui passe de 10 à 10 000 lignes entre deux exécutions | `Statistics changed` |

Ces essais donnent plusieurs résultats :

- Le plan trivial ne s'est pas recompilé après un changement massif des statistiques.
- Le recalcul d'une statistique sans modification préalable des données n'a pas déclenché de recompilation.
- L'instruction `SET` dans la procédure a déclenché une seule recompilation, lors de la première exécution.
- L'augmentation du nombre de lignes de la table temporaire a déclenché une recompilation avec la cause `Statistics changed`.

La cause `Temp table changed` apparaît dans un autre cas : une procédure lit une table temporaire créée en dehors d'elle, puis la définition de cette table change entre deux exécutions de la procédure. L'exemple suivant montre ce comportement :

```sql
CREATE OR ALTER PROCEDURE dbo.q_ext AS SELECT COUNT(*) AS n FROM #t WHERE a > 0;
GO
CREATE TABLE #t (a int);
EXEC dbo.q_ext;                       -- compilation, mise en cache
EXEC dbo.q_ext;                       -- réutilisation
GO
ALTER TABLE #t ADD b int NULL;
GO
EXEC dbo.q_ext;                       -- Temp table changed
GO
DROP TABLE #t;
GO
CREATE TABLE #t (a bigint);
EXEC dbo.q_ext;                       -- Temp table changed
GO
CREATE INDEX ix ON #t (a);
GO
EXEC dbo.q_ext;                       -- Schema changed
```

La table peut avoir été créée par le batch appelant, par une procédure appelante, ou par un batch précédent de la session ; un batch ad hoc qui relit `#u` après un `ALTER TABLE #u ADD` donne la même cause. Un index ajouté donne `Schema changed`. Dans la procédure qui crée elle-même `#t`, un `ALTER TABLE` donne `Schema changed` à la première exécution, puis plus rien. Le détail des seuils et des cas est dans [La recompilation des plans](https://www.pachadata.com/docs/articles/sqlserver/internals/recompilation/) et [Les statistiques et les plans en cache](https://www.pachadata.com/docs/articles/sqlserver/internals/statistiques-et-plans-en-cache/).

`sp_recompile` sur une table ne retire rien du cache : les plans qui la lisent restent, et chacun se recompile à sa prochaine exécution. Sur une procédure, il retire son plan (`sp_cache_remove` donne la raison `Sp module recompile`).

## Le vidage du cache

Vider le cache, c'est en retirer des entrées sans qu'aucune limite ne l'exige. Les commandes :

| Commande | Effet | Depuis |
|---|---|---|
| `DBCC FREEPROCCACHE` | tout le cache de plans de l'instance | 2000 au moins |
| `DBCC FREEPROCCACHE (plan_handle)` | un plan ; avec un `sql_handle`, les plans d'un batch ; avec un nom de pool, les plans d'un pool du Resource Governor | 2008 |
| `DBCC FREESYSTEMCACHE ('SQL Plans')` | les entrées inutilisées d'un _store_ ; `'ALL'` pour tous les caches | 2005 |
| `ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE` | les plans de la base courante | 2016 |
| `ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE plan_handle` | un plan de la base courante | 2019 |

Pour retirer le plan d'une seule requête, la forme la plus sûre est la première avec un `plan_handle`, retrouvé par son texte :

```sql
SELECT p.plan_handle, p.usecounts, LEFT(t.text, 80) AS debut_du_texte
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.text LIKE N'%DateCreation%FROM%Inscription%'
  AND t.text NOT LIKE N'%dm_exec_cached_plans%';

-- remplacez la valeur par un plan_handle rendu par la requête précédente
DBCC FREEPROCCACHE (0x060005006583130FA0E2279A0D00000001000000000000000000000000000000000000000000000000000000);
```

Pour une requête paramétrée par le moteur, c'est l'entrée `Prepared` qu'il faut retirer : retirer la _shell query_ `Adhoc` laisse le plan en cache.

`DBCC FREESYSTEMCACHE ('SQL Plans')` laisse les procédures, `('Object Plans')` laisse l'ad hoc. Les deux ne retirent que les entrées inutilisées au moment de la commande. Delaney et Low citent aussi `DBCC FLUSHPROCINDB`, non documentée, que `CLEAR PROCEDURE_CACHE` remplace.

### Les reconfigurations et les options de base

Certaines opérations vident le cache sans qu'on le demande. J'ai placé une entrée ad hoc et une procédure dans trois bases, la base de test, une seconde base et `master`, puis exécuté chaque action en suivant `sp_cache_remove` :

| Action | Base de test | Autre base | `master` | Méthode et raison de `sp_cache_remove` |
|---|---|---|---|---|
| `max degree of parallelism` de 0 à 2, ou de 2 à 0 | vidé | vidé | vidé | `Proc Cache Flush`, `Server reconfiguration` |
| `cost threshold for parallelism` de 5 à 10 | vidé | vidé | vidé | idem |
| `max server memory` de 2 048 à 2 000 Mo | vidé | vidé | vidé | idem |
| `max server memory` de 2 000 à 2 048 Mo | gardé | gardé | gardé | aucun |
| `RECONFIGURE` sans changement de valeur | gardé | gardé | gardé | aucun |
| `optimize for ad hoc workloads` de 0 à 1 | gardé | gardé | gardé | aucun |
| `CLEAR PROCEDURE_CACHE` dans la base de test | vidé | gardé | gardé | `Compplan Remove`, `Database settings update` |
| `DBCC FREEPROCCACHE (plan_handle)` | l'entrée seule | gardé | gardé | `Compplan Remove`, `Dbcc command flush` |
| `ALTER DATABASE ... SET AUTO_UPDATE_STATISTICS_ASYNC ON` | vidé | gardé | gardé | `Compplan Remove`, `Database state update` |
| `ALTER DATABASE ... SET COMPATIBILITY_LEVEL = 160` | vidé | gardé | gardé | `Compplan Remove`, `Database compatibility update` |
| `DBCC CHECKDB` | gardé | gardé | gardé | aucun |
| `sp_recompile` sur une table | gardé | gardé | gardé | aucun |

La page de `DBCC FREEPROCCACHE` liste seize options de configuration qui vident le cache, dont `max degree of parallelism`, `cost threshold for parallelism`, `max server memory`, `min server memory`, `max worker threads`, `user options` et `query governor cost limit`. Pour `max server memory`, la mesure nuance : le cache n'est vidé qu'à la baisse. Sous Windows, une baisse de 48 Mo a vidé les trois _stores_, alors qu'une hausse de 48 Mo puis une hausse de 576 Mo (de 1 024 à 1 600 Mo) n'ont rien retiré, et le retour de 1 600 à 1 024 Mo a de nouveau tout vidé. La mesure a été faite deux fois. Sous Linux, j'avais obtenu le même résultat avec 48 Mo dans chaque sens. Changer `MAXDOP` en production vide donc les plans de toutes les bases, et l'instance recompile ensuite toute sa charge ; le réglage par base, `ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP`, ne vide que la base.

Vider une base retire aussi les variantes de ses procédures à paramètres sensibles, rangées dans SQL Plans (voir plus loin). `DBCC CHECKDB` ne vide plus le cache depuis 2005 SP2.

La documentation se contredit sur la portée des options de base. La page [`ALTER DATABASE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql#remarks) dit que `COLLATE`, `MODIFY_NAME`, `OFFLINE`, `ONLINE`, `PAGE_VERIFY`, `READ_ONLY`, `READ_WRITE` et quelques options de groupes de fichiers vident le cache de l'instance, et ajoute `AUTO_CLOSE`, la suppression d'un instantané et la reconstruction du journal ; la page des [options `SET`](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options#remarks) ajoute la restauration et le détachement ; la page [`DROP DATABASE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-database-transact-sql#database-snapshots) limite la suppression d'un instantané à ses propres plans, la page [`RESTORE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql#remarks) limite la restauration à la base restaurée, et le [livre blanc de Greg Low sur SQL Server 2012](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/dn148262%28v%3Dmsdn.10%29) range `SET ONLINE`, `SET OFFLINE` et `MODIFY NAME` parmi les vidages limités à la base. Je les ai mesurés un par un, de la même façon que le tableau précédent :

| Action | Base touchée | Autre base | `master` | Raison de `sp_cache_remove` |
|---|---|---|---|---|
| `SET OFFLINE WITH ROLLBACK IMMEDIATE` | vidé | gardé | gardé | `Database shutdown` |
| `SET ONLINE` | rien à vider | gardé | gardé | aucune |
| `MODIFY NAME` | vidé | gardé | gardé | `Database name change` |
| `SET READ_ONLY`, puis `SET READ_WRITE` | vidé chaque fois | gardé | gardé | `Database shutdown` |
| `SET PAGE_VERIFY TORN_PAGE_DETECTION` | vidé | gardé | gardé | `Database state update` |
| `COLLATE French_CI_AS` d'une base vide | vidé | gardé | gardé | `Database collation update` |
| `RESTORE DATABASE ... WITH REPLACE` | vidé | gardé | gardé | `Database shutdown` |
| `DROP DATABASE` d'un instantané | vidé (l'instantané) | gardé, base source comprise | gardé | `Drop database` |
| `DROP DATABASE` d'une base utilisée | vidé | gardé | gardé | `Drop database` |
| `MODIFY FILEGROUP DEFAULT`, `READ_ONLY`, `READ_WRITE` | vidé chaque fois | gardé | gardé | non relevée dans cette passe |
| détachement puis rattachement | vidé | gardé | gardé | non relevée dans cette passe |
| `DBCC FREESYSTEMCACHE ('ALL')` | vidé | vidé | vidé | `System Flush`, un événement par _store_ |

Le contrôle des `plan_handle` confirme cette portée sur SQL Server 2019 CU32-GDR (`15.0.4500.2`), 2022 CU27-GDR (`16.0.4305.5`) et 2025 CU10 (`17.0.5035.5`), dans des conteneurs Linux neufs. Les trois types de plans, `Adhoc`, `Prepared` et `Proc`, ont été suivis dans la base modifiée, une autre base et `master`. Les essais couvrent aussi les options de groupes de fichiers et le détachement. La suppression d'un instantané conserve les plans de sa base source.

La passe initiale sur 2025 CU10 avait également suivi les événements, ce qui précise le mécanisme de retrait :

- Les plans de la base concernée sont retirés un par un, avec la méthode `Compplan Remove` et un événement par entrée.
- `SET ONLINE` ne retire rien dans cet essai : le passage précédent à `OFFLINE` avait déjà retiré les plans.
- `READ_ONLY` et `READ_WRITE` ferment la base. Ils donnent donc la même raison de retrait, `Database shutdown`, qu'`OFFLINE` et `RESTORE`.

Ces résultats diffèrent de la portée à toute l'instance annoncée par la page `ALTER DATABASE`. Ils concordent avec les pages `RESTORE` et `DROP DATABASE`, ainsi qu'avec le livre blanc de Greg Low cité plus haut.

J'avais déjà mesuré dans [La fermeture automatique d'une base](https://www.pachadata.com/docs/articles/sqlserver/administration/auto-close/) que `AUTO_CLOSE` ne retire que les plans de la base fermée, sur 2019, 2022 et 2025.

La reconstruction du journal reste à mesurer.

### Le message du journal

Un vidage de _store_ écrit dans le journal d'erreurs un message de ce genre :

```output
SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
```

Le journal cumule les vidages avant d'en publier le nombre. Dans mes essais, l'écriture attendait un nouveau vidage après un intervalle d'au moins cinq minutes :

- à `13:20:43`, un premier bloc de messages a compté les vidages depuis `13:15` ;
- à `13:27:03`, un `DBCC FREEPROCCACHE` a déclenché l'écriture du bloc suivant.

La fin du message indique l'origine des vidages :

- `due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations` pour ces commandes ;
- `due to some database maintenance or reconfigure operations` pour les opérations de maintenance ou de reconfiguration.

Dans ces essais, `CLEAR PROCEDURE_CACHE`, les options de base et `DBCC FREEPROCCACHE (plan_handle)` ont retiré des entrées sans vider un _store_. Ces opérations n'ont produit aucun message de ce type.

Le message constitue donc un bilan des vidages depuis l'écriture précédente. Il ne donne ni l'heure de chaque action ni la base concernée. Pour suivre les retraits individuellement, j'utilise les événements étendus :

- `sp_cache_remove` permet de suivre les vidages et les retraits explicites ;
- `query_cache_removal_statistics` permet d'observer les retraits lors des évictions par l'horloge, avec la réserve sur les plans non mis en cache expliquée plus haut.

## Les plans à variantes

SQL Server 2022 a ajouté l'[optimisation des plans sensibles aux paramètres](https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization#psp-optimization-implementation) (*Parameter Sensitive Plan optimization*, PSP), au niveau de compatibilité 160. À la compilation, le moteur repère dans l'histogramme d'une statistique jusqu'à trois prédicats d'égalité paramétrés dont la cardinalité varie beaucoup selon la valeur. Au lieu d'un plan, il compile un plan répartiteur (*dispatcher plan*), qui contient pour chaque prédicat une borne basse et une borne haute découpant les cardinalités en trois plages. À l'exécution, le répartiteur calcule la plage de la valeur et appelle une variante de requête (*query variant*), compilée à sa première utilisation et mise en cache comme une instruction préparée.

PSP est disponible dans les éditions Enterprise, Standard et Express de [SQL Server 2022](https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022#intelligent-query-processing) et de [SQL Server 2025](https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2025#intelligent-query-processing), ainsi que dans l'édition Web de 2022, d'après les tableaux des fonctionnalités par édition. La base doit être au niveau de compatibilité 160 ou supérieur.

J'ai mesuré la répartition dans le cache sur 2025, au niveau 170, avec une procédure dont le paramètre désigne un client de 100 000 lignes ou un client d'une ligne :

| Entrée | Type | _store_ | Contenu |
|---|---|---|---|
| répartiteur de la procédure | `Proc` | Object Plans | élément `<Dispatcher>` avec `ParameterSensitivePredicate LowBoundary="100" HighBoundary="100000"` et la statistique utilisée |
| variante 1 | `Prepared` | SQL Plans | texte terminé par `option (PLAN PER VALUE(ObjectID = 1669580986, QueryVariantID = 1, predicate_range(... = @c, 100.0, 100000.0)))` |
| variante 3 | `Prepared` | SQL Plans | même texte, `QueryVariantID = 3` |

Pour identifier une variante et retrouver sa procédure d'origine :

- L'attribut `objectid` de la variante, dans `sys.dm_exec_plan_attributes`, est un hachage. `sys.dm_exec_sql_text` renvoie un `objectid` à `NULL`. Pour retrouver la procédure, il faut lire l'`ObjectID` dans le texte `PLAN PER VALUE`.
- Le numéro de variante apparaît dans ce même texte. On le retrouve aussi dans l'attribut `QueryVariantID` de l'élément `QueryPlan` du plan XML.
- Les commentaires du corps de la procédure ne sont pas recopiés dans le texte des variantes. Pour chercher celles-ci dans le cache, utiliser un filtre sur `PLAN PER VALUE`, comme dans la requête ci-dessous.

```sql
SELECT p.objtype, p.usecounts, p.size_in_bytes, t.text
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.text LIKE N'%PLAN PER VALUE%'
  AND t.text NOT LIKE N'%dm_exec_cached_plans%';
```

Les statistiques d'exécution sont dans les variantes : `sys.dm_exec_query_stats` n'avait aucune ligne pour le plan de la procédure, deux lignes pour les variantes, de même `query_hash` et de `query_plan_hash` différents, tandis que `sys.dm_exec_procedure_stats` comptait les trois exécutions. Le même mécanisme vaut pour `sp_executesql`, dont le répartiteur est alors un plan `Prepared` de SQL Plans. Une page de la documentation avait inversé les _stores_ du répartiteur et des variantes ; Microsoft l'a corrigée en 2026.

Le niveau 160 suffit pour PSP. Sur 2025, la même procédure a donné au niveau 160 exactement les mêmes entrées qu'au niveau 170 : un répartiteur, les variantes 1 (valeur rare, une ligne estimée) et 3 (valeur fréquente, 100 000 lignes), les mêmes bornes 100 et 100 000.

SQL Server 2025 étend PSP à plusieurs prédicats d'une même table et ajoute, au niveau 170, l'optimisation des paramètres facultatifs (*optional parameter plan optimization*, OPPO), qui utilise les mêmes répartiteurs et variantes pour les prédicats `col = @p OR @p IS NULL`. Les [conditions d'activation d'OPPO](https://learn.microsoft.com/en-us/sql/relational-databases/performance/optional-parameter-optimization#use-optional-parameter-plan-optimization) sont le niveau de compatibilité `170` et `OPTIONAL_PARAMETER_OPTIMIZATION = ON`, activé par défaut.

La différence se voit sur une procédure de ce type :

```sql
CREATE OR ALTER PROCEDURE dbo.p_oppo @c int = NULL
AS SELECT SUM(montant) AS s FROM dbo.Commande WHERE client_id = @c OR @c IS NULL;
GO
EXEC dbo.p_oppo 2; EXEC dbo.p_oppo NULL; EXEC dbo.p_oppo 3; EXEC dbo.p_oppo NULL;
```

Au niveau 160, la procédure n'a reçu qu'une variante PSP, prévue pour une ligne, qui a servi aux quatre appels, y compris aux deux appels avec `NULL` qui lisent les 200 000 lignes de la table avec un plan prévu pour une. Au niveau 170, elle a reçu deux variantes, dont le texte combine les deux mécanismes :

```text
option (PLAN PER VALUE(ObjectID = 178099675, QueryVariantID = 1, optional_predicate(@c IS NULL),
        predicate_range([cachelab].[dbo].[Commande].[client_id] = @c, 100.0, 100000.0)))
option (PLAN PER VALUE(ObjectID = 178099675, QueryVariantID = 4, optional_predicate(@c IS NULL),
        predicate_range([cachelab].[dbo].[Commande].[client_id] = @c, 100.0, 100000.0)))
```

La variante 4 estime 200 000 lignes, pour `@c IS NULL`, la variante 1 en estime une, pour une valeur rare. Les numéros 1, 3 et 4 ne sont pas un compteur : ils désignent la case choisie par le répartiteur (valeur rare, valeur fréquente, paramètre absent). Les variantes comptent dans le plafond d'entrées de SQL Plans, ce que la page de PSP signale.

> Note :
>
> Dans les publications consultées, je n'ai pas trouvé de description scientifique de la conception de PSP telle qu'elle est livrée dans SQL Server.
> 
> Son principe se rapproche de l'optimisation paramétrique décrite en 1992 par Yannis E. Ioannidis, Raymond T. Ng, Kyuseok Shim et Timos K. Sellis dans [*Parametric Query Optimization*](https://www.vldb.org/conf/1992/P103.PDF), VLDB, p. 103-114 : préparer plusieurs plans adaptés à différentes régions de l'espace des paramètres, puis sélectionner à l'exécution celui qui convient aux valeurs rencontrées, en limitant le travail d'optimisation à ce moment-là.
> 
> Microsoft a déposé en 2010 le brevet [*Parameter-sensitive plans*, US 8 874 547 B2](https://patents.google.com/patent/US8874547B2/en), publié en 2014, avec Cesar A. Galindo-Legaria parmi les inventeurs. Il décrit un répartiteur, un espace de sélectivités découpé en régions et la compilation d'un plan à la première valeur rencontrée dans chaque région. PSP présente des ressemblances avec ce mécanisme, avec trois plages par prédicat. Je n'ai trouvé aucune source établissant que PSP implémente ce brevet.
> 
> Des chercheurs de Microsoft Research ont également publié des travaux expérimentaux utilisant SQL Server :
> 
> - Anshuman Dutt, Vivek Narasayya et Surajit Chaudhuri, [*Leveraging Re-costing for Online Optimization of Parameterized Queries with Guarantees*](https://www.microsoft.com/en-us/research/publication/leveraging-re-costing-online-optimization-parameterized-queries-guarantees/), SIGMOD, 2017, p. 1539-1554. Les auteurs évaluent leur technique dans une version modifiée de l'optimiseur de SQL Server 2016.
> - Kapil Vaidya, Anshuman Dutt, Vivek Narasayya et Surajit Chaudhuri, [*Leveraging Query Logs and Machine Learning for Parametric Query Optimization*](https://www.microsoft.com/en-us/research/publication/leveraging-query-logs-and-machine-learning-for-parametric-query-optimization/), *PVLDB*, vol. 15, no 3, novembre 2021, p. 401-413, présenté à VLDB 2022. Leur prototype sur SQL Server 2017 utilise les journaux de requêtes pour choisir les plans à conserver et entraîner un modèle de sélection du plan.
> 
> Ces travaux éclairent le problème traité par PSP. Leur présence dans la littérature ne suffit pas à établir un lien avec l'implémentation du produit.


## Le Query Store et le cache

Le Query Store garde dans la base les requêtes, leurs plans et leurs statistiques d'exécution, et il survit au redémarrage, ce que le cache ne fait pas : après un redémarrage, le plus ancien plan du cache a l'âge du service. Le Query Store ne remplace pas le cache. Un plan forcé par le Query Store est compilé comme les autres, sous la contrainte du plan forcé, puis mis en cache ; la compilation n'est pas évitée. Les causes de recompilation propres au Query Store (plan manquant, échec du forçage, changement de politique de forçage) apparaissent dans la map des causes.

Plusieurs mécanismes récents stockent leur état dans le plan en cache et le perdent quand il en sort. La rétroaction d'allocation mémoire (*memory grant feedback*), en mode batch depuis 2017 et en mode ligne depuis 2019, corrigeait l'allocation d'un plan dans le cache seulement, jusqu'à ce que SQL Server 2022 la persiste dans le Query Store, quand celui-ci est activé en lecture et écriture ; sinon, l'éviction du plan la fait toujours perdre. `sys.dm_exec_query_plan_stats` (2019), qui donne le dernier plan réel connu, ne rend rien pour un plan évincé.

De SQL Server 2022 CU8 à CU11 (corrigé dans CU12), la rétroaction d'estimation de cardinalité a causé une fuite du cache : des plans marqués en cours d'utilisation ne pouvaient plus être retirés, ni par l'horloge ni par `DBCC FREEPROCCACHE`, et leur nombre montait à 50 000 ou plus.

## Les instruments

| Instrument | Ce qu'il montre |
|---|---|
| `sys.dm_exec_cached_plans` | une ligne par entrée : type, taille, `usecounts`, `plan_handle` |
| `sys.dm_exec_plan_attributes` | la clé du cache et les compteurs de contextes d'un plan |
| `sys.dm_exec_query_stats`, `sys.dm_exec_procedure_stats`, `sys.dm_exec_trigger_stats`, `sys.dm_exec_function_stats` | statistiques d'exécution, par instruction ou par module, tant que le plan est en cache |
| `sys.dm_exec_cached_plan_dependent_objects` | contextes d'exécution et curseurs d'un plan |
| `sys.dm_os_memory_cache_counters` | taille et nombre d'entrées par _store_ |
| `sys.dm_os_memory_cache_hash_tables` | buckets, longueur des chaînes, succès et échecs de recherche |
| `sys.dm_os_memory_cache_clock_hands` | les deux aiguilles et leurs retraits |
| `sys.dm_os_memory_cache_entries` | coût, taille et état de chaque entrée |
| événements `sp_cache_insert`, `sp_cache_hit`, `sp_cache_miss` | insertion, réutilisation, recherche manquée, mais seulement pour un batch non mis en cache ou une session qui attendait un verrou de compilation |
| événement `sp_cache_remove` | retrait explicite (commande `DBCC`, option de base, vidage d'un _store_), avec sa méthode et sa raison (38 raisons sur 2025) ; ne voit pas les évictions par l'horloge |
| événement `plan_not_cached` | ce qui n'a pas été mis en cache, et pourquoi (70 raisons) |
| événement `query_cache_removal_statistics` | les statistiques d'une requête au moment où son plan est détruit, y compris par l'horloge : le seul événement qui voit les évictions |
| événement `sql_statement_recompile` | recompilation d'une instruction et sa cause |
| compteurs `SQLServer:Plan Cache` | taux de succès, nombre d'objets, pages, par _store_, avec quelques secondes de retard |
| compteurs `SQLServer:SQL Statistics` | compilations, recompilations, tentatives de paramétrage |

Aucune page de Learn ne décrit les événements `sp_cache_*` ni `plan_not_cached`, et `query_cache_removal_statistics` n'y est que nommé ; leurs champs se lisent dans `sys.dm_xe_object_columns`, et les valeurs de leurs maps dans `sys.dm_xe_map_values`. Une session qui suit les retraits d'un cache, sur une instance de test :

```sql
CREATE EVENT SESSION suivi_retraits ON SERVER
ADD EVENT sqlserver.sp_cache_remove (
    ACTION (sqlserver.database_id, sqlserver.sql_text))
ADD TARGET package0.ring_buffer;
GO
ALTER EVENT SESSION suivi_retraits ON SERVER STATE = START;
```

Le compteur `SQL Compilations/sec` compte plus que des optimisations. Il monte aussi pour la recherche d'une _shell query_ et pour le paramétrage : sous paramétrage forcé, un site web simulé faisait 4 640 à 5 256 compilations par seconde sans aucune optimisation, ce que montrait `sys.dm_exec_query_optimizer_info`. Le rapport entre compilations et batchs par seconde ne suffit donc pas à juger qu'une charge est mal paramétrée.

## Le cache dans d'autres moteurs

Les autres moteurs ont fait d'autres choix, et la comparaison éclaire ceux de SQL Server.

Oracle partage des curseurs dans la *shared pool* : un curseur parent par texte exact, des curseurs enfants par plan et par environnement, l'équivalent des options `SET` de SQL Server. L'optimiseur lit les valeurs des variables liées à la première analyse (*bind peeking*), le reniflage de SQL Server. Depuis 11g, le partage adaptatif des curseurs (*adaptive cursor sharing*) apprend après coup, d'après les statistiques d'exécution, qu'un curseur est sensible à ses valeurs, et range des plages de sélectivité par prédicat : c'est PSP, à ceci près que PSP décide à la compilation, d'après l'histogramme. Oracle invalide aussi les curseurs progressivement après un recalcul de statistiques, chacun après un délai aléatoire, ce que SQL Server ne fait pas.

PostgreSQL n'a pas de cache partagé entre sessions : une instruction préparée vit dans sa session. Avec `plan_cache_mode = auto`, les cinq premières exécutions utilisent des plans personnalisés pour les valeurs reçues, puis le moteur compare leur coût moyen à celui d'un plan générique et garde le générique s'il n'est pas plus cher. Le code ajoute au coût d'un plan personnalisé une estimation du travail de planification, `1000 × cpu_operator_cost × (relations + 1)`, que son commentaire juge « probablement basse ». PostgreSQL tient ainsi compte du coût de planification dans son choix. SQL Server ne choisit pas entre un plan personnalisé et un plan générique de cette manière.

Db2 a gardé le modèle de System R pour le SQL statique : des packages compilés à l'avance par `BIND`, invalidés par leurs dépendances. MySQL n'a pas de cache de plans partagé, et a retiré en 8.0 son cache de résultats, qui n'était pas un cache de plans.

## Les questions ouvertes

Ce que ni la documentation, ni mes mesures, ni le débogueur n'ont tranché :

- comment `CacheClockAlgorithm::CalculateCost`, qui combine des *ticks* de compilation et des pages, aboutit à la suite 0, 1, 2, 4, 8, 16 mesurée à chaque réutilisation ;
- si une réutilisation sous pression remet à son coût d'origine une entrée dont le coût d'origine est inférieur à 16 (les entrées à 16 reviennent à 16) ;
- la fonction qui calcule l'`objectid` haché d'un batch ad hoc, et ce que désigne exactement l'adresse des octets 8 à 15 du `plan_handle` ;
- si la « case » du hachage de Zobrist est le rang d'un nœud ou son chemin complet dans l'arbre ;
- l'éviction d'un plan fréquent quand le _store_ est plein de plans préparés, qui demande, pour des plans de 48 Ko, une mémoire cible d'environ 120 Go ;
- la portée de la reconstruction du journal ;
- la règle qui sépare `SensInfo` et `ZeroCostPlan` pour les instructions de définition seules dans leur batch ;
- la raison pour laquelle une exception détruit le contexte d'exécution, que je déduis sans l'avoir lue dans le code.

## Bibliographie

Les références suivantes regroupent les sources citées dans l'article, avec les éditions utilisées et les chapitres pertinents.

### Livres

- Mara Bos, [*Rust Atomics and Locks*](https://mara.nl/atomics/), O'Reilly, 2023. Chapitre 7, [« Understanding the Processor »](https://mara.nl/atomics/hardware.html#x86-compare-and-exchange-instruction), instruction x86 de comparaison et échange.
- Andrew S. Tanenbaum et Herbert Bos, [*Modern Operating Systems*](https://www.pearson.com/en-us/pearsonplus/p/9780137618880.html), Pearson, 5e édition, 2023 (publiée en octobre 2022). Chapitre 3, « Memory Management », algorithmes de remplacement de pages et vieillissement.
- Jonathan Kehayias et Ted Krueger, [*Troubleshooting SQL Server: A Guide for the Accidental DBA*](https://www.red-gate.com/simple-talk/resources/books/simple-talk-published-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/), Redgate, 2011. Discussion de la mémoire et de la taille du cache de plans sous SQL Server 2005 et 2008.
- Kalen Delaney et al., [*Microsoft SQL Server 2012 Internals*](https://www.microsoftpressstore.com/store/microsoft-sql-server-2012-internals-9780735658561), Microsoft Press, 2013. Chapitre 12, « Plan caching and recompilation », rédigé par Kalen Delaney, notamment la section « Cache stores » et le tableau 12-1.
- Kalen Delaney et al., [*Inside Microsoft SQL Server 2005: Query Tuning and Optimization*](https://www.microsoftpressstore.com/store/inside-microsoft-sql-server-2005-query-tuning-and-optimization-9780735621961), Microsoft Press, 2007. Chapitre 5, « Plan Caching and Recompilation », également disponible dans les [archives Microsoft](https://learn.microsoft.com/en-us/previous-versions/tn-archive/cc304418%28v%3Dtechnet.10%29).
- Dmitri Korotkevitch, [*Pro SQL Server Internals*](https://doi.org/10.1007/978-1-4302-5963-3), Apress, 1re édition, 2014. Chapitre 26, « Plan Caching ».
- Grant Fritchey, [*SQL Server Execution Plans*](https://www.red-gate.com/simple-talk/featured/sql-server-execution-plans-third-edition-by-grant-fritchey/), Redgate, 3e édition, 2018. Sections « Plan aging », p. 35, et « Criteria for plan reuse », p. 36, et chapitre 9, « Exploring Plan Reuse ».
- Grant Fritchey, [*SQL Server 2025 Query Performance Tuning: Troubleshoot and Optimize Query Performance*](https://doi.org/10.1007/979-8-8688-1865-3), Apress, 7e édition, 2026. Chapitre 2, [« Execution Plan Generation and the Query Optimizer »](https://doi.org/10.1007/979-8-8688-1865-3_2), section « Aging of the Execution Plan », p. 37-38 ; chapitres 7, « Execution Plan Cache Behavior », et 8, « Query Recompilation ».
- Steven Wort et al., *Professional SQL Server 2005 Performance Tuning*, Wrox/Wiley, 2008, ISBN 9780470176399. [Extrait de l'éditeur](https://catalogimages.wiley.com/images/db/pdf/9780470176399.excerpt.pdf).

### Livres blancs Microsoft

- Arun Marathe, révision par Shu Scott, [*Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005*](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966425%28v%3Dtechnet.10%29), Microsoft, mise à jour de décembre 2006.
- Greg Low, [*Plan Caching in SQL Server 2008*](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ee343986%28v%3Dsql.100%29), Microsoft, août 2009.
- Greg Low, [*Plan Caching and Recompilation in SQL Server 2012*](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/dn148262%28v%3Dmsdn.10%29), Microsoft, mars 2013. Voir notamment les contextes d'exécution, les sections parallèles, les clés du cache et l'annexe A sur le paramétrage simple.

### Articles et rapports de recherche

- Anshuman Dutt, Vivek Narasayya et Surajit Chaudhuri, [*Leveraging Re-costing for Online Optimization of Parameterized Queries with Guarantees*](https://www.microsoft.com/en-us/research/publication/leveraging-re-costing-online-optimization-parameterized-queries-guarantees/), ACM SIGMOD, 2017, p. 1539-1554, [DOI 10.1145/3035918.3064040](https://doi.org/10.1145/3035918.3064040).
- Kapil Vaidya, Anshuman Dutt, Vivek Narasayya et Surajit Chaudhuri, [*Leveraging Query Logs and Machine Learning for Parametric Query Optimization*](https://www.microsoft.com/en-us/research/publication/leveraging-query-logs-and-machine-learning-for-parametric-query-optimization/), *PVLDB*, vol. 15, no 3, novembre 2021, p. 401-413, [DOI 10.14778/3494124.3494126](https://doi.org/10.14778/3494124.3494126).
- Redha Gouicem, [*Operating Systems and System Software*](https://teaching.os.rwth-aachen.de/BuS/BuS-lecture-full.html#page-replacement-lru-approximation-with-aging), RWTH Aachen. Section « Page Replacement: LRU Approximation With Aging ».
- Edsger W. Dijkstra, [*Recursive programming*](https://ir.cwi.nl/pub/9253), *Numerische Mathematik*, vol. 2, 1960, p. 312-318.
- Donald D. Chamberlin et al., [*A History and Evaluation of System R*](https://research.ibm.com/publications/a-history-and-evaluation-of-system-r), *Communications of the ACM*, vol. 24, no 10, octobre 1981, p. 632-646.
- Fernando J. Corbató, [*A Paging Experiment with the Multics System*](https://multicians.org/paging-experiment.pdf), dans *In Honor of Philip M. Morse*, MIT Press, 1969, p. 217-228.
- Victor F. Nicola, Asit Dan et Daniel M. Dias, [*Analysis of the Generalized Clock Buffer Replacement Scheme for Database Transaction Processing*](https://doi.org/10.1145/133057.133084), ACM SIGMETRICS, 1992.
- Neal E. Young, [*On-Line Caching as Cache Size Varies*](https://www.cs.ucr.edu/~neal/publication/Young91Online.pdf), ACM-SIAM SODA, 1991. Version en revue : [*The k-server dual and loose competitiveness for paging*](https://www.cs.ucr.edu/~neal/publication/Young94KServer.pdf), *Algorithmica*, vol. 11, no 6, 1994, p. 525-541.
- Pei Cao et Sandy Irani, [*Cost-Aware WWW Proxy Caching Algorithms*](https://www.usenix.org/conference/usits-97/cost-aware-www-proxy-caching-algorithms), USENIX USITS, 1997, p. 193-206.
- Albert L. Zobrist, [*A New Hashing Method with Application for Game Playing*](https://research.cs.wisc.edu/techreports/1970/TR88.pdf), rapport technique 88, université du Wisconsin, 1970.
- J. Lawrence Carter et Mark N. Wegman, [*Universal Classes of Hash Functions (Extended Abstract)*](https://doi.org/10.1145/800105.803400), ACM STOC, 1977, p. 106-112. Version en revue : [*Universal Classes of Hash Functions*](https://doi.org/10.1016/0022-0000%2879%2990044-8), *Journal of Computer and System Sciences*, vol. 18, no 2, 1979, p. 143-154.
- Mihai Pătrașcu et Mikkel Thorup, [*The Power of Simple Tabulation Hashing*](https://doi.org/10.1145/1993636.1993638), ACM STOC, 2011, p. 1-10. [Version longue des auteurs](https://people.csail.mit.edu/mip/papers/charhash/charhash.pdf).
- Yannis E. Ioannidis, Raymond T. Ng, Kyuseok Shim et Timos K. Sellis, [*Parametric Query Optimization*](https://www.vldb.org/conf/1992/P103.PDF), VLDB, 1992, p. 103-114. Version longue : [*Parametric query optimization*](https://www.vldb.org/journal/VLDBJ6/70060132.pdf), *The VLDB Journal*, vol. 6, no 2, 1997, p. 132-151, [DOI 10.1007/s007780050037](https://doi.org/10.1007/s007780050037).
- Sumit Ganguly, [*Design and Analysis of Parametric Query Optimization Algorithms*](https://www.vldb.org/conf/1998/p228.pdf), VLDB, 1998, p. 228-238.

### Billets techniques

- Slava Oks, [*SQLOS Caching*](https://learn.microsoft.com/en-us/archive/blogs/slavao/sqlos-caching), 18 mars 2005, et [*Q and A: Clock Hands - what are they for?*](https://learn.microsoft.com/en-us/archive/blogs/slavao/q-and-a-clock-hands-what-are-they-for), 20 novembre 2005, archives Microsoft.
- Sangeetha Shekar et l'équipe SQL Programmability, série sur le cache de plans, janvier 2007, notamment [*6.0 Best Programming Practices*](https://learn.microsoft.com/en-us/archive/blogs/sqlprogrammability/6-0-best-programming-practices), [*7.0 Costing Cache Entries*](https://techcommunity.microsoft.com/blog/sqlserver/7-0-costing-cache-entries/383210) et [*9.0 Memory Pressure Limits*](https://learn.microsoft.com/en-us/archive/blogs/sqlprogrammability/9-0-memory-pressure-limits). Les dates de republication des archives diffèrent des dates des billets d'origine.
- Équipe SQL Server, [*Name resolution, default schema, implicit schema Part I*](https://techcommunity.microsoft.com/blog/sqlserver/name-resolution-default-schema-implicit-schema-part-i/383052/), publié initialement le 3 avril 2006. Passage « Active Default Schema ».
- Bart Duncan, [*Query Fingerprints and Plan Fingerprints: The Best SQL 2008 Feature That You've Never Heard Of*](https://learn.microsoft.com/en-us/archive/blogs/bartd/query-fingerprints-and-plan-fingerprints-the-best-sql-2008-feature-that-youve-never-heard-of), 3 septembre 2008.
- Aaron Bertrand, [*Multiple Plans for an "Identical" Query*](https://sqlperformance.com/2014/11/t-sql-queries/multiple-plans-identical-query), SQLPerformance, 20 novembre 2014.
- Paul White, *Simple Parameterization and Trivial Plans*, SQLPerformance, 2022 : [partie 1](https://sqlperformance.com/2022/03/sql-performance/simple-param-trivial-plans-1), [partie 2](https://sqlperformance.com/2022/03/sql-performance/simple-param-trivial-plans-2), [partie 3](https://sqlperformance.com/2022/04/sql-performance/simple-parameterization-and-trivial-plans-part-3), [partie 4](https://sqlperformance.com/2022/05/sql-optimizer/simple-parameterization-and-trivial-plans-part-4), [partie 5](https://sqlperformance.com/2022/06/sql-optimizer/simple-parameterization-and-trivial-plans-part-5), [partie 6](https://sqlperformance.com/2022/08/sql-optimizer/simple-parameterization-and-trivial-plans-part-6).
- Paul White, [*Parameter Sniffing, Embedding, and the RECOMPILE Options*](https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options), SQLPerformance, 28 août 2013.
- Erland Sommarskog, [*Slow in the Application, Fast in SSMS? Understanding Performance Mysteries*](https://www.sommarskog.se/query-plan-mysteries.html), notamment les sections sur les clés du cache, le schéma par défaut et l'autoparamétrage.
- Kendra Little, [*Query Hash Values Are Meaningless in SQL Server: They May Be Reset to Be the Same Value as the Query Plan Hash*](https://kendralittle.com/2024/11/24/query-hash-values-meaningless-same-as-query-plan-hash-sql-server/), 24 novembre 2024.

### Documentation Microsoft

- Microsoft, [*Editions and supported features of SQL Server 2022*](https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022#intelligent-query-processing) et [*Editions and supported features of SQL Server 2025*](https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2025#intelligent-query-processing), tableaux « Intelligent query processing », disponibilité de PSP par édition.
- Microsoft, [*SQL Server, Plan Cache object*](https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-plan-cache-object) et [*sys.dm_exec_procedure_stats*](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-procedure-stats-transact-sql), compteurs du cache et date de mise en cache des plans de procédures.
- Microsoft, [*Query Processing Architecture Guide*](https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide), notamment les sections « Execution context », « Cache multiple plans for the same query » et « Remove execution plans from the plan cache ».
- Microsoft, [*Memory Management Architecture Guide*](https://learn.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide).
- Microsoft, [*sys.dm_exec_cached_plans*](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-cached-plans-transact-sql) et [*sys.dm_exec_plan_attributes*](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-plan-attributes-transact-sql).
- Microsoft, [*sys.dm_exec_query_stats*](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-query-stats-transact-sql) et [*sys.dm_exec_cached_plan_dependent_objects*](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-cached-plan-dependent-objects-transact-sql).
- Microsoft, [*sys.dm_os_memory_objects*](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-os-memory-objects-transact-sql) et [*sys.dm_os_memory_cache_entries*](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-os-memory-cache-entries-transact-sql).
- Microsoft, [*sys.dm_os_memory_cache_counters*](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-os-memory-cache-counters-transact-sql), [*sys.dm_os_memory_cache_hash_tables*](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-os-memory-cache-hash-tables-transact-sql) et [*sys.dm_os_memory_cache_clock_hands*](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-os-memory-cache-clock-hands-transact-sql).
- Microsoft, [*sys.query_store_query*](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-query-transact-sql).
- Microsoft, [*sp_executesql*](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql), notamment la section « OPTIMIZED_SP_EXECUTESQL ».
- Microsoft, [*Server Configuration: optimize for ad hoc workloads*](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option).
- Microsoft, [*ALTER DATABASE SCOPED CONFIGURATION*](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql) et [*DBCC FREEPROCCACHE*](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql).
- Microsoft, [*CREATE PROCEDURE*](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql#interoperability) et [*Execute a Stored Procedure*](https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure).
- Microsoft, [*sqlcmd utility*](https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility#query-execution-options), option `-I`.
- Microsoft, [*Configure parameters with Microsoft.Data.SqlClient*](https://learn.microsoft.com/en-us/sql/connect/ado-net/configure-parameters) et [*SqlParameter.Size*](https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlparameter.size).
- Microsoft, [*Troubleshoot blocking issues caused by compile locks*](https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-blocking-caused-compile-locks), notamment la section « Stored procedure is invoked as a Language event ».
- Microsoft, [KB3026083, *SOS_CACHESTORE spinlock contention on ad hoc SQL Server plan cache causes high CPU usage*](https://support.microsoft.com/en-us/servicing/sql/performance-query/kb3026083-fix-sos-cachestore-spinlock-contention-on-ad-hoc-sql-server-plan-cache-causes-high-cpu-usa), limite d'entrées et indicateur de trace 174.
- Microsoft, [*Parameter Sensitive Plan optimization*](https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization), plans répartiteurs, variantes et problèmes connus.

### Structures de données

- NIST, [*Load factor*](https://xlinux.nist.gov/dads/HTML/loadfactor.html), *Dictionary of Algorithms and Data Structures*. Définition du facteur de charge d'une table de hachage.
- Robert Sedgewick et Kevin Wayne, [supports d'*Algorithms*, section 3.4, « Hash Tables »](https://www.cs.princeton.edu/courses/archive/spring14/cos226/lectures/34HashTables%2B35SearchingApplications-2x2.pdf#page=5), Princeton. Diapositive 20, « Analysis of separate chaining », page 5 du PDF : dimensionnement courant `M ≈ N / 4`.
- Équipe Tcl, [`generic/tclHash.c`](https://core.tcl-lang.org/tcl/file?ci=tip&name=generic/tclHash.c). Constante `REBUILD_MULTIPLIER` et agrandissement des tables de hachage.

### Autres moteurs

- PostgreSQL Global Development Group, [`buf_internals.h`](https://github.com/postgres/postgres/blob/REL_18_STABLE/src/include/storage/buf_internals.h), branche PostgreSQL 18. Définition du mot d'état des buffers et mises à jour atomiques.
- Oracle, [*Improving Real-World Performance Through Cursor Sharing*](https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/improving-rwp-cursor-sharing.html), *Oracle Database 19c SQL Tuning Guide*.
- PostgreSQL Global Development Group, [*PREPARE*](https://www.postgresql.org/docs/18/sql-prepare.html), documentation PostgreSQL 18, et [*plancache.c*](https://github.com/postgres/postgres/blob/REL_18_STABLE/src/backend/utils/cache/plancache.c), notamment la fonction `choose_custom_plan`.
- Oracle, [*What Is New in MySQL 8.0*](https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html), section sur les fonctionnalités retirées, dont le cache de résultats.

### Normes et brevets

- Roy T. Fielding, Mark Nottingham et Julian Reschke, [*HTTP Caching*, RFC 9111](https://www.rfc-editor.org/rfc/rfc9111.html), juin 2022. Section 2, clé d'un cache HTTP.
- Nimish S. Khanolkar, Pawel Terlecki, Cesar A. Galindo-Legaria, Yavor V. Angelov, Mostafa Elhemali et Pooja N. Darera, [*Parameter-sensitive plans*, US 8 874 547 B2](https://patents.google.com/patent/US8874547B2/en), Microsoft. Dépôt le 4 janvier 2010, publication le 28 octobre 2014.

### Scripts et corrections de documentation

- Rudi Bruchez, [*Plans multiples pour un même texte*](https://github.com/rudi-bruchez/tsql-scripts/blob/a570746cba1259a5fc83fca8f525e060d6f55dea/diagnostics/Memory/plan-cache-duplicates.sql), dépôt `TSQL-Scripts`.
- MicrosoftDocs/sql-docs, [PR #10501, *Correct plan stub identification and clarify database cache clearing*](https://github.com/MicrosoftDocs/sql-docs/pull/10501), proposition de correction envoyée le 15 novembre 2026.
- MicrosoftDocs/sql-docs, [PR #10502, *Correct the database scope of plan cache removal for ALTER DATABASE*](https://github.com/MicrosoftDocs/sql-docs/pull/10502), proposition de correction envoyée le 15 novembre 2026.


---

## L'auteur

Rudi Bruchez, consultant SQL Server indépendant depuis 2006 : audit de performance, optimisation, migrations, haute disponibilité et formation. Pour un audit ou une intervention sur votre serveur : https://www.pachadata.com/contact/ (audit à distance : https://www.pachadata.com/services/audit-sql-server-a-distance/).
