Garder un FULLSCAN sur certaines statistiques avec IndexOptimize

Article coécrit avec Claude, qui a mené les mesures et la lecture du code d’IndexOptimize.

L’échantillon des statistiques

Quand SQL Server calcule une statistique sans qu’on lui dise combien de lignes lire, il lit un échantillon de pages, c’est-à-dire quelques pourcents d’une grosse table. C’est le cas de la mise à jour automatique (AUTO_UPDATE_STATISTICS) et d’un UPDATE STATISTICS sans option. Sur la plupart des colonnes, l’échantillon suffit. Sur une colonne dont les valeurs sont groupées sur les pages, par exemple un identifiant de commande dans une table de lignes écrites à la suite, l’échantillon voit chaque valeur bien plus souvent qu’elle n’apparaît en réalité. Il sous-estime alors le nombre de valeurs distinctes, et surestime le nombre de lignes par valeur. Le chapitre Statistiques et cardinalité explique ce que l’optimiseur fait de ces chiffres.

L’option PERSIST_SAMPLE_PERCENT d’UPDATE STATISTICS existe depuis SQL Server 2016 SP1 CU4 et SQL Server 2017 CU1. Elle enregistre le taux d’échantillonnage d’une statistique, et toutes les mises à jour qui ne précisent pas de taux le reprennent ensuite. Vous voyez le taux enregistré dans la colonne persisted_sample_percent de sys.dm_db_stats_properties.

Le cas qui m’a fait écrire cette page est celui d’un client : une jointure sur six tables, devenue lente un matin. Recalculer les statistiques de la table d’en-tête en FULLSCAN n’a rien changé ; les recalculer sur la table de lignes a rendu le bon plan tout de suite. Restait à le garder sans faire passer toutes les bases en FULLSCAN chaque nuit.

Reproduire le changement de plan

La démonstration utilise la base PachaDataFormation, sur SQL Server 2022 CU26. La table Elearning.Activite contient 7,8 millions de lignes, environ 107 par inscription, écrites à la suite pour chaque inscription. Créez d’abord un index sur la colonne de jointure (une dizaine de secondes) :

USE [PachaDataFormation];
CREATE INDEX nix_activite_inscription ON Elearning.Activite (InscriptionId);

Recalculez sa statistique avec l’échantillon par défaut, puis regardez combien de lignes ont été lues et la densité obtenue :

USE [PachaDataFormation];
UPDATE STATISTICS Elearning.Activite nix_activite_inscription;

SELECT sp.rows, sp.rows_sampled, sp.persisted_sample_percent
FROM sys.dm_db_stats_properties(OBJECT_ID('Elearning.Activite'),
     INDEXPROPERTY(OBJECT_ID('Elearning.Activite'), 'nix_activite_inscription', 'IndexID')) AS sp;

DBCC SHOW_STATISTICS ('Elearning.Activite', nix_activite_inscription) WITH DENSITY_VECTOR;

L’échantillon lit 225 160 lignes sur 7 805 406, soit 2,9 %. La densité vaut 2,97E-5, ce qui correspond à 33 600 inscriptions distinctes, alors que la table en contient 72 578. L’optimiseur compte donc en moyenne 232 activités par inscription, au lieu de 107.

Voici la requête, qui additionne les durées d’activité des inscrits à une série de sessions :

USE [PachaDataFormation];
SET STATISTICS IO, TIME ON;

SELECT SUM(a.DureeSecondes)
FROM Inscription.Inscription AS i
JOIN Elearning.Activite AS a ON a.InscriptionId = i.InscriptionId
WHERE i.SessionId BETWEEN 10000 AND 10020
OPTION (RECOMPILE);

SET STATISTICS IO, TIME OFF;

Affichez le plan réel avant de la lancer (Ctrl+M dans SSMS, ou SET STATISTICS XML ON). Avec l’échantillon par défaut, l’optimiseur estime 185 inscriptions et environ 232 activités pour chacune : 42 910 lignes à la sortie de la jointure. Il choisit une jointure de hachage (Hash Match) et parcourt toute la table Elearning.Activite : 60 043 lectures logiques, 1 000 ms de CPU, 515 ms écoulées. La jointure produit en réalité 16 810 lignes, pour 196 inscriptions.

Trouver la table qui compte

Dans le plan réel, cherchez l’opérateur où les lignes estimées s’éloignent le plus des lignes réelles. Ensuite, recalculez en FULLSCAN les statistiques d’une seule table à la fois, et recompilez la requête entre deux essais. Vous saurez ainsi laquelle compte. Ici, la table d’en-tête n’y est pour rien :

