Ce que change un FULLSCAN des statistiques

Un UPDATE STATISTICS en FULLSCAN corrige-t-il les estimations ? Mesure sur trois colonnes, selon que la requête lit la densité ou l’histogramme.

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

Les deux parties d’une statistique

Une statistique SQL Server contient un vecteur de densité et un histogramme. Le vecteur de densité donne, pour la colonne (et pour chaque préfixe de colonnes d’un index), la densité, c’est-à-dire l’inverse du nombre de valeurs distinctes. L’histogramme découpe la colonne en 200 pas au plus, plus un pas pour NULL si la colonne en contient ; pour chaque pas, il indique la valeur haute (RANGE_HI_KEY), le nombre de lignes égales à cette valeur (EQ_ROWS), le nombre de lignes dans l’intervalle qui précède (RANGE_ROWS), le nombre de valeurs distinctes de cet intervalle (DISTINCT_RANGE_ROWS) et le nombre moyen de lignes par valeur (AVG_RANGE_ROWS). La page sur les statistiques les décrit, et DBCC SHOW_STATISTICS les affiche.

L’optimiseur ne lit pas la même partie selon la requête :

  • quand il connaît la valeur à la compilation (une constante, un paramètre de procédure, une variable avec OPTION (RECOMPILE)), il lit l’histogramme : EQ_ROWS si la valeur est la borne d’un pas, AVG_RANGE_ROWS sinon (RANGE_ROWS si l’intervalle a moins d’une valeur distincte, et jamais moins d’une ligne), et, pour un intervalle, les pas couverts, au prorata pour ceux des extrémités ;
  • quand il ne la connaît pas (une variable locale sans RECOMPILE, OPTIMIZE FOR UNKNOWN), il estime une égalité en multipliant la densité par le nombre de lignes ; quand la colonne accepte NULL, il retire les NULL des deux côtés, puisqu’une égalité ne les trouve jamais ;
  • pour un GROUP BY sur la colonne, il estime le nombre de groupes à partir de la densité.

Sans option, UPDATE STATISTICS et la mise à jour automatique lisent un échantillon de pages, quelques pourcents d’une grosse table. L’article Statistiques échantillonnées explique pourquoi cet échantillon se trompe sur une colonne dont les valeurs sont groupées sur les pages : il voit chaque valeur plus souvent qu’elle n’apparaît, et sous-estime le nombre de valeurs distinctes. Ce nombre sert à la densité, mais aussi à DISTINCT_RANGE_ROWS, donc à AVG_RANGE_ROWS : l’histogramme se trompe en même temps.

Je voulais savoir ce que le FULLSCAN change, partie par partie, sur des colonnes réelles. Les mesures viennent de SQL Server 2022 CU26, édition Developer sous Linux, base PachaDataFormation au niveau de compatibilité 160.

Les colonnes de l’essai

J’ai choisi trois colonnes dont les valeurs sont disposées différemment dans leur table :

  • Marketing.Envoi.CampagneId : les envois d’une campagne sont écrits à la suite. La table contient 36 lettres d’information de 107 994 envois chacune et 16 390 campagnes d’évaluation de quelques envois ;
  • Elearning.Activite.InscriptionId : les activités d’une inscription sont écrites à la suite, 107 en moyenne ;
  • Elearning.Activite.ModuleId : les modules sont dispersés dans la table, de 170 à 49 887 lignes par module, et 1,2 million de lignes sans module (NULL).

Marketing.Envoi a déjà un index sur CampagneId. Pour les deux autres, créez les index (une vingtaine de secondes) :

USE PachaDataFormation;
CREATE INDEX nix_activite_inscription ON Elearning.Activite (InscriptionId);
CREATE INDEX nix_activite_module ON Elearning.Activite (ModuleId);

Un index se crée avec une statistique calculée sur toutes les lignes. Recalculez-la avec l’échantillon par défaut, puis comparez la densité au nombre réel de valeurs :

USE PachaDataFormation;
UPDATE STATISTICS Marketing.Envoi ix_Envoi_CampagneId;

SELECT sp.rows, sp.rows_sampled, sp.steps
FROM sys.dm_db_stats_properties(OBJECT_ID('Marketing.Envoi'),
     INDEXPROPERTY(OBJECT_ID('Marketing.Envoi'), 'ix_Envoi_CampagneId', 'IndexID')) AS sp;

