SQL Server 2005 : Comprendre et utiliser les statistiques

Dans le cas qui nous occupe, ProductId = 800 correspond à 416 lignes/133 443. Voyons le plan d'exécution estimé :

DBCC FREEPROCCACHE 
GO
SET SHOWPLAN_XML ON
GO
SELECT *
FROM Production.TransactionHistory
WHERE ProductId = 800
GO
SET SHOWPLAN_XML OFF
GO

Ci-dessous un extrait de ce plan :

<RelOp NodeId="0" PhysicalOp="Clustered Index Scan" 
    LogicalOp="Clustered Index Scan" EstimateRows="418" 
    EstimateIO="0.586088" EstimateCPU="0.124944" AvgRowSize="54" 
    EstimatedTotalSubtreeCost="0.711032" Parallel="0" EstimateRebinds="0" 
    EstimateRewinds="0">

Nous voyons que l'optimiseur choisit de parcourir la table (donc ici un scan de l'index ordonné, puisque le dernier niveau de l'index ordonné correspond aux données de la table) au lieu d'utiliser l'index. Nous voyons aussi dans l'attribut EstimateRows que les statistiques permettent à l'optimiseur d'avoir une idée assez précise du nombre de lignes correspondant à la clause WHERE.

Essayons maintenant d'indiquer un nombre plus petit de lignes à retourner :

SELECT ProductId, COUNT(*)
FROM Production.TransactionHistory
GROUP BY ProductId

Nous voyons par exemple que le ProductId 760 ne se retrouve que six fois dans la table. Essayons avec cela :

DBCC FREEPROCCACHE 
GO
SET SHOWPLAN_XML ON
GO
SELECT *
FROM Production.TransactionHistory
WHERE ProductId = 760
GO
SET SHOWPLAN_XML OFF
GO

Un extrait du plan d'exécution estimé :

<RelOp NodeId="2" PhysicalOp="Index Seek" LogicalOp="Index Seek" 
    EstimateRows="5.28571" EstimateIO="0.003125" EstimateCPU="0.000162814" 
    AvgRowSize="15" EstimatedTotalSubtreeCost="0.00328781" Parallel="0" 
    EstimateRebinds="0" EstimateRewinds="0">
...
<Object Database="[AdventureWorks]" Schema="[Production]" 
    Table="[TransactionHistory]" Index="[IX_TransactionHistory_ProductID]" />

Cette fois-ci nous voyons que l'index est utilisé : c'est la solution la moins coûteuse. L'estimation des lignes à retourner, basée sur les statistiques de distribution, donne 5.28571 lignes. Nous verrons plus loin d'où SQL Server tire cette approximation.

Les statistiques sont-elles utiles uniquement pour les index ?
Non.
L'optimiseur peut profiter de la présence de statistiques même sur des colonnes qui ne font pas partie de la clé d'un index. Cela permettra à SQL Server d'évaluer le nombre de lignes retournées dans une requête qui filtre sur les valeurs de cette colonne, et donc de choisir un bon plan d'exécution. Par exemple, si la colonne participe à un JOIN, cela permettra à l'optimiseur de choisir le type de jointure le plus adapté.

En SQL Server 2000, comme les statistiques de colonne sont stockées dans la table système sysindexes avec la définition des index, la création de statistiques avait comme conséquence de diminuer le nombre possible d'index pouvant être créés sur la table.
Vous étiez limités à 249 index non ordonnés par table, donc à 249 index plus statistiques.
Sur des tables contenant beaucoup de colonnes, la création automatique des statistiques pouvait vous faire atteindre cette limite. Il vous fallait alors supprimer des statistiques (avec la commande DROP STATISTICS ) pour permettre la création de nouveaux index.

Dans SQL Server 2005, la limite du nombre de statistiques de colonne sur une table a été augmentée à 2000, plus 249 statistiques d'index, poussant le nombre de statistiques possibles sur une table à 2249.

Pour voir les statistiques créées automatiquement :

SELECT * FROM sys.stats WHERE auto_created = 1

leurs noms commencent par _WA_Sys_.

