Les statistiques et les plans en cache

Quand une mise à jour de statistiques fait-elle recompiler un plan en cache, et quand un plan devenu mauvais reste-t-il en mémoire ? Mesures sur SQL Server 2022 et 2025.

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

Le contrôle des statistiques à l’exécution

Un plan d’exécution est compilé une fois et réutilisé tant qu’il reste dans le cache. Quand une instruction prend son plan dans le cache, SQL Server vérifie d’abord qu’il est encore valide, puis qu’il est encore optimal. La page sur les statistiques le dit ainsi : avant d’exécuter un plan en cache, le moteur vérifie que le plan utilise des statistiques à jour. Si ce n’est plus le cas, il recompile cette instruction seule : depuis SQL Server 2005, la recompilation se fait instruction par instruction. L’événement étendu sql_statement_recompile donne alors la cause Statistics changed.

Le mécanisme est décrit dans le livre blanc de Microsoft Plan Caching and Recompilation in SQL Server 2012, qui vaut pour 2005 à 2012, et rien de ce que j’ai mesuré sur 2022 et 2025 ne le contredit. À la compilation, le plan garde la liste des statistiques qu’il a chargées et, pour chacune, une copie du compteur de modifications de sa colonne. À l’exécution, il compare le compteur actuel à cette copie, et recompile quand l’écart atteint le seuil de recompilation de la table.

Ce compteur est exposé depuis SQL Server 2008 R2 SP2 et 2012 SP1 par la colonne modification_counter de sys.dm_db_stats_properties. Il compte les modifications de la première colonne de la statistique depuis sa dernière mise à jour, et il a trois particularités, mesurées sur 2022 CU26 et 2025 CU7 :

  • il compte une modification par ligne écrite, y compris quand la valeur ne change pas (UPDATE ... SET k = k), et une seule par ligne pour une colonne clé d’index ;
  • il n’est pas transactionnel : une insertion de 100 lignes annulée par ROLLBACK l’augmente de 200 ;
  • sur une statistique de plusieurs colonnes, seule la première compte : 50 000 modifications de la deuxième colonne d’une statistique sur (a, b) l’ont laissé à zéro.

Les seuils

Le seuil de recompilation est le même que celui qui déclare une statistique périmée pour la mise à jour automatique. La page sur les statistiques en donne la table, avec n le nombre de lignes de la table :

TableJusqu’au niveau 120Depuis le niveau 130
temporaire, moins de 6 lignes66
temporaire, 6 à 500 lignes500500
permanente, 500 lignes ou moins500500
plus de 500 lignes500 + 20 % de nle plus petit de 500 + 20 % de n et de la racine carrée de 1 000 × n

Au niveau 120 et en dessous, l’indicateur de trace 2371 donne le seuil dynamique de la dernière colonne.

J’ai cherché le seuil exact en modifiant les lignes une à une. Sur une table de 100 000 lignes au niveau 160, l’exécution qui suit la 9 999e modification réutilise le plan, et celle qui suit la 10 000e met la statistique à jour puis recompile : le seuil se déclenche à l’égalité. À 1 000 000 de lignes, 31 622 modifications suffisent, la racine carrée tronquée. Au niveau 120, 20 500 et 200 500. Les mêmes valeurs sur 2022 et 2025, deux fois chacune.

Le n de la formule est le nombre de lignes enregistré dans la statistique à sa dernière mise à jour (la colonne rows de sys.dm_db_stats_properties). La documentation parle du nombre de lignes « au moment où les statistiques ont été évaluées », le livre blanc de celui de la compilation. J’ai compilé le plan après 9 000 insertions, puis après 9 000 suppressions, dans une table dont la statistique avait été mise à jour à 100 000 lignes : dans les deux cas, la mise à jour et la recompilation sont venues à 10 000 modifications, la valeur calculée sur 100 000.

Une recompilation n’arrive qu’à l’exécution d’un plan. Une table modifiée mais jamais interrogée garde sa statistique périmée et ses plans tels quels jusqu’à la requête suivante.

La mise à jour asynchrone

