La recompilation des plans

Ce qui fait recompiler un plan en cache dans SQL Server 2022 et 2025, comment le voir avec les événements étendus, ce que cela coûte et comment l’éviter.

Article coécrit avec Claude, qui a mené les mesures et les recherches.

Compilation, recompilation et éviction

SQL Server compile un lot ou une procédure en un plan d’exécution, qu’il garde dans le cache des plans pour le réutiliser. Avant d’exécuter une instruction d’un plan pris dans le cache, il vérifie que ce plan est encore valide, puis encore optimal. Si l’une des deux vérifications échoue, il compile cette instruction à nouveau : c’est une recompilation (recompilation). Depuis SQL Server 2005, seule cette instruction est recompilée, pas tout le lot.

Le plan recompilé remplace l’ancien dans la même entrée du cache. Le plan_handle ne change pas, usecounts continue de monter, et dans sys.dm_exec_query_stats l’instruction voit plan_generation_num augmenter d’un, creation_time changer et execution_count repartir à 1 : les statistiques cumulées de l’instruction recommencent à zéro à chaque recompilation.

Trois situations ressemblent à une recompilation sans en être une, au sens du moteur :

  • le plan a été retiré du cache (pression mémoire, DBCC FREEPROCCACHE, ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, sp_recompile sur une procédure, ALTER PROCEDURE, changement d’une configuration de base comme MAXDOP) et l’exécution suivante compile un plan neuf ;
  • la session arrive avec d’autres options SET que celles du plan en cache, et SQL Server compile une seconde entrée de cache ;
  • la procédure est appelée avec WITH RECOMPILE.

Ce sont des compilations. Kalen Delaney le résume dans Microsoft SQL Server 2012 Internals : toute recompilation est une compilation, l’inverse est faux. La distinction compte pour la mesure : le compteur SQL Re-Compilations/sec et l’événement sql_statement_recompile ne voient pas ces trois cas.

Les causes de recompilation d’une instruction, et les situations qui font seulement compiler un plan neuf.

Observer les recompilations

L’événement étendu sql_statement_recompile se déclenche à chaque recompilation d’une instruction, quel que soit le type de lot (procédure, déclencheur, lot ad hoc, sp_executesql), et son champ recompile_cause en donne la raison. L’événement sql_statement_post_compile se déclenche après chaque compilation, recompilation ou non : son champ recompile_cause vaut « Not a recompile » pour une compilation ordinaire, et son champ duration donne la durée de la compilation en millisecondes. sp_cache_remove signale un plan retiré du cache, avec sa raison. sql_statement_post_compile, ajouté à SQL Server 2016 et décrit seulement dans les notes de version de son Service Pack 3, est le seul des trois qui distingue une nouvelle entrée de cache d’une recompilation ; il ne se déclenche pas pour la recompilation d’une instruction DDL.

La session ci-dessous filtre sur l’identifiant de la base, et se crée donc par un EXEC :

USE PachaDataFormation;
-- Session d'événements étendus : recompilations, compilations et retraits du cache
DECLARE @s nvarchar(max) = N'CREATE EVENT SESSION recompilations ON SERVER
ADD EVENT sqlserver.sql_statement_recompile (SET collect_object_name = 1, collect_statement = 1
    WHERE sqlserver.database_id = ' + CAST(DB_ID() AS nvarchar(10)) + N'),
ADD EVENT sqlserver.sql_statement_post_compile (SET collect_object_name = 1, collect_statement = 1
    WHERE sqlserver.database_id = ' + CAST(DB_ID() AS nvarchar(10)) + N'),
ADD EVENT sqlserver.sp_cache_remove (WHERE database_id = ' + CAST(DB_ID() AS nvarchar(10)) + N')
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS);';
EXEC (@s);
ALTER EVENT SESSION recompilations ON SERVER STATE = START;

Pour la lire :