USE [PachaDataFormation];
UPDATE STATISTICS Inscription.Inscription WITH FULLSCAN;
-- relancer la requête : toujours la jointure de hachage et 60 043 lectures sur Activite

La table de détail, elle, change tout :

USE [PachaDataFormation];
UPDATE STATISTICS Elearning.Activite nix_activite_inscription WITH FULLSCAN;
-- relancer la requête

Avec la statistique calculée sur toutes les lignes, l’estimation tombe à 19 879 lignes, proche des 16 810 réelles. L’optimiseur passe à une boucle imbriquée (Nested Loops) : une recherche dans l’index par inscription, puis une recherche de ligne dans le tas (RID Lookup). La requête lit 17 526 pages au lieu de 60 043, et s’exécute en 17 ms au lieu de 515.

La jointure de hachage n’est pas plus rapide en soi : elle l’est quand la jointure utilise une grande partie de la table. Ici, elle lit les 7,8 millions d’activités pour n’en garder que 16 810, soit 0,2 %. La boucle ne touche que ces 16 810 lignes. Le travail réel est d’ailleurs le même quelle que soit l’estimation, puisque la requête renvoie les mêmes lignes : avec l’échantillon par défaut et l’indicateur WITH (FORCESEEK) sur Elearning.Activite, le plan en boucle lit aussi 17 526 pages et s’exécute en 16 à 33 ms. Un simple OPTION (LOOP JOIN) ne suffit pas : il impose la boucle, mais le plan garde le parcours complet de Elearning.Activite (60 043 lectures) et la requête prend 2,5 s, cinq fois plus que le hachage. C’est la recherche dans l’index qui fait gagner la boucle, pas la boucle elle-même. L’optimiseur l’a écartée parce que son modèle de coût compte chaque RID Lookup comme une lecture aléatoire sur disque, et qu’à 42 910 lignes estimées ces lectures coûtaient plus cher que le parcours de la table. En réalité, les pages sont en mémoire, et les activités d’une même inscription, écrites à la suite, tombent dans les mêmes pages. Une surestimation pousse ainsi vers le hachage et le parcours complet ; une sous-estimation pousse vers la boucle, qui devient alors la mauvaise solution.

Un essai ne suffit pas toujours à conclure, parce que le plan ne bascule qu’au-delà d’un seuil. Avec l’échantillon par défaut, l’optimiseur passe au hachage entre 100 et 150 inscriptions estimées ; avec le FULLSCAN, entre 270 et 360. En dessous de 100, les deux statistiques donnent le même plan. Mesurez donc sur la requête réelle, avec ses paramètres réels.

Fixer l’échantillon de cette statistique

Une fois la statistique trouvée, fixez son échantillon. En T-SQL :

USE [PachaDataFormation];
UPDATE STATISTICS Elearning.Activite nix_activite_inscription
WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

Ou avec IndexOptimize, en un appel ponctuel limité à cette statistique par @Indexes (nom en quatre parties : base, schéma, table, index) :

EXECUTE dbo.IndexOptimize
    @Databases = 'PachaDataFormation',
    @FragmentationLow = NULL,
    @FragmentationMedium = NULL,
    @FragmentationHigh = NULL,
    @UpdateStatistics = 'ALL',
    @Indexes = 'PachaDataFormation.Elearning.Activite.nix_activite_inscription',
    @StatisticsSample = 100,
    @StatisticsPersistSample = 'Y';

IndexOptimize (version du 12 septembre 2026) traduit cet appel en UPDATE STATISTICS [Elearning].[Activite] [nix_activite_inscription] WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON. @StatisticsPersistSample ne s’utilise qu’avec @StatisticsSample, jamais avec @StatisticsResample = 'Y', et la procédure le refuse avant SQL Server 2017 CU1. Sur un SQL Server 2016, passez par le T-SQL.

Pour lister les statistiques dont l’échantillon est fixé dans une base :

USE [PachaDataFormation];
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
       OBJECT_NAME(s.object_id) AS table_name,
       s.name AS statistique,
       sp.rows, sp.rows_sampled, sp.persisted_sample_percent
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE sp.persisted_sample_percent > 0;

Le job de maintenance

