Les statistiques et la cardinalité

Chapitre 06.5 - Les statistiques

22 minutes à lire

Les statistiques

Vous avez fait la connaissance sur un chat internet d’une jeune fille qui vous semble charmante et qui vit à Courtrai, en Belgique flamande. Elle semble apprécier vos grandes qualités de coeur et vous convenez d’un premier rendez-vous, afin de vous connaître en vrai. Comme vous êtes galant, vous proposez de faire le voyage à Courtrai. Elle vous donne rendez-vous au café De Brouwzaele.

Si vous n’avez jamais entendu parler de Courtrai, vous ne savez probablement rien de la taille de la ville, de la complexité de ses rues et donc vous ne pouvez par juger, avant de vous y rendre, si vous trouverez le café De Brouwzaele facilement.

Logiquement, votre premier réflexe sera de vous renseigner sur le nombre d’habitants. Si Courtrai est une ville de quelques milliers d’âmes, vous pouvez vous dire qu’elle est peu étendue et que le café De Brouwzaele sera facile à trouver. Par contre, en cherchant sur Wikipédia, vous vous apercevez que Courtrai compte quelque 73 000 habitants. Sachant cela, vous vous attendez à plus de difficultés. Vous allez donc demander l’adresse du café De Brouwzaele. Kapucijnestraat 19... bien, mais, combien y a-t-il de rues à Courtrai, et où se trouve Kapucijnestraat par rapport à la gare ? La connaissance de ces détails risque d’influencer fortement votre méthode de déplacement.

Pour SQL Server, la problématique est exactement la même. Imaginons que nous envoyons cette requête :

SELECT *

FROM Production.TransactionHistory

WHERE ProductId = 800

La première chose que sait SQL Server, c’est qu’il y a 113 443 lignes dans la table Production.TransactionHistory (dans ce cas, il maintient un compte de lignes dans la définition des index). Ensuite, il sait qu’il dispose d’un index sur la colonne ProductId, qui va lui permettre de filtrer rapidement les enregistrements où ProductId = 800 :

SELECT *

FROM sys.indexes i

WHERE OBJECT_NAME(i.object_id)

L’index s’appelle IX_TransactionHistory_ProductId.

L’optimiseur de SQL Server se base sur l’évaluation du coût d’un plan d’exécution afin de déterminer la meilleure stratégie. C’est ce qu’on appelle une optimisation basée sur le coût (cost-based optimization).

Mais pour évaluer le coût d’un plan d’exécution, SQL Server ne peut se contenter des seules informations que nous venons de voir. Il doit disposer d’une estimation du nombre de lignes qui va être retourné, pour juger au plus juste du coût de celle-ci. C’est là où interviennent les statistiques.

Si SQL Server dispose de statistiques de distribution des valeurs contenues dans une colonne, il peut évaluer avec une bonne précision le nombre de lignes impactées lorsqu’une requête filtre sur ces valeurs, car il sait à l’avance combien de lignes environ correspondent au critère.

Les statistiques sont donc un échantillonnage des données. Elles sont créées et maintenues automatiquement par SQL Server sur la clé de tout index créé. Elles sont visibles dans les vues sys.stats et sys.stats_columns.

Voyons les statistiques de l’index sur ProductId :

SELECT c.name as ColumnName, s.name as IndexName

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

WHERE s.name = ‘IX_TransactionHistory_ProductId’

ColumnName IndexName

ProductID IX_TransactionHistory_ProductID

Où nous voyons que des statistiques existent dans cet index, pour la colonne ProductId.

Elles sont en quelque sorte les informations vitales de l’index, ou son diplôme : elles permettent à SQL Server, dans sa stratégie, d’estimer le coût d’utilisation de l’index. En d’autres termes, grâce aux statistiques, SQL Server va pouvoir connaître à l’avance, approximativement, le résultat d’un

SELECT COUNT(*)

FROM Production.TransactionHistory

WHERE ProductId = 800

Si le ratio entre le nombre de lignes qui répond au critère recherché et le nombre total de ligne est faible, SQL Server va choisir d’utiliser l’index. Si par contre il est important, et qu’un bonne partie des lignes de la table doit être retournée par la requête, SQL Server va certainement faire le choix de parcourir la table plutôt qu’utiliser l’index, parce qu’il sera moins coûteux de procéder de cette façon que de résoudre ligne par ligne les adresses de pointeurs contenues dans le dernier niveau de l’index.

6.4.1 Statistiques sur les index

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

DBCC FREEPROCCACHE

SET SHOWPLAN_XML ON

SELECT *

FROM Production.TransactionHistory