Ces statistiques de distribution permettent de déterminer la sélectivité d'un index. Plus le nombre de données uniques présentes dans la colonne est élevé, plus l'index est dit sélectif. La plus haute sélectivité est donnée par un index unique, où toutes les valeurs sont distinctes, et les plus basses sélectivités sont amenées par des colonnes qui contiennent beaucoup de doublons, comme les colonnes de type bit, ou char(1) avec seulement quelques valeurs (par exemple H et F pour une colonne de type sexe).

À l'inverse, la densité représente le nombre de valeurs dupliquées présentes dans la colonne. Plus il y a de valeurs dupliquées, plus la densité est élevée.

Sur l'index dans son entier, le pourcentage moyen de lignes dupliquées donne la sélectivité et la densité : plus ce pourcentage est faible, plus l'index est sélectif, et plus il est élevé, plus l'index est dense. La densité s'oppose donc à la sélectivité.

Vous trouvez dans l'entête les informations générales des statistiques : nom, date de dernière mise à jour, nombre de lignes dans l'index et nombre de lignes échantillonnées, nombre d'étapes (steps) effectuées pour effectuer cet échantillonnage, densité de l'index, longueur moyenne de la clé de l'index. Le booléen String Index indique si la colonne contient des résumés de chaîne, qui est une fonctionnalité de SQL Server 2005 que nous détaillerons plus loin.

Le deuxième résultat vous indique la densité de chaque colonne de l'index. La clé de l'index dépendant toujours de la première colonne, la densité individuelle est calculée seulement pour celle-ci, ensuite les densités calculées sont celles des colonnes agrégées dans l'ordre de leur présence dans l'index (le préfixe de la clé), une colonne après l'autre, si l'index est composite, ou si l'index est non ordonné et présent sur une table ordonnée. Dans ce cas, comme nous le voyons ici, chaque index non ordonné contiendra la ou les colonnes de l'index ordonné en dernière position.

La densité est donnée par le nombre moyen de lignes pour une valeur de la colonne divisé par le nombre total de lignes. Le nombre moyen de lignes est calculé en prenant le nombre total de lignes (T) divisé par le nombre de valeurs distinctes (VD) dans la colonne. Ce qui donne :

(T / VD) / T

Qui équivaut à

1 / VD

Et en effet :

SELECT 1.00 / COUNT(DISTINCT ProductId))
FROM Production.TransactionHistory

retourne bien 0,0022675736961.

Plus la densité est faible, plus la sélectivité est élevée, et plus l'index est utile. La sélectivité maximale est offerte par un index unique, comme ici l'index ordonné unique sur la clé primaire de la table.

La densité de la colonne unique est donc 1 / VD, qui correspond logiquement à 1 / T, puisque le nombre de valeurs distinctes équivaut au nombre de lignes de la table.

SELECT 1.00 / COUNT(*)
FROM Production.TransactionHistory

retourne 0.0000088149996, ce qui représente une très basse densité, donc une sélectivité très élevée. Pour vérifier que nous obtenons bien la même valeur que la deuxième densité (ProductID, TransactionID), forçons la notation scientifique en retournant une donnée float :

SELECT CAST(1.00 as float) / COUNT(*)
FROM Production.TransactionHistory

qui donne une valeur approximative de 8,815E-06, CQFD.

Le couple ProductID + TransactionID ne peut être que d'une sélectivité maximale, puisqu'incluant une colonne unique, chaque ligne est donc unique, et sa densité est 1 / T.

Avec les deux affichages précédents, l'optimiseur SQL sait quel est le nombre de lignes dans la table, et le nombre de valeurs distinctes dans la colonne indexée. Il lui faut maintenant pouvoir évaluer, pour une certaine valeur de colonne, quel sera le nombre approximatif de lignes concernées (combien ProductId = 760 concerne-t-il de lignes ?). Pour cela, il maintient un échantillonnage des valeurs établi selon un certain nombre de sauts (les steps que nous avons vus dans les informations de header), sous forme d'un tableau. Dans notre exemple, un tableau de 200 steps. SQL Server crée un maximum de 200 steps par statistiques. Détaillons la signification des colonnes :