Avec AUTO_UPDATE_STATISTICS_ASYNC à ON, la requête qui atteint le seuil ne recompile pas : elle exécute le plan en cache et met la mise à jour en file. L’événement auto_stats le montre avec l’état « Queued ». La recompilation vient à la première exécution qui suit la fin de la tâche de fond, avec la cause Statistics changed. Sur une table de 1 000 000 de lignes, les trois exécutions enchaînées après le seuil ont toutes réutilisé l’ancien plan, et la quatrième a recompilé. Kalen Delaney écrit dans Microsoft SQL Server 2012 Internals que la requête recompile alors avec les anciennes statistiques ; ce n’est pas ce que j’observe sur 2022 et 2025. L’article REORGANIZE et statistiques asynchrones détaille le mode asynchrone.

La mise à jour manuelle

UPDATE STATISTICS, sp_updatestats et la reconstruction d’un index recalculent une statistique sans attendre le seuil. Recompilent-ils les plans qui la lisent ? La reconstruction d’index, toujours, comme on le verra plus bas. Les deux autres, à une condition : que le compteur de la statistique n’ait pas été à zéro au moment de la mise à jour.

L’exemple ci-dessous le montre sur Marketing.Envoi, la table des envois de campagnes de PachaDataFormation. La requête joint les envois d’une campagne aux contacts. Exécutez d’abord la requête et la lecture de son état :

USE PachaDataFormation;
DECLARE @q nvarchar(300) = N'SELECT COUNT(*), MAX(c.Nom)
FROM Marketing.Envoi AS e
JOIN Contact.Contact AS c ON c.ContactId = e.ContactId
WHERE e.CampagneId = @c;';
EXEC sp_executesql @q, N'@c int', @c = 12623;
-- Génération du plan en cache et compteur de modifications de la statistique
SELECT qs.plan_generation_num, qs.execution_count, sp.modification_counter
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_db_stats_properties(OBJECT_ID('Marketing.Envoi'),
    INDEXPROPERTY(OBJECT_ID('Marketing.Envoi'), 'ix_Envoi_CampagneId', 'IndexID')) AS sp
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
WHERE st.text LIKE N'(@c int)SELECT COUNT(*), MAX(c.Nom)%'
  AND pa.attribute = 'dbid' AND CAST(pa.value AS int) = DB_ID();

plan_generation_num vaut 1. Mettez la statistique à jour sur toutes les lignes, puis relancez le bloc précédent :

USE PachaDataFormation;
UPDATE STATISTICS Marketing.Envoi ix_Envoi_CampagneId WITH FULLSCAN;

Le plan est réutilisé : génération 1, deux exécutions. Aucune ligne n’avait changé depuis la mise à jour précédente, le compteur était à zéro. Modifiez maintenant une ligne, sans changer sa valeur, et refaites la mise à jour :

USE PachaDataFormation;
UPDATE Marketing.Envoi SET CampagneId = CampagneId WHERE EnvoiId = 1;
UPDATE STATISTICS Marketing.Envoi ix_Envoi_CampagneId WITH FULLSCAN;

Le bloc de la requête donne cette fois la génération 2 : une seule écriture a suffi. Ce comportement est connu depuis 2012 : Kimberly Tripp l’avait d’abord pris pour un bogue avant que Microsoft le déclare voulu, et un billet du support Microsoft le résume : sans changement de données, pas besoin de recompiler. Il surprend quand on met à jour une statistique en FULLSCAN pour corriger une estimation : l’histogramme est meilleur, le plan en cache ne change pas. L’article Ce que change un FULLSCAN des statistiques est tombé dessus.

J’avais d’abord écrit l’exemple avec un simple COUNT(*), et rien ne recompilait. La clé étrangère de Marketing.Envoi vers Contact.Contact permet à l’optimiseur de supprimer la jointure, et il ne reste qu’une recherche dans l’index de CampagneId : un plan trivial (StatementOptmLevel="TRIVIAL" dans le XML du plan), que les statistiques ne font jamais recompiler. MAX(c.Nom) oblige à lire les contacts, donc à une optimisation complète.

J’ai mesuré les autres opérations sur une table de test, avec le compteur à 0 puis à 1, sur 2022 et 2025 et pour trois formes de la même requête (lot ad hoc, sp_executesql, procédure stockée), qui se sont toujours comportées de la même façon :