SELECT e.n.value('@name', 'varchar(30)') AS evenement,
       e.n.value('(data[@name="recompile_cause"]/text)[1]', 'varchar(60)') AS cause,
       e.n.value('(data[@name="remove_reason"]/text)[1]', 'varchar(30)') AS retrait,
       e.n.value('(data[@name="duration"]/value)[1]', 'bigint') AS duree_ms,
       e.n.value('(data[@name="object_name"]/value)[1]', 'sysname') AS objet,
       LEFT(e.n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)'), 50) AS instruction
FROM (SELECT CAST(t.target_data AS xml) AS d
      FROM sys.dm_xe_sessions AS s
      JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address
      WHERE s.name = 'recompilations') AS r
CROSS APPLY r.d.nodes('RingBufferTarget/event') AS e(n);

Les exemples de cet article utilisent une procédure qui totalise les prestations d’un formateur. Créez-la, exécutez-la deux fois, puis lisez son état dans le cache :

USE PachaDataFormation;
GO
CREATE OR ALTER PROCEDURE Formateur.PrestationsDuFormateur @FormateurId int
AS
SELECT COUNT(*) AS prestations, SUM(p.Jours) AS jours, MAX(s.DateDebut) AS derniere_session
FROM Formateur.Prestation AS p
JOIN Stage.Session AS s ON s.SessionId = p.SessionId
WHERE p.FormateurId = @FormateurId;
GO
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;
GO
-- Entrée de cache de la procédure et génération du plan de son instruction
SELECT cp.usecounts, qs.plan_generation_num, qs.execution_count
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
LEFT JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
WHERE st.objectid = OBJECT_ID('Formateur.PrestationsDuFormateur') AND st.dbid = DB_ID();

La dernière requête rend usecounts 2, plan_generation_num 1 et execution_count 2. Je l’appellerai la requête d’état.

Les compteurs de performances SQL Compilations/sec et SQL Re-Compilations/sec, de l’objet SQL Statistics, comptent les mêmes choses à l’échelle de l’instance. Le premier inclut les recompilations, ce que dit sa page de documentation. Les ratios qu’on lit souvent (moins de 10 % de compilations par lot, moins de 10 % de recompilations par compilation) ne viennent pas de Microsoft, qui ne donne aucun chiffre : je les ai trouvés chez Ken Simmons et Sylvester Carstarphen (Pro SQL Server 2012 Administration), Brent Ozar reprend le premier, et Christian Bolton propose 20 % (Professional SQL Server 2008 Internals and Troubleshooting). Rapporter les recompilations aux compilations compare un compteur à un autre qui le contient déjà.

La liste des causes se lit sur l’instance :

SELECT map_key, map_value
FROM sys.dm_xe_map_values
WHERE name = N'statement_recompile_cause'
ORDER BY map_key;

Elle compte 23 valeurs sur SQL Server 2022 CU26 et 34 sur 2025 CU7, dont « Not a recompile ». Les codes 1 à 23 sont les mêmes sur les deux versions, mais ils ont changé depuis des versions plus anciennes ; mieux vaut citer les libellés. Les sections suivantes reprennent les causes que j’ai pu provoquer.

Les changements de schéma

La cause Schema changed couvre toute modification de la définition d’un objet que le plan lit. Avec la session et la procédure en place, créez un index sur une colonne que la requête n’utilise pas, réexécutez la procédure et la requête d’état :

USE PachaDataFormation;
CREATE INDEX ix_Prestation_DateFacture ON Formateur.Prestation (DateFacture);
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;
DROP INDEX ix_Prestation_DateFacture ON Formateur.Prestation;
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;

La session montre deux recompilations Schema changed, une pour la création de l’index, une pour sa suppression, et la requête d’état donne usecounts 4, plan_generation_num 3, execution_count 1. Le guide d’architecture du traitement des requêtes, section sur la recompilation des plans, ne cite que les index utilisés par le plan ; Grant Fritchey l’avait remarqué dans SQL Server Query Performance Tuning, où un index inutile recompile quand même.

Sur une table de test, sur 2022 et 2025, et pour les trois formes d’une même requête (procédure, sp_executesql, lot ad hoc), j’ai obtenu ceci :