Nous voyons donc ici comment l'optimiseur a estimé le nombre de lignes retournées par la clause WHERE ProductId = 760 : il s'est positionné sur l'échantillon 779, puisque la valeur 760 est contenue dans l'ensemble échantillonné de cette ligne, et a retrouvé la valeur de AVG_RANGE_ROWS.

Dans le case de ProductId = 800, l'optimiseur a trouvé la ligne où le RANGE_HI_KEY = 800. Le nombre d'EQ_ROWS est 418.

Pourquoi l'optimiseur fait-il le choix de parcourir la table au lieu d'utiliser l'index ? Sachant qu'il aura à retourner 418 lignes, cela fait donc potentiellement un maximum de 418 pages de 8 ko en plus des pages de l'index à parcourir.

Nous savons, grâce à la requête suivante, combien de pages sont utilisées par chaque index :

SELECT     o.name AS table_name,
        p.index_id, 
        i.name AS index_name, 
        au.type_desc AS allocation_type, 
        au.data_pages, partition_number
FROM     sys.allocation_units AS au
JOIN     sys.partitions AS p ON au.container_id = p.partition_id
JOIN     sys.objects AS o ON p.object_id = o.object_id
LEFT JOIN sys.indexes AS i     ON p.index_id = i.index_id 
                            AND i.object_id = p.object_id
WHERE     o.name = N'TransactionHistory'
ORDER BY o.name, p.index_id

Nous pouvons donc supposer qu'un scan de la table (donc de l'index ordonné) coûtera la lecture de 788 pages, donc 788 reads. Cela représente bien plus que 418 pages. Pourquoi choisir un scan ?

Analysons ce qui se passe réellement lorsque nous utilisons un plan ou un autre. Nous pouvons le découvrir en forçant l'optimiseur à utiliser l'index en ajoutant un index hint dans la requête :

SET STATISTICS IO ON
GO
SELECT *
FROM Production.TransactionHistory 
    WITH (INDEX = IX_TransactionHistory_ProductID)
WHERE ProductId = 800

Voici les résultats de pages lues (reçues grâce à SET STATISTICS IO ON qui active le renvoi d'informations d'entrées/sorties), et le plan d'exécution utilisé :

Table 'TransactionHistory'. 
Scan count 1, logical reads 1979, physical reads 3, read-ahead reads 744, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Relançons ensuite la requête en laissant SQL Server choisir son plan :

SELECT *
FROM Production.TransactionHistory
WHERE ProductId = 800

Résultat :

Table 'TransactionHistory'. 
Scan count 1, logical reads 792, physical reads 0, read-ahead reads 44, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Alors qu'en SQL Server 2000, l'utilisation de l'index aurait entraîné un bookmark lookup, c'est-à-dire une recherche à partir du pointeur présent dans le dernier niveau de l'index, SQL Server 2005 essaie autant que possible d'éviter le bookmark lookup. Le plan généré utilise donc une jointure entre les deux index : l'index non ordonné IX_TransactionHistory_ProductID est utilisé pour chercher les lignes correspondant au critère, et une jointure de type nested loop est utilisée pour parcourir l'index ordonné à chaque occurrence trouvée, pour extraire toutes les données de la ligne (SELECT *).

Cette jointure doit donc lire bien plus de pages que celles de l'index, puisqu'on doit aller chercher toutes les colonnes de la ligne. À chaque ligne trouvée, il faut parcourir l'index ordonné pour retrouver la page de données et en extraire la ligne. En l'occurrence cela entraîne la lecture de 1979 pages.

Nous avons vu que l'index ordonné utilise 788 pages. Le scan de cet index, selon les informations d'entrées/sorties, a coûté 792 lectures de page. D'où sortent les quatre pages supplémentaires ?

Selon la documentation de la vue sys.allocation_units dont la valeur 788 est tirée, la colonne data_pages exclut les pages internes de l'index et les pages de gestion d'allocation (IAM). Les quatre pages supplémentaires sont donc probablement ces pages internes. Nous pouvons le vérifier rapidement grâce à une commande DBCC non documentée qui liste les pages d'un index : DBCC IND

DBCC IND (AdventureWorks, 'Production.TransactionHistory', 1)