OpérationCompteur à 0Compteur à 1
UPDATE STATISTICS, en FULLSCAN, échantillon, RESAMPLE ou sur toute la tablepas de recompilationStatistics changed
UPDATE STATISTICS ... WITH NORECOMPUTEpas de recompilationStatistics changed
UPDATE STATISTICS d’une statistique que le plan ne lit paspas de recompilationpas de recompilation
sp_updatestatsstatistique ignoréestatistique mise à jour, Statistics changed
ALTER INDEX ... REBUILDSchema changedSchema changed
ALTER INDEX ... REORGANIZEpas de recompilationpas de recompilation
CREATE STATISTICS, même sur une colonne du prédicatpas de recompilationpas de recompilation
DROP STATISTICS, même d’une statistique que le plan ne lit pasSchema changedSchema changed
sp_recompile sur la tableSchema changedSchema changed

Sur une table sur disque, sp_updatestats met à jour une statistique dès que son compteur dépasse zéro, comme le dit sa page de référence, et non au-delà du seuil de la mise à jour automatique. La reconstruction d’un index recompile toujours, mais au titre d’un changement de schéma, comme la suppression d’une statistique.

Les plans exclus du contrôle

Trois sortes de plans échappent au contrôle. Ils ne recompilent pas pour les statistiques, et ils ne déclenchent pas non plus la mise à jour automatique quand le seuil est atteint : si seules ces requêtes lisent une statistique, elle reste périmée.

  • Le plan trivial, que l’optimiseur choisit quand il n’existe qu’un plan raisonnable. Le livre blanc le dit : recompiler serait inutile. Après 10 000 modifications sur 100 000 lignes, en mode synchrone comme asynchrone, la statistique n’a pas été mise à jour, et aucun événement auto_stats n’a été émis.

  • Le plan avec OPTION (KEEPFIXED PLAN). Il ne recompile qu’à un changement de schéma, sp_recompile ou ALTER INDEX ... REBUILD par exemple.

  • Le plan compilé alors que la statistique était en NORECOMPUTE, ou désactivée par sp_autostats. Ce plan ne recompile plus pour les statistiques, même après un UPDATE STATISTICS sans NORECOMPUTE qui a remis la statistique en mise à jour automatique : c’est l’état au moment de la compilation qui compte. Seuls sp_recompile ou l’éviction du plan l’en sortent. Deux cas voisins obéissent à d’autres règles :

  • La variable de table n’a pas de statistiques. Depuis le niveau 150, la compilation différée attend la première exécution pour connaître son nombre de lignes, puis le garde : compilé à 2 000 lignes, le plan est resté pour 1, puis pour 50 000.

  • La table temporaire mise en cache dans une procédure : un UPDATE STATISTICS #t ne recompile pas le plan qui la lit, comme Paul White l’a décrit pour 2005 à 2022 dans Temporary Table Caching in Stored Procedures, et c’est encore vrai sur 2025. Le plan recompile en revanche quand le nombre de lignes de la table s’écarte de celui de la compilation d’au moins le seuil (6, puis 500, puis la formule), à la hausse comme à la baisse, avec la cause Statistics changed.

AUTO_UPDATE_STATISTICS à OFF sur la base arrête les recompilations dues au seuil, avec la mise à jour automatique. Il n’empêche plus celles d’une mise à jour manuelle : une ligne modifiée et un UPDATE STATISTICS ont recompilé le plan sur 2022 et 2025. Erin Stellato observait le contraire sur 2008 R2, et le guide d’architecture du traitement des requêtes dit encore que l’option à OFF arrête ces recompilations. Notez aussi que changer cette option par ALTER DATABASE a vidé le cache des plans de la base.

Un plan qui vieillit sous le seuil

Entre deux recompilations, les données changent et le plan reste. Sur une grande table, le seuil est loin : 63 390 modifications pour les 4 018 304 lignes de Marketing.Envoi. Une campagne de masse, envoyée d’un coup, peut rester en dessous.

L’exemple suivant compile la requête pour la dernière campagne, 17390, qui a 7 envois, puis ajoute 60 000 envois à cette campagne dans une transaction qu’il annule à la fin. Il demande les plans réels (dans SSMS, activez aussi « Inclure le plan d’exécution réel ») :