DBCC SHOW_STATISTICS ('Marketing.Envoi', ix_Envoi_CampagneId) WITH DENSITY_VECTOR;  -- 1 / densité

SELECT COUNT(DISTINCT CampagneId) AS valeurs_reelles FROM Marketing.Envoi;

Faites de même pour les deux statistiques de Elearning.Activite, puis recommencez après un UPDATE STATISTICS ... WITH FULLSCAN. L’échantillon lit 256 704 lignes sur 4 018 304 dans Marketing.Envoi (6,4 %), et 225 160 sur 7 805 406 dans Elearning.Activite (2,9 %).

ColonneValeurs réellesEstimées, échantillonEstimées, FULLSCAN
CampagneId (groupées)16 4261 19316 426
InscriptionId (groupées)72 57833 36372 578
ModuleId (dispersées)989993989

L’échantillon se trompe d’un facteur 14 sur les campagnes et d’un facteur 2 sur les inscriptions. Sur les modules, il est juste. La densité compte NULL comme une valeur : 988 modules plus NULL.

Le groupement qui compte est celui de la table, pas celui de l’index. Dans ix_Envoi_ContactId, les envois d’un contact sont triés côte à côte, mais ils sont dispersés dans la table (35 par contact en moyenne) : la densité échantillonnée donne 113 368 contacts pour 113 841 réels.

Mesurer la qualité d’un histogramme

La densité se compare en une requête. Pour l’histogramme, il faut l’estimation de chaque valeur. Le script suivant la reconstruit à partir des pas, comme l’optimiseur le fait pour une égalité sur une constante, et la compare au nombre réel de lignes de chaque valeur. J’ai vérifié la reconstruction sur 48 valeurs tirées au hasard dans les trois statistiques, avec et sans FULLSCAN : elle donnait chaque fois l’estimation du plan.

USE PachaDataFormation;

-- Histogramme de la statistique
CREATE TABLE #h (
    hi int, range_rows float, eq_rows float,
    distinct_range_rows float, avg_range_rows float);
INSERT INTO #h
EXEC ('DBCC SHOW_STATISTICS (''Marketing.Envoi'', ix_Envoi_CampagneId) WITH HISTOGRAM, NO_INFOMSGS');