où le troisième paramètre est l'id de l'index. L'index ordonnée prend toujours l'id 1.

Le résultat de cette commande nous donne 792 pages, donc 788 sont des pages de données, plus une page d'IAM (PageType = 10) et trois pages internes (PageType = 2). Voilà pour le mystère.

Nous pouvons vérifier la présence de statistique de plusieurs manières :

• sp_helpstats est l'ancienne méthode d'obtention des statistiques. Ne l'utilisez plus et préférez les vues de catalogues ci-dessous :
• sys.stats : liste des statistiques présentes dans la base de données ;
• sys.stats_columns : colonnes présentes pour chaque statistique

SELECT *
FROM sys.stats s
JOIN sys.stats_columns sc ON s.object_id = sc.object_id 
                        AND  s.stats_id  = sc.stats_id
JOIN sys.columns c ON s.object_id = c.object_id 
                        AND  sc.column_id = c.column_id

• STATS_DATE() : vous pouvez récupérer la date de dernière mise à jour des statistiques en utilisant la nouvelle fonction STATS_DATE() de SQL Server 2005, à laquelle vous passez un id de table et un id d'index

SELECT  OBJECT_NAME(i.object_id) AS table_name, 
        i.name AS index_name, 
        STATS_DATE(i.object_id, i.index_id)
FROM sys.indexes AS i
WHERE OBJECT_NAME(i.object_id) = N'TransactionHistory'

• DBCC SHOW_STATISTICS : comme nous l'avons déjà vu, DBCC SHOW_STATISTICS affiche les détails d'un jeu de statistiques.

Les statistiques sur les index sont créées automatiquement, sans possibilité de désactivation : un index sans statistique serait inutilisable.
Les statistiques sur les colonnes non indexées peuvent être créées manuellement ou automatiquement.
Par défaut, les bases de données sont créées avec l'option AUTO_CREATE_STATISTICS à ON, c'est-à-dire que les statistiques dont l'optimiseur de requêtes a besoin pour générer son plan d'exécution seront générées à la volée. Certes, il y aura un ralentissement du service de la requête dû à la création des statistiques, mais cela ne se produira que la première fois, et potentiellement ce ralentissement sera moindre que celui induit par un plan d'exécution mal évalué.

Cette fonctionnalité, de création et mise à jour automatique des statistiques par SQL Server, est appelée Auto Stats.

Pour créer manuellement des statistiques sur une ou plusieurs colonnes, vous pouvez utiliser la commande CREATE STATISTICS :