Action sur la table lueEffet sur le plan
CREATE INDEX ou DROP INDEX, même d’un index que le plan n’utilise pasSchema changed
ALTER TABLE ... ADD ou DROP COLUMN, même d’une colonne non lueSchema changed
ALTER TABLE ... ADD CONSTRAINT ... CHECKSchema changed
CREATE TRIGGER, même pour une requête en lectureSchema changed
ALTER INDEX ... REBUILD, DROP STATISTICSSchema changed
ALTER INDEX ... REORGANIZE, CREATE STATISTICSrien
GRANT SELECTrien
CREATE INDEX sur une autre tablerien
sp_recompile sur la tableSchema changed

sp_recompile agit de deux façons selon ce qu’on lui donne. Sur une table, il marque la table comme modifiée, et chaque instruction qui la lit recompile à sa prochaine exécution : les procédures, mais aussi les plans de sp_executesql et des lots ad hoc, que la page de sp_recompile ne cite pas. Sur une procédure, il retire son plan du cache :

USE PachaDataFormation;
EXEC sp_recompile N'Formateur.PrestationsDuFormateur';
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;

La session montre un sp_cache_remove de raison « Sp module recompile », puis une compilation « Not a recompile », et aucune recompilation ; la requête d’état repart à usecounts 1 et plan_generation_num 1. ALTER PROCEDURE fait de même, avec la raison « Metadata update », et un changement de configuration de base (ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP) retire tous les plans de la base, raison « Database settings update ».

Les options SET

Une partie des options de session (ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, DATEFIRST, DATEFORMAT, LANGUAGE, QUOTED_IDENTIFIER et quelques autres) font partie de la clé du cache, parce que le plan compilé dépend de leur valeur : l’optimiseur évalue par exemple les expressions constantes à la compilation. Deux phénomènes en découlent, qu’on confond souvent.

Le premier : une session qui arrive avec d’autres valeurs ne trouve pas le plan en cache, et SQL Server en compile un second. C’est le cas classique de SSMS, qui ouvre ses sessions avec ARITHABORT à ON, face à une application .NET, ODBC ou OLE DB, où il est à OFF :

USE PachaDataFormation;
SET ARITHABORT ON;                 -- comme SSMS
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;
SET ARITHABORT OFF;                -- comme une application .NET
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;
-- Une entrée de cache par jeu d'options
SELECT cp.usecounts, pa.value AS set_options
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
WHERE st.objectid = OBJECT_ID('Formateur.PrestationsDuFormateur') AND st.dbid = DB_ID()
  AND pa.attribute = 'set_options';

Deux entrées, avec les set_options 4347 et 251, deux compilations « Not a recompile », et aucun événement sql_statement_recompile. DATEFORMAT et LANGUAGE donnent aussi une entrée de plus chacun, avec le même set_options : la différence est dans les attributs date_format et language_id. Avec QUOTED_IDENTIFIER, une procédure garde sa seule entrée, son réglage étant fixé à la création. Le guide d’architecture précise qu’un changement d’options de la session ne déclenche pas de recompilation, et Erland Sommarskog en a tiré l’explication la plus citée du « lent dans l’application, rapide dans SSMS » : Slow in the Application, Fast in SSMS?. Chaque entrée est compilée avec les paramètres de son premier appel, et peut donc avoir un autre plan. La session reste maintenant en ARITHABORT OFF : les exemples suivants s’exécutent dans cette même session, comme le ferait l’application.

Le second : un SET d’une de ces options dans le corps d’une procédure. C’est une vraie recompilation, de cause Set option change. Mais sur 2022 CU26 et 2025 CU7, elle n’a lieu qu’à la première exécution de chaque entrée de cache : avec SET ARITHABORT ON, SET DATEFORMAT dmy ou SET CONCAT_NULL_YIELDS_NULL OFF en tête de procédure, trois appels ont donné une recompilation, puis ont réutilisé le plan recompilé. SET NOCOUNT ON et SET ANSI_NULLS OFF n’en donnent aucune. Fritchey le note dans l’édition 2025 de son livre ; la littérature plus ancienne décrit une recompilation à chaque appel. La page de l’erreur MSSQLSERVER_2814, levée quand des instructions font recompiler un lot au moins cinquante fois, conseille toujours de retirer ces SET des procédures. Un SET ARITHABORT ON dans la procédure ne rapproche pas non plus le plan de l’application de celui de SSMS ; pour reproduire le plan de l’application, réglez plutôt SSMS à ARITHABORT OFF.