WHERE ProductId = 800

SET SHOWPLAN_XML OFF

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 clustered, puisque le dernier niveau de l’index clustered 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 6 fois dans la table. Essayons avec cela :

DBCC FREEPROCCACHE

SET SHOWPLAN_XML ON

SELECT *

FROM Production.TransactionHistory

WHERE ProductId = 760

SET SHOWPLAN_XML OFF

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.

6.4.2 Colonnes non indexées

Les statistiques ne sont pas uniquement utiles pour les index. 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 lui permettra 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

leur nom commence par _WA_Sys_.

6.4.3 Sélectivité et densité

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é.

6.4.4 Consulter les statistiques

Nous avons quelquefois entendu des imprécisions au sujet des statistiques. Les statistiques ne sont pas des index, et ne sont pas stockés comme les index ou dans les pages d’index. Notamment les informations de distribution ne se situent pas au niveau des noeuds de l’arborescence de l’index. Si c’était le cas, le plan d’exécution ne pourrait pas faire le choix d’utiliser l’index ou non... avant de l’utiliser, puisqu’il devrait entrer dans l’arborescence pour retrouver l’estimation de lignes à retourner, puis ensuite quitter l’index pour faire un scan si ce choix lui paraît meilleur.

Ce genre de décision doit se prendre avant de commencer le processus de recherche des données.

Ainsi, les données de statistiques sont disponibles en tant que métadonnées de l’index, et non pas dans sa structure propre. Elle sont stockées dans une colonne de type LOB, dans une table système visible à travers la vue sys.sysindexes (du nom d’une ancienne table système de SQL Server 2000).

En faisant cette requête :

SELECT statblob

FROM sys.sysindexes

On peut voir que la colonne statblob, qui contient ces informations de statistiques, retourne NULL. La colonne contient bien des données, mais elles ne sont pas visibles par ce moyen, et la valeur retournée par requête sera toujours NULL. D’ailleurs,

SELECT * FROM sys.indexes;

Qui est la vue de catalogue officielle affichant les données d’index, ne retourne pas cette colonne. La seule façon de retourner les données de statistiques est d’utiliser une commande DBCC :

DBCC SHOW_STATISTICS

ou d’afficher les propriétés des statistiques dans l’explorateur d’objets de SQL Server Management Studio (noeud Statistiques), page Détails, qui affiche les résultats du DBCC SHOW_STATISTICS dans une zone de texte.

Exemple avec notre index :

DBCC SHOW_STATISTICS ( ‘Production.TralinsactionHistory’,

X_TransactionHistory_ProductID)

qui retourne un entête, la densité par colonne de l’index, et un histogramme de distribution des données, selon l’échantillonnage opéré. Observons d’abord le résultat de l’entête :

Name Updated Rows Rows Sampled

IX_TransactionHistory_ProductID Apr 26 2006 113443 113443 11:45AM

Steps Density Average key String Index length

200 0,01581469 8 NO

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.

All density Average Length Columns

0,0022675736961 4 ProductID

8,815E-06 8 ProductID, TransactionID

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ée 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 ligne (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, puisque incluant une colonne unique, chaque ligne est donc unique, et sa densité est 1 / T.

Échantillonnage

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS

1 45 45 0 1

3 45 307 1 45

316 144 307 1 45

319 218 118 2 72

...

779 37 771 7 5,285714

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 :

Colonne Contenu

RANGE_HI_KEY La valeur de colonne de l’échantillon, donc de la dernière ligne de l’ensemble échantillonné (le bucket)

RANGE_ROWS Le nombre de lignes entre l’échantillon et l’échantillon précédent, ces deux échantillons non compris

EQ_ROWS Nombre de lignes dans l’ensemble dont la valeur est égale à celle de l’échantillon

DISTINCT_RANGE_ROWS Nombre de valeurs distinctes dans l’ensemble

AVG_RANGE_ROWS Nombre moyen de lignes de l’ensemble ayant la même valeur, donc RANGE_ROWS / DISTINCT_RANGE_ROWS

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 contenu 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

index_name allocation_type data_pages

PK_TransactionHistory_TransactionID IN_ROW_DATA 788

IX_TransactionHistory_ProductID IN_ROW_DATA 155

IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID IN_ROW_DATA 211

Nous pouvons donc assumer 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 indicateur de table dans la requête (nous verrons les indicateurs de table dans la section 8.2.1) :

SET STATISTICS IO ON

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), 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.

SQL Server essaie autant que possible d’éviter le bookmark lookup. Le plan généré utilise donc une jointure entre les deux index : l’index nonclustered 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 clustered 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 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 à DBCC IND :