CREATE STATISTICS statistics_name 
ON { table | view } ( column [ ,...n ] ) 
    [ WITH 
        [ [ FULLSCAN 
          | SAMPLE number { PERCENT | ROWS } 
        [ NORECOMPUTE ] 
    ] ;

Il peut être intéressant par exemple de créer manuellement des statistiques sur une combinaison de colonnes pour améliorer l'estimation du plan d'une requête dont la clause WHERE filtre sur ces colonnes.

WITH FULLSCAN vous permet d'indiquer que toutes les valeurs doivent être parcourues. Sur les tables volumineuses, l'échantillonnage ne se fait pas sur toutes les valeurs, les statistiques seront donc moins précises. En indiquant WITH FULLSCAN (ou WITH SAMPLE 100 PERCENT, qui est équivalent), vous forcez SQL Server à prendre en compte toutes les lignes. Pour les tables de taille moyenne, l'échantillonnage se fait de toute manière sur toutes les valeurs. SQL Server s'assure un minimum de 8 MB de données échantillonnées (1024 pages ou plus), ou la taille de la table si elle pèse moins.

À l'inverse, vous pouvez demander un échantillonnage moins précis avec l'option WITH SAMPLE. Notez que si SQL Server considère que votre sampling n'est pas suffisamment élevé pour générer des statistiques utiles, il corrige lui-même la valeur à la hausse.

Avec NORECOMPUTE, vous forcez SQL Server à ne jamais mettre à jour automatiquement ces statistiques. Ne l'utilisez que si vous savez ce que vous faites.

Vous pouvez supprimer des statistiques avec l'instruction DROP STATISTICS. Vous ne devriez normalement jamais avoir à faire cela.

Si à la suite de cet article, vous êtes tombé amoureux des statistiques, vous pouvez, grâce à la procédure sp_createstats, créer en une seule fois, des statistiques pour toutes les colonnes de toutes les tables de votre base.
Cela doit être une charmante expérience que je n'ai jamais tentée. Si vous voulez réaliser des optimisations poussées de votre système, vous pouvez vous y lancer. Cela vous permettra d'éviter le temps d'attente à la première exécution d'une requête qui va nécessiter la création de statistiques. Dans la plupart des cas, la fonctionnalité Auto Stats vous suffira amplement.

La mise à jour des statistiques est importante. Pour reprendre notre exemple, supposons qu'avant de partir pour Courtrai, vous en parliez avec votre grand-père qui vous annonce qu'il connaît bien Courtrai pour y avoir passé ses vacances d'été pendant plusieurs années avec sa famille lorsqu'il était enfant. Par politesse vous évitez de commenter les choix de destination de vacances de vos arrière-grands-parents, d'autant plus qu'il se souvient d'avoir eu en son temps un plan détaillé de la ville. Intéressant, cela vous permettrait de vous faire une idée plus précise de vos déplacements. Après quelques recherches au grenier, il vous ressort un vieux plan très jauni, qui date de 1933. Pensez-vous réellement qu'il va vous être très utile, sachant que la ville a été très endommagée par les bombardements de 1944, et qu'une grande partie de celle-ci a été reconstruite après la guerre ?

Le contenu de votre table évolue, et vous ne pouvez baser ad vitam aeternam l'évaluation de la sélectivité d'une colonne sur les statistiques telles qu'elles ont été créées. Cette mise à jour doit se faire régulièrement et prendre en compte les larges modifications de données. Comme la création, elle peut se déclencher automatiquement ou manuellement.

Automatiquement avec l'option de base de données AUTO UPDATE STATISTICS, qu'il est vivement recommandé de laisser à ON, telle qu'elle est par défaut.

SELECT DATABASEPROPERTYEX('IsAutoUpdateStatistics') -- pour consulter la valeur actuelle
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS [ON|OFF] -- pour modifier la valeur

En SQL Server 2000, la mise à jour automatique des statistiques était déclenchée par un nombre de modifications de lignes de la table. Chaque fois qu'un INSERT, DELETE ou une modification de colonne indexée était réalisée, la valeur de la colonne rowmodctr de la table sysindexes était incrémentée. Pour que la mise à jour des statistiques soit décidée, il fallait que la valeur de rowmodctr soit au moins 500. Si cela vous intéresse, vous trouverez une explication plus détaillée de ce mécanisme dans le document de la base de connaissances Microsoft 195565, listé en bibliographie.

Avec SQL Server 2005, ce seuil (threshold) est géré plus finement. La colonne rowmodctr est toujours maintenue mais on ne peut plus considérer sa valeur comme exacte. En effet, SQL Server maintient des compteurs de modifications pour chaque colonne, dans un enregistrement nommé colmodctr présent pour chaque colonne de statistique. Cependant, à ma connaissance, cette colonne n'est pas visible. Vous pouvez toujours vous baser sur la valeur de rowmodctr pour déterminer si vous pouvez mettre à jour les statistiques sur une colonne, ou si la mise à jour automatique va se déclencher bientôt, car les développeurs ont fait en sorte que sa valeur soit " raisonnablement " proche de ce qu'elle donnait dans les versions suivantes.

V-C-1. sp_autostats▲

sp_autostats [@tblname =] 'table_name'
            [, [@flagc =] 'stats_flag']
            [, [@indname =] 'index_name']

V-C-2. sp_updatestats▲

V-C-3. Mise à jour et performances▲

V-C-4. Plan de maintenance▲

Dans la boîte du plan de maintenance, l'outil d'optimisation et de sauvegarde de vos bases, vous disposez d'une tâche pour planifier aisément la mise à jour de vos statistiques. Vous pouvez découvrir dans les deux copies d'écran ci-dessous les options à votre disposition.