La compilation différée et le DDL

Quand une procédure crée une table temporaire, les instructions qui la lisent ne peuvent pas être compilées avant qu’elle existe. SQL Server les compile à leur première exécution, avec la cause Deferred compile, puis réutilise le plan. Depuis le niveau de compatibilité 150, les variables de table suivent la même règle, et cette compilation différée est comptée par SQL Re-Compilations/sec comme par l’événement. La documentation dit qu’elle n’augmente pas la fréquence des recompilations : c’est vrai, elle ne se répète pas.

Un ALTER TABLE placé après la création de la table change les choses. Comparez deux versions de la même procédure, l’une qui ajoute une colonne après le CREATE TABLE, l’autre qui la déclare d’emblée :

USE PachaDataFormation;
GO
CREATE OR ALTER PROCEDURE Formateur.JoursParSession_DDL @FormateurId int
AS
SET NOCOUNT ON;
CREATE TABLE #p (SessionId int NOT NULL, Jours decimal(4,1) NOT NULL);
ALTER TABLE #p ADD DateDebut date NULL;       -- DDL après la création
INSERT INTO #p (SessionId, Jours)
SELECT SessionId, Jours FROM Formateur.Prestation WHERE FormateurId = @FormateurId;
SELECT COUNT(*), SUM(p.Jours), MAX(s.DateDebut)
FROM #p AS p JOIN Stage.Session AS s ON s.SessionId = p.SessionId;
GO
CREATE OR ALTER PROCEDURE Formateur.JoursParSession @FormateurId int
AS
SET NOCOUNT ON;
CREATE TABLE #p (SessionId int NOT NULL, Jours decimal(4,1) NOT NULL, DateDebut date NULL);
INSERT INTO #p (SessionId, Jours)
SELECT SessionId, Jours FROM Formateur.Prestation WHERE FormateurId = @FormateurId;
SELECT COUNT(*), SUM(p.Jours), MAX(s.DateDebut)
FROM #p AS p JOIN Stage.Session AS s ON s.SessionId = p.SessionId;
GO
EXEC Formateur.JoursParSession_DDL @FormateurId = 13;
EXEC Formateur.JoursParSession_DDL @FormateurId = 13;
EXEC Formateur.JoursParSession_DDL @FormateurId = 13;
EXEC Formateur.JoursParSession @FormateurId = 13;
EXEC Formateur.JoursParSession @FormateurId = 13;
EXEC Formateur.JoursParSession @FormateurId = 13;

À la première exécution, les deux procédures compilent leurs instructions en différé. Aux suivantes, JoursParSession ne recompile plus rien, et JoursParSession_DDL recompile à chaque appel, avec la cause Schema changed, une seule instruction : l’ALTER TABLE lui-même. L’INSERT et le SELECT qui le suivent gardent leur plan. Kalen Delaney écrivait en 2012 que les instructions suivant le DDL devaient être recompilées, et le chapitre Optimiser les procédures de mon livre aussi. Pour une table temporaire créée dans la procédure, ce n’est plus ce que j’observe sur 2022 et 2025. Sur une table permanente, c’est toujours vrai : une procédure qui crée puis supprime un index au milieu de ses instructions recompile, à chaque appel, celles qui lisent la table après le CREATE INDEX.

L’autre conséquence est que la table temporaire n’est plus mise en cache : SQL Server la crée et la détruit à chaque appel, comme Paul White l’a décrit dans Temporary Table Caching Explained. Mille appels de chaque procédure, trois fois, sur 2022 CU26 :

ProcédureCPU pour 1 000 appelsTables temporaires crééesRecompilations
JoursParSession_DDL9 764 à 9 968 ms1 0001 000
JoursParSession8 777 à 8 935 ms0 à 30