USE PachaDataFormation;
DECLARE @q nvarchar(300) = N'SELECT COUNT(*), MAX(c.Nom)
FROM Marketing.Envoi AS e
JOIN Contact.Contact AS c ON c.ContactId = e.ContactId
WHERE e.CampagneId = @c;';
EXEC sp_executesql @q, N'@c int', @c = 17390;     -- compilation : 7 envois

BEGIN TRANSACTION;
INSERT INTO Marketing.Envoi (CampagneId, ContactId, DateEnvoi, Ouvert, Clic)
SELECT TOP (60000) 17390, ContactId, SYSDATETIME(), 0, 0
FROM Contact.Contact;

SET STATISTICS XML ON;
EXEC sp_executesql @q, N'@c int', @c = 17390;     -- plan en cache, 60 007 envois
EXEC sp_recompile N'Marketing.Envoi';
EXEC sp_executesql @q, N'@c int', @c = 17390;     -- recompilé, statistique inchangée
UPDATE STATISTICS Marketing.Envoi ix_Envoi_CampagneId WITH FULLSCAN;
EXEC sp_executesql @q, N'@c int', @c = 17390;     -- recompilé, statistique à jour
SET STATISTICS XML OFF;
ROLLBACK;

Résultats sur 2022 CU26, plans réels, trois passages : plans, estimations et lectures identiques, CPU variable d’un passage à l’autre :

ÉtapePlanEstimé / réelLectures logiquesCPU
compilationboucles imbriquées, recherche de chaque contact7 / 745< 1 ms
60 000 envois ajoutés, plan en cachele même7 / 60 007360 254274 à 376 ms
après sp_recompilele même7,1 / 60 007360 254261 à 347 ms
après UPDATE STATISTICShachage, parcours des deux tables, parallèle60 007 / 60 00722 454576 à 581 ms (313 ms écoulées)

Le plan conservé fait seize fois plus de lectures logiques que le plan recompilé. La durée, elle, ne s’améliore pas ici : les tables sont en mémoire, et le hachage parallèle consomme plus de CPU. Sur une copie plus compacte de la table, un relecteur a mesuré l’inverse : 93 à 107 ms pour le hachage, 246 à 312 ms pour la boucle. Je n’ai pas mesuré le cas d’un serveur dont les pages ne tiennent pas en mémoire, où une partie de ces lectures se ferait sur disque.

sp_recompile ne sert à rien dans ce cas. La valeur 17390 est la borne du dernier pas de l’histogramme, avec ses 7 lignes. Recompilée sur la même statistique, la requête retrouve 7 lignes, à peine ajustées à la croissance de la table. Il faut une statistique à jour.

Avec une campagne nouvelle, absente de l’histogramme (17391, insérée dans Marketing.Campagne dans la même transaction), la première compilation estime une ligne, et la recompilation par sp_recompile, au niveau 160, en estime 244,6, la moyenne par valeur donnée par la densité (4 018 304 lignes pour 16 426 campagnes). La boucle imbriquée reste. Au niveau 170 de SQL Server 2025, sur une copie de la table, un relecteur a obtenu 3,65, soit les 60 000 modifications multipliées par la densité : l’estimation d’une valeur absente de l’histogramme dépend de la version et du compteur. Sur une table de test, avec un prédicat d’intervalle (d >= @d) au-delà du dernier pas, la recompilation seule avait suffi : l’optimiseur a estimé 30 % du compteur de modifications et choisi un parcours. Avec l’estimation de cardinalité héritée (LEGACY_CARDINALITY_ESTIMATION = ON), elle avait gardé la boucle.

Le seuil finit par remplacer le plan : sur la table de test, la 10 000e insertion a fait mettre la statistique à jour et recompiler à l’exécution suivante. Les indicateurs qui corrigent l’estimation des clés croissantes (2389, 2390, 4139) n’agissent qu’à la compilation, et ne changent rien à un plan resté en cache.

Observer les recompilations