DBCC IND (AdventureWorks, ‘Production.TransactionHistory’, 1)

Où le troisième paramètre est l’id de l’index. L’index clustered 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.

Résumés de chaîne

Les résumés de chaîne (string summary) sont une addition aux statistiques dans SQL Server 2005. Comme vous l’avez vu dans le header retourné par DBCC SHOW_STATISTICS, la valeur de String Index indique si les statistiques pour cette clé de l’index contiennent des résumés de chaîne. Il s’agit d’un échantillonnage à l’intérieur d’une colonne de type chaîne de caractères (un varchar par exemple), qui permet à l’optimiseur d’avoir une meilleure estimation du nombre de lignes que la requête va retourner. Ces statistiques ne sont pas utiles pour un seek d’index, bien sûr, puisque la recherche ne pourra se faire sur une clé d’index, mais elle permettent simplement d’améliorer l’estimation de cardinalité. Les résumés de chaîne ne couvrent que 80 caractères de la donnée (les 40 premiers et 40 derniers si la chaîne est plus longue).

6.4.5 Maintenir les statistiques

Consultation

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.

Création

Les statistiques sur les index sont créées automatiquement, sans possibilité de désactivation : un index sans statistiques 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..

A 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.

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, ce qui n’est pas nécessaire dans la plupart des cas : pourquoi maintenir des statistiques sur des colonnes jamais filtrées ?

Mise à jour

La mise à jour des statistiques est importante. Pour reprendre notre exemple, supposons qu’avant de partir pour Courtrai, vous en parlez 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 grand 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, tel qu’il 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.

Vous trouverez une explication plus détaillée de ce mécanisme dans le document de la base de connaissances Microsoft 195565 (http://support.microsoft.com/kb/195565).

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ésente pour chaque colonne de statistique. Cependant 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.

Vous pouvez activer ou désactiver la mise à jour automatique des statistiques sur un index, une table ou des statistiques, en utilisant la procédure stockée sp_autostats :

sp_autostats [@tblname =] ’table_name'

[, [@flagc =] ‘stats_flag’]

[, [@indname =] ‘index_name’]

Le paramètre @flagc peut être indiqué à ON pour activer, OFF pour désactiver, NULL pour afficher l’état actuel.

sp_updatestats met à jour les statistiques de toutes les tables de la base courante, mais seulement celles qui ont dépassé le seuil d’obsolescence déterminé par rowmodctr (contrairement à SQL Server 2000 qui mettait à jour toutes les statistiques). Cela vous permet de lancer une mise à jour des statistiques de façon contrôlée, durant des périodes creuses, afin d’éviter d’éventuels problèmes de performances dûs à la mise à jour automatique, dont nous allons parler dans la section suivante.

Nous avons vu que les mises à jour de statistiques sont indispensables à la bonne performance des requêtes, et que leur maintenance est nécessaire pour assurer une bonne qualité à travers le temps. Mais, quand cette opération se déclenche-t-elle ?

Tout comme la création automatique, elle se produit au besoin, c’est-à-dire au moment où une requête va s’exécuter et pour laquelle ces statistiques sont utiles, aussi bien dans le cas d’une requête ad hoc (c’est-à-dire d’une requête écrite une fois pour un besoin particulier), que d’une procédure stockée. Si le seuil de modification de la table est atteint, il va d’abord lancer une mise à jour des statistiques nécessaires, puis ensuite générer son plan d’exécution. Cela peut évidemment provoquer une latence à l’exécution de la requête. Pour les plans d’exécution déjà dans le cache, la phase d’exécution inclut une vérification des statistiques, un UPDATE éventuel de celles-ci, puis une recompilation, ce qui dans certains cas peut se révéler encore plus lourd.

Cela pose rarement un problème, mais si vous êtes dans le cas où cela affecte vos performances, vous avez quelques solutions à votre disposition :

AUTO_UPDATE_STATISTICS_ASYNC vous permet de réaliser une mise à

jour des statistiques de façon asynchrone. Cela veut dire que lorsque le moteur SQL décide de mettre à jour ses statistiques, il n’attend pas la fin de cette opération pour exécuter la requête déclenchante. Elle démarre donc plus vite, mais ne s’exécute pas forcément plus rapidement, car avec un plan d’exécution potentiellement obsolète. Les exécutions suivantes profiteront des statistiques rafraîchies. En temps normal, cette option n’est pas nécessaire ;
vous pouvez mettre à jour manuellement vos statistiques durant vos

fenêtres d’administration, en utilisant par exemple sp_updatestats, ou une tâche d’un plan de maintenance.