Environ une milliseconde de plus par appel, 11 % de cette procédure. C’est le prix d’une petite recompilation et d’une création de table ; il grandit avec la complexité de l’instruction recompilée. Sur une table de test, une version qui ajoutait la colonne après avoir rempli la table, puis la mettait à jour, coûtait cinq fois plus que la version corrigée ; l’écart venait presque tout entier de l’UPDATE, qui devait réécrire les lignes, et non de la recompilation. Ventilez le coût par instruction (sys.dm_exec_query_stats) avant de l’attribuer à la recompilation.

La table temporaire d’un appelant

Une procédure peut lire une table temporaire créée par la procédure qui l’appelle. Son plan dépend alors de cette table, et il recompile avec la cause Temp table changed quand la table vient d’un autre appelant :

USE PachaDataFormation;
GO
CREATE OR ALTER PROCEDURE Formateur.TotaliserPrestations
AS
SELECT COUNT(*), SUM(p.Jours), MAX(s.DateDebut)
FROM #p AS p JOIN Stage.Session AS s ON s.SessionId = p.SessionId;
GO
CREATE OR ALTER PROCEDURE Formateur.PrestationsA @FormateurId int
AS
CREATE TABLE #p (SessionId int NOT NULL, Jours decimal(4,1) NOT NULL);
INSERT INTO #p SELECT SessionId, Jours FROM Formateur.Prestation WHERE FormateurId = @FormateurId;
EXEC Formateur.TotaliserPrestations;
GO
CREATE OR ALTER PROCEDURE Formateur.PrestationsB @FormateurId int
AS
CREATE TABLE #p (SessionId int NOT NULL, Jours decimal(4,1) NOT NULL);
INSERT INTO #p SELECT SessionId, Jours FROM Formateur.Prestation WHERE FormateurId = @FormateurId;
EXEC Formateur.TotaliserPrestations;
GO
EXEC Formateur.PrestationsA @FormateurId = 13;
EXEC Formateur.PrestationsA @FormateurId = 13;   -- même appelant : rien
EXEC Formateur.PrestationsB @FormateurId = 13;   -- autre appelant : Temp table changed
EXEC Formateur.PrestationsB @FormateurId = 13;   -- rien
EXEC Formateur.PrestationsA @FormateurId = 13;   -- Temp table changed

Avant SQL Server 2019, la procédure appelée recompilait à chaque exécution ; depuis, des contrôles plus légers évitent la recompilation tant que l’appelant ne change pas (Tables, section sur les tables temporaires). Paul White signale un autre effet de ce partage dans Avoid optional_spid Plan Cache Issues when Sharing Temp Tables : la procédure appelée a une entrée de cache par session.

Le Query Store

Le Query Store fait recompiler une requête quand on change ce qu’il lui applique. Il faut l’identifiant de la requête et de son plan ; choisissez le plan le plus récent, car la même procédure a un query_id par jeu d’options de session, et l’exemple des options SET vient d’en créer deux :

USE PachaDataFormation;
EXEC sp_query_store_flush_db;
DECLARE @query_id bigint, @plan_id bigint;
SELECT TOP (1) @query_id = q.query_id, @plan_id = p.plan_id
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
WHERE q.object_id = OBJECT_ID('Formateur.PrestationsDuFormateur')
ORDER BY p.last_execution_time DESC;

EXEC sp_query_store_force_plan @query_id = @query_id, @plan_id = @plan_id;
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;   -- recompilation
EXEC sp_query_store_unforce_plan @query_id = @query_id, @plan_id = @plan_id;
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;   -- recompilation
EXEC sp_query_store_set_hints @query_id = @query_id, @query_hints = N'OPTION (MAXDOP 1)';
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;   -- recompilation
EXEC sp_query_store_clear_hints @query_id = @query_id;
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;   -- recompilation

Forcer le plan et défaire le forçage donnent chacun une recompilation Query Store plan forcing policy changed, à l’exécution suivante seulement. Poser et retirer un indicateur donnent Query Store hints changed. Le plan forcé était pourtant celui qui était déjà en cache ; la page Comment le Query Store collecte les données ne parle de recompilation que pour un plan forcé différent de celui du cache.