Le job de nuit ne change pas : un seul appel à IndexOptimize, sans @StatisticsSample. Sans ce paramètre, la procédure écrit UPDATE STATISTICS [schéma].[table] [statistique] sans clause WITH, et SQL Server choisit l’échantillon. Chaque fois que le job met à jour la statistique fixée, il reprend son FULLSCAN ; pour les statistiques sans taux fixé, il prend l’échantillon par défaut. Avec @OnlyModifiedStatistics = 'Y', une statistique dont aucune ligne n’a changé est simplement sautée.

EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y';

Je l’ai vérifié sur les deux tables de la démonstration, avec @Indexes limité à elles : après le passage, nix_activite_inscription a lu ses 7 805 406 lignes, alors que pk_Activite et les statistiques de Inscription.Inscription, qui étaient en FULLSCAN avant le passage, sont revenues à l’échantillon par défaut (225 160 et 83 319 lignes).

Sans PERSIST_SAMPLE_PERCENT, il faudrait deux appels : un premier sur la table qui compte, avec @Indexes = 'PachaDataFormation.Elearning.Activite' et @StatisticsSample = 100, un second sur tout le reste, avec @Indexes = 'ALL_INDEXES, -PachaDataFormation.Elearning.Activite'. Cela fonctionne, mais seulement la nuit : la mise à jour automatique suivante, en journée, revient à l’échantillon par défaut et le plan bascule de nouveau. Le taux fixé sur la statistique, lui, s’applique aussi à la mise à jour automatique.

Ce qui garde l’échantillon, et ce qui l’efface

Mesuré sur SQL Server 2022 CU26, sur une table d’un million de lignes dont une statistique est fixée à 100 % :

La reconstruction d’index gardait mal le taux sur les anciennes versions : la documentation indique qu’il n’est conservé que depuis SQL Server 2016 SP2 CU17, 2017 CU26 et 2019 CU10. Et la documentation dit qu’un TRUNCATE ramène les statistiques à l’échantillon par défaut. Sur 2022 CU26, je mesure que le taux survit au TRUNCATE : il ne disparaît que si une mise à jour a lieu pendant que la table est vide. Sur une table vidée puis rechargée par un traitement, vérifiez persisted_sample_percent après le premier chargement.

Attention aussi à @StatisticsSample dans le job de nuit : avec @StatisticsPersistSample = 'N', IndexOptimize ajoute PERSIST_SAMPLE_PERCENT = OFF à chaque statistique et efface les taux fixés.

Le coût

Sur Elearning.Activite, la mise à jour de la statistique dure 2,2 secondes en FULLSCAN, et 0,2 seconde avec un échantillon de 3 %. La nuit, c’est négligeable. En journée, la mise à jour automatique est synchrone par défaut : elle se fait pendant la compilation de la requête qui a vu la statistique périmée, et cette requête attend la fin du FULLSCAN. Cette attente grandit avec la table.

Réservez donc le taux fixé aux statistiques dont vous avez mesuré l’effet sur un plan, pas à toute une base. Si l’attente pose problème, l’option de base AUTO_UPDATE_STATISTICS_ASYNC fait recalculer la statistique en arrière-plan : la requête compile avec l’ancienne, et la suivante profite de la nouvelle.

Le hint du Query Store

En attendant de trouver la bonne table, on pose souvent un hint sur la requête par le Query Store, avec sys.sp_query_store_set_hints (SQL Server 2022 et suivants). Une fois les statistiques corrigées, il ne sert peut-être plus à rien. Avant de le retirer, notez son texte, pour pouvoir le remettre :

USE [PachaDataFormation];
SELECT query_hint_id, query_id, query_hint_text, source_desc,
       last_query_hint_failure_reason_desc
FROM sys.query_store_query_hints;

Retirez-le ensuite, en remplaçant 42 par le query_id de la requête :

USE [PachaDataFormation];
EXEC sys.sp_query_store_clear_hints @query_id = 42;

Comparez enfin le plan et la durée des exécutions suivantes avec celles d’avant, dans le Query Store. Si le plan reste bon, le hint est inutile. S’il se dégrade, remettez-le avec sys.sp_query_store_set_hints et le texte noté : il garde alors la requête dans une forme que les statistiques seules ne donnent pas. Gardez-le, en le réévaluant quand la distribution des données change : un hint s’applique à toutes les exécutions de la requête, quels que soient ses paramètres.

Nettoyer

Pour finir, supprimez l’index de la démonstration :

USE [PachaDataFormation];
DROP INDEX IF EXISTS nix_activite_inscription ON Elearning.Activite;