-- Estimation d'une égalité pour chaque valeur réelle, comparée au nombre réel de lignes
WITH reel AS (
    SELECT CampagneId AS v, COUNT(*) AS n
    FROM Marketing.Envoi
    GROUP BY CampagneId
), estime AS (
    SELECT r.v, r.n,
           CASE WHEN p.hi = r.v THEN p.eq_rows
                WHEN p.distinct_range_rows < 1 THEN IIF(p.range_rows < 1, 1, p.range_rows)
                ELSE p.avg_range_rows END AS est
    FROM reel AS r
    OUTER APPLY (SELECT TOP (1) * FROM #h WHERE hi >= r.v ORDER BY hi) AS p
)
SELECT COUNT(*) AS valeurs,
       SUM(CASE WHEN est IS NULL THEN 1 ELSE 0 END) AS apres_le_dernier_pas,
       -- pourcentages calculés sur les valeurs couvertes par l'histogramme
       CAST(AVG(CASE WHEN est IS NULL THEN NULL WHEN est / n BETWEEN 0.5 AND 2 THEN 100.0 ELSE 0 END) AS decimal(5,1)) AS pct_a_deux_pres,
       CAST(AVG(CASE WHEN est IS NULL THEN NULL WHEN est / n > 2 THEN 100.0 ELSE 0 END) AS decimal(5,1)) AS pct_surestimees,
       CAST(AVG(CASE WHEN est IS NULL THEN NULL WHEN est / n < 0.5 THEN 100.0 ELSE 0 END) AS decimal(5,1)) AS pct_sous_estimees
FROM estime;

DROP TABLE #h;

Le script ne reconstruit pas l’estimation d’une valeur située après le dernier pas : il la compte à part. Pour une autre statistique, changez le nom de la table, de la statistique et de la colonne. Si la colonne accepte NULL, ajoutez WHERE ... IS NOT NULL dans reel : sinon le groupe NULL est compté parmi les valeurs après le dernier pas.

ColonneValeurs estimées à un facteur deux près, échantillonMême mesure, FULLSCAN
CampagneId0,2 % (94,5 % surestimées)98,7 %
InscriptionId42,8 % (57,2 % surestimées)80,4 %
ModuleId84,8 %85,5 %

Avec l’échantillon, l’histogramme surestime 94,5 % des campagnes et 57,2 % des inscriptions. Le FULLSCAN améliore les deux, sans supprimer toutes les erreurs sur les inscriptions. Avec l’échantillon, les 233 campagnes les plus récentes tombent en plus après le dernier pas : la campagne 17300, qui a 9 envois, est estimée à 2 005.

Sur ModuleId, le FULLSCAN change peu la proportion d’estimations justes, et les erreurs qui restent ne viennent pas de l’échantillon : 200 pas ne décrivent pas 988 valeurs. Un module qui n’est pas la borne d’un pas reçoit la moyenne de son intervalle. Le module 990, qui a 439 lignes, est estimé à 758 avec l’échantillon et à 2 589 en FULLSCAN, parce que son intervalle a changé.

L’effet sur les requêtes

Une estimation plus juste ne change le plan que si elle franchit un seuil où l’optimiseur préfère un autre opérateur. Voici ce que j’ai mesuré, plans réels, en recompilant à chaque fois, avec MAXDOP 1 pour les regroupements :

RequêtePartie lueÉchantillonFULLSCANRéelPlan
Envois d’une campagne avec leurs contacts, variabledensité2 334 à 3 4002452change (ci-dessous)
Même requête, constantehistogramme822identique
InscriptionId = 100002histogramme246112123identique
InscriptionId = @idensité232108123identique
InscriptionId BETWEEN 100000 AND 100999histogramme77 12887 88089 068identique
GROUP BY InscriptionIddensité33 363 groupes72 57872 578identique, sans débordement en FULLSCAN
GROUP BY CampagneIddensité1 193 groupes16 42616 426identique
ModuleId = 992, 119, 990histogramme483, 1 203, 758170, 1 529, 2 589170, 275, 439identique

L’intervalle sur InscriptionId est bien estimé dans les deux cas : RANGE_ROWS est compté en lignes, sans passer par le nombre de valeurs distinctes. Je ne l’ai mesuré que sur cet intervalle.

Le regroupement par inscription calcule la durée totale de chaque inscription (le SUM oblige à lire la table : avec un simple COUNT(*), l’optimiseur se contente de l’index, sans allocation de mémoire) :

USE PachaDataFormation;
SELECT InscriptionId, SUM(DureeSecondes) AS duree
INTO #g
FROM Elearning.Activite
GROUP BY InscriptionId
OPTION (RECOMPILE, MAXDOP 1, USE HINT('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));
DROP TABLE #g;

Estimé à 33 363 groupes avec l’échantillon, il reçoit 6,9 Mo de mémoire et déborde dans tempdb : 1,8 à 1,9 s. En FULLSCAN, il reçoit 11,5 Mo, ne déborde plus et dure 1,6 s. Le regroupement par campagne, lui, ne déborde dans aucun des deux cas (0,7 s).

La requête qui cherche les envois d’une campagne dans une variable change de plan :

USE PachaDataFormation;
DECLARE @c int = 12623;

SELECT c.Nom, c.Prenom, e.DateEnvoi, e.Ouvert
FROM Marketing.Envoi AS e
JOIN Contact.Contact AS c ON c.ContactId = e.ContactId
WHERE e.CampagneId = @c;

La campagne 12623 a deux envois. Avec la statistique échantillonnée, l’optimiseur ignore la valeur de @c et compte la moyenne donnée par la densité : 2 334 envois dans un tirage, 3 368 et 3 400 dans d’autres. Pour autant de lignes, il trie les envois et les fusionne (Merge Join) avec la table Contact.Contact, parcourue presque en entier (119 638 lignes sur 120 140) : 2 127 lectures logiques sur Contact et 29 à 54 ms de CPU. En FULLSCAN, la densité donne 245 envois, et l’optimiseur passe à une boucle imbriquée qui cherche chaque contact par sa clé : sept lectures logiques en tout, moins d’une milliseconde. Le seuil dépend de la table : sur une copie plus compacte de Marketing.Envoi, un relecteur a obtenu une jointure de hachage pour 3 313 envois estimés, et la boucle pour 2 263.

La même requête avec une constante (WHERE e.CampagneId = 12623) lit l’histogramme, qui estime 8 envois avec l’échantillon : la boucle imbriquée est choisie dans les deux cas. Sur une même colonne, le FULLSCAN peut donc changer le plan des requêtes à variable et pas celui des requêtes à constante. Le how-to FULLSCAN persistant montre le même mécanisme sur une jointure, avec Elearning.Activite : la densité échantillonnée y fait choisir une jointure de hachage et un parcours de table.

Les pièges de la mesure

Le cache des plans peut d’abord fausser la comparaison. Dans mon essai, reproduit par les deux relecteurs de cet article, après un UPDATE STATISTICS ... WITH FULLSCAN sur Marketing.Envoi, dont aucune ligne n’avait changé depuis la mise à jour précédente (modification_counter à 0 dans sys.dm_db_stats_properties), la requête ci-dessus a gardé son plan en cache, avec l’estimation de 3 368 envois. Après la modification d’une seule ligne de la colonne, le même FULLSCAN a fait recompiler le plan. Le guide d’architecture du traitement des requêtes compte pourtant la mise à jour des statistiques par UPDATE STATISTICS parmi les causes de recompilation, sans cette condition. Pour comparer, forcez la recompilation des requêtes sur la table :

USE PachaDataFormation;
EXEC sp_recompile 'Marketing.Envoi';

N’ajoutez pas OPTION (RECOMPILE) à une requête à variable pour la tester : l’optimiseur utilise alors la valeur de la variable, donc l’histogramme, et non plus la densité. Avec cette option, la requête ci-dessus reçoit la même estimation qu’avec la constante.

Le retour d’expérience sur l’allocation de mémoire (memory grant feedback) brouille aussi la mesure. Depuis SQL Server 2022, quand le Query Store est actif en lecture-écriture et le niveau de compatibilité à 140 ou plus, il garde ce retour d’expérience d’une compilation à l’autre. Je l’ai vu jouer même avec OPTION (RECOMPILE), que la page sur le retour d’expérience dit pourtant exclu : après les premières exécutions du regroupement par inscription, l’allocation était de 13,6 Mo, sans débordement, quelle que soit la statistique. Un relecteur a obtenu le débordement à chaque exécution une fois le Query Store désactivé, sur une copie de la table. J’ai mesuré les regroupements avec OPTION (USE HINT('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK')). En production, ce retour d’expérience masque une partie de l’erreur d’estimation après la première exécution.

Enfin, l’échantillon lui-même change d’une mise à jour à l’autre. Sur 30 mises à jour de la statistique d’InscriptionId sans aucune modification de la table, 28 ont estimé 33 363 ou 33 623 inscriptions, et deux en ont estimé environ 62 500. Sur CampagneId, trois mises à jour ont donné 1 183, 1 193 et 1 722 campagnes. Un plan peut donc changer après une mise à jour nocturne des statistiques sans que les données aient changé.

Les colonnes à recalculer en FULLSCAN

Le FULLSCAN sert sur une colonne dont les valeurs sont groupées dans la table, avec peu de lignes par valeur : un identifiant de campagne, de commande ou d’inscription dans une table de détail remplie au fil de l’eau. Sur ces colonnes, il corrige la densité et l’histogramme, et les requêtes qui en profitent le plus sont celles qui lisent la densité : variables, OPTIMIZE FOR UNKNOWN et regroupements, ainsi que la jointure du how-to. Sur une colonne aux valeurs dispersées, il corrige peu, et les erreurs qui restent viennent du nombre de pas de l’histogramme.

Avant de l’imposer, comparez la densité au COUNT(DISTINCT), passez le script de l’histogramme avant et après, et comparez les plans réels après sp_recompile. Le FULLSCAN de ix_Envoi_CampagneId a duré 1,3 s, contre 0,2 s pour l’échantillon. Pour qu’il survive aux mises à jour suivantes, automatiques comprises, fixez-le avec PERSIST_SAMPLE_PERCENT = ON, comme le décrit le how-to FULLSCAN persistant.

Nettoyer

Supprimez les index de la démonstration, et remettez la statistique de Marketing.Envoi sur toutes les lignes :

USE PachaDataFormation;
DROP INDEX IF EXISTS nix_activite_inscription ON Elearning.Activite;
DROP INDEX IF EXISTS nix_activite_module ON Elearning.Activite;
UPDATE STATISTICS Marketing.Envoi ix_Envoi_CampagneId WITH FULLSCAN;