Quand le plan forcé devient impossible, après la suppression d’un index qu’il utilise par exemple, la requête recompile deux fois à l’exécution suivante (Schema changed, puis Query Store plan forcing failed), puis plus du tout : force_failure_count de sys.query_store_plan n’augmente qu’à cette recompilation, comme le dit sa documentation. Le forçage n’empêche pas non plus les recompilations ordinaires.

Les statistiques et les autres causes

Les recompilations dues aux statistiques, Statistics changed, ont leur propre article : Les statistiques et les plans en cache. Elles obéissent à un seuil, et un plan peut vieillir longtemps en dessous.

Avec l’optimisation des plans sensibles aux paramètres (PSP, 2022) et celle des paramètres facultatifs (OPPO, 2025), le plan répartiteur et chaque variante recompilent indépendamment, avec les causes ordinaires. Les causes « Recompiling to clean up the multiplan dispatcher plan » et sa variante OPPO ne sont apparues dans aucun de mes essais, ni après un changement de distribution, ni après sp_recompile, et aucune source ne dit quand elles se déclenchent.

SQL Server 2025 ajoute onze causes. Aucune n’est documentée comme cause de recompilation ; leurs libellés et les messages d’erreur voisins les rattachent au rejeu d’optimisation du forçage de plan, au retour d’expérience persisté dans le Query Store, à la jointure adaptative, à l’incorporation des fonctions scalaires, et à des fonctionnalités de Fabric (cache de jeux de résultats, mode distribué). Je n’en ai provoqué aucune.

Les trois RECOMPILE

Trois options demandent une compilation, et elles n’ont ni la même portée ni le même effet sur le cache. Mesuré sur 2022 et 2025 :

OptionPortéeEffet sur le cacheÉvénement
OPTION (RECOMPILE)une instruction, à chaque exécutiondans une procédure, le plan de la procédure reste en cache, et l’instruction reste visible dans sys.dm_exec_query_stats avec les chiffres de sa seule dernière exécution ; en lot ad hoc ou en sp_executesql, rien n’est mis en cacherecompilation Option (recompile) requested
CREATE PROCEDURE ... WITH RECOMPILEtoute la procédure, à chaque appeljamais en cachecompilation
EXEC ... WITH RECOMPILEtoute la procédure, pour cet appelplan compilé puis jeté ; l’entrée en cache n’est pas touchéecompilation

Le dernier cas se vérifie avec la requête d’état, qui rend ici deux lignes, une par jeu d’options SET :

USE PachaDataFormation;
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13 WITH RECOMPILE;
EXEC Formateur.PrestationsDuFormateur @FormateurId = 13;

Le premier appel laisse usecounts et plan_generation_num tels quels, le second ajoute une utilisation au plan existant. La page d’EXECUTE le dit, le guide d’architecture range au contraire WITH RECOMPILE parmi les conditions qui invalident un plan. C’est ce qui fait l’intérêt de l’option : obtenir le plan d’une valeur atypique sans remplacer le plan en cache. Erik Darling a montré qu’un EXEC sp_executesql ... WITH RECOMPILE ne recompile pas la requête passée à sp_executesql.

Pour OPTION (RECOMPILE) dans une procédure, la page des indicateurs de requête parle d’un plan jeté aussitôt, et Paul White écrit dans Parameter Sniffing, Embedding, and the RECOMPILE Options que le plan reste visible avec les chiffres de la dernière exécution : c’est ce que je mesure, plan_generation_num augmentant d’un à chaque appel. Le Query Store garde, lui, l’historique de ces requêtes.

Le verrou de compilation

Pour qu’une procédure n’ait qu’un plan en cache par jeu d’options, SQL Server sérialise sa compilation : la session qui compile prend un verrou exclusif sur la sous-ressource COMPILE de l’objet, et les autres attendent en LCK_M_X. Un article du support Microsoft décrit ces blocages.