Une session d’événements étendus donne la cause de chaque recompilation et les mises à jour automatiques. Elle filtre sur l’identifiant de la base, et se crée donc par un EXEC. Le champ database_name de l’événement auto_stats n’est rempli qu’avec SET collect_database_name = 1, et un prédicat global sur le nom de la base perd les mises à jour asynchrones, faites en tâche de fond :

USE PachaDataFormation;
-- Session d'événements étendus : causes de recompilation et mises à jour automatiques
DECLARE @s nvarchar(max) = N'CREATE EVENT SESSION recompilations ON SERVER
ADD EVENT sqlserver.sql_statement_recompile (SET collect_statement = 1
    WHERE sqlserver.database_id = ' + CAST(DB_ID() AS nvarchar(10)) + N'),
ADD EVENT sqlserver.auto_stats (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(40)') AS evenement,
       e.n.value('(data[@name="recompile_cause"]/text)[1]', 'varchar(60)') AS cause,
       e.n.value('(data[@name="status"]/text)[1]', 'varchar(40)') AS etat,
       e.n.value('(data[@name="statistics_list"]/value)[1]', 'nvarchar(200)') AS statistiques,
       LEFT(e.n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)'), 80) 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);

L’événement auto_stats est bavard : il signale chaque chargement de statistique à la compilation (« Loading without updating »). Les lignes utiles disent « Loading and updating » et « Updated », ou, en asynchrone, l’état « Queued » puis « Updated Async stats ». Sur la requête de l’exemple, au niveau 160, chaque recompilation produit deux événements Statistics changed : la répartition de CampagneId est très inégale, et l’optimisation des plans sensibles aux paramètres (PSP) garde un plan répartiteur et une variante. L’UPDATE d’une ligne de l’exemple, auto-paramétré, produit lui aussi un événement, Parameterized plan flushed.

Une mise à jour manuelle et un seuil atteint donnent la même cause, Statistics changed. La reconstruction d’index, DROP STATISTICS, sp_autostats et sp_recompile donnent Schema changed. La liste des causes (sys.dm_xe_map_values, name = 'statement_recompile_cause') compte 23 valeurs sur 2022 CU26 et 34 sur 2025 CU7.

Dans le cache, plan_generation_num augmente d’un à chaque recompilation de l’instruction, et execution_count repart à 1. Le Query Store ne crée pas de nouveau plan quand la recompilation donne la même forme : il augmente count_compiles du plan existant. Une forme différente donne un nouveau plan_id. Le Query Store écrit ses données de façon asynchrone : pour une mesure, attendez quelques secondes et appelez sp_query_store_flush_db.

Supprimez la session à la fin :

DROP EVENT SESSION recompilations ON SERVER;

La documentation Microsoft

Le guide d’architecture du traitement des requêtes, section sur la recompilation des plans, compte la mise à jour des statistiques par UPDATE STATISTICS parmi les causes d’invalidation d’un plan, et la page Mettre à jour les statistiques écrit que la mise à jour des statistiques entraîne la recompilation des requêtes. Les pages de référence d’UPDATE STATISTICS et des statistiques sont plus prudentes : la mise à jour « peut » entraîner une recompilation. La page de sp_updatestats cite le cas du plan trivial, aucune celui du compteur à zéro. Le chapitre Statistiques et cardinalité de mon livre décrit la vérification des statistiques à l’exécution, et le chapitre Optimiser les procédures le seuil de recompilation, les tables temporaires et KEEPFIXED PLAN.

Nettoyer

L’exemple du plan qui vieillit annule ses insertions, mais pas le reste. Le UPDATE STATISTICS fait dans la transaction n’est pas annulé : la statistique de CampagneId décrit encore les 60 000 envois disparus. Les compteurs ne sont pas transactionnels non plus : l’insertion et son annulation ont ajouté 120 000 modifications aux autres statistiques de la table, au-delà du seuil, et la prochaine compilation les mettrait à jour par échantillon. Recalculez-les sur les données réelles :

USE PachaDataFormation;
UPDATE STATISTICS Marketing.Envoi ix_Envoi_CampagneId WITH FULLSCAN;
UPDATE STATISTICS Marketing.Envoi ix_Envoi_ContactId WITH FULLSCAN;
UPDATE STATISTICS Marketing.Envoi pk_Envoi;