Sur une base de test, j’ai appelé depuis huit sessions synchronisées une procédure longue à compiler, après l’avoir retirée du cache par sp_recompile. Elle a été compilée une fois, et les sept autres sessions ont attendu LCK_M_X sur la sous-ressource COMPILE, jusqu’à 65 ms sur 2022 et 292 ms sur 2025, la durée de la compilation. Après un CREATE INDEX sur sa table, les huit sessions ont recompilé chacune l’instruction, sans attente notable (un relecteur a vu une attente de 5 ms en refaisant l’essai) : les recompilations ne sont pas sérialisées. Arun Marathe l’écrivait déjà pour 2005 dans le livre blanc Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 ; l’édition 2025 du livre de Fritchey dit le contraire, ce que ma mesure contredit.

Dans ces essais, le verrou n’a coûté qu’au moment où beaucoup de sessions demandaient en même temps une procédure absente du cache, ce qui arrive après un redémarrage, un sp_recompile de la procédure ou une éviction. L’article du support cite d’autres cas que je n’ai pas reproduits : l’appel d’une procédure sans nom de schéma, avec une autre casse ou avec le préfixe sp_, et les procédures en WITH RECOMPILE. Avec EXEC ... WITH RECOMPILE, je n’ai vu aucune attente, mais huit compilations pour huit appels. sp_executesql n’a pas attendu non plus, et compile en parallèle dans chaque session ; l’option de base OPTIMIZED_SP_EXECUTESQL de SQL Server 2025 y remédie en appliquant ce verrou à sp_executesql : une compilation et sept attentes dans le même essai. Une session voit ses attentes ainsi :

SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@SPID AND wait_type LIKE N'LCK%';

Limiter les recompilations

  • Déclarez toute la table temporaire dans son CREATE TABLE, index compris (syntaxe d’index en ligne), sans ALTER TABLE ni CREATE INDEX ensuite. C’est aussi la condition de la mise en cache de la table.
  • Gardez les mêmes options SET dans toutes les connexions d’une application, et laissez les procédures sans SET d’options de cache.
  • Pour une table temporaire partagée entre procédures, appelez la procédure lectrice toujours depuis le même appelant.
  • Réservez OPTION (RECOMPILE) aux requêtes dont le plan dépend vraiment des valeurs, en sachant qu’elles recompilent à chaque exécution et que le cache n’en garde que la dernière exécution, dans une procédure, ou rien, en dehors.
  • KEEP PLAN et KEEPFIXED PLAN n’agissent que sur les recompilations dues aux statistiques ; les causes de schéma passent outre.
  • Créez et supprimez les index en dehors des heures chargées : chaque création recompile toutes les requêtes de la table, même celles qui ne l’utiliseront pas.

La documentation Microsoft

Le guide d’architecture du traitement des requêtes, section sur la recompilation des plans, a trois écarts avec ce que je mesure sur 2022 et 2025 : il ne cite que les index utilisés par le plan, il compte l’appel WITH RECOMPILE parmi les invalidations, et sa liste de seize causes est en retard sur les 33 de 2025. La page de sp_recompile ne cite pas les plans ad hoc et sp_executesql, et celle du Query Store ne prévoit pas la recompilation au forçage du plan déjà en cache. Mon livre, aux chapitres Optimiser les procédures et Optimiser le code, décrit les recompilations automatiques et leur trace avec le Profiler, aujourd’hui remplacé par les événements étendus.

Nettoyer

Les exemples ont créé une session d’événements étendus, six procédures et un index, supprimé ensuite. Supprimez la session et les procédures :

USE PachaDataFormation;
DROP EVENT SESSION recompilations ON SERVER;
DROP PROCEDURE IF EXISTS Formateur.PrestationsDuFormateur, Formateur.JoursParSession_DDL,
    Formateur.JoursParSession, Formateur.TotaliserPrestations,
    Formateur.PrestationsA, Formateur.PrestationsB;

Le Query Store garde l’historique de ces procédures, sans forçage ni indicateur, jusqu’à ce que sa politique de nettoyage le retire.