Comme vous le savez certainement la mise à jour des statistiques peut s’effectuer de différentes manières. Une d’entre elle est d’utiliser la commande UPDATE STATISTICS avec l’option WITH SAMPLE qui permet de mettre à jour les statistiques selon une méthode d’échantillonnage en prenant en compte un certain nombre de lignes ou en se basant sur un certain pourcentage. Cette méthode est très utilisée dans les VLDB avec des tables volumineuses. Cependant quelle est le comportement de cette option sur les tables à faible volumétrie ?

Pour cela nous prendrons une table ayant la définition SQL suivante :

CREATE TABLE T_small

(

id INT IDENTITY(1,1) PRIMARY KEY,

texte CHAR(2)

);

Insérons un jeu de données de 10000 lignes :

INSERT T_small (texte) VALUES (‘TT’);

GO 10000

Lançons la requêtes suivante :

SELECT *

FROM T_small

WHERE id = 1;

… Et regardons les statistiques concernant l’index cluster de la table T_small :

EXEC sp_helpindex T_small;

qui donne :

On récupère les statistiques associées à l’index :

DBCC SHOW_STATISTICS(‘T_small’, PK__T_small__3213E83F6EE2CED2)

WITH STAT_HEADER;

qui donne

On remarque que la création des statistiques concernant l’index cluster a pris en compte la totalité de la table T_small (Colonne Rows = Colonne Rows Sampled)

Maintenant procédons à une mise à jour manuelle des statistiques de cet index à l’aide de la commande UPDATE STATISTICS et en choisissant un taux d’échantillonnage de 10%.

UPDATE STATISTICS T_small(PK__T_small__3213E83F6EE2CED2)

WITH SAMPLE 10 PERCENT;

… et voyons ce que donne cette commande sur les statistiques de l’index cluster :

On pourrait penser que le taux d’échantillonnage spécifié est purement ignoré dans notre cas (Colonne Rows toujours = Colonne Rows Sampled).

Jetons un rapide coup d’oeil à la volumétrie de la table T_small à l’aide de la procédure stockée système sp_spaceused.

EXEC sp_spaceused T_small;

qui donne :

Les données de la table occupent 24KB approximativement (La taille précise de la table ne nous intéresse pas ici).

Augmentons la quantité de données dans la table. Dans mon cas après avoir rejoué le script de population de la table présent au début du billet je me retrouve avec une table faisant 12 MB. Les statistiques sont maintenant les suivantes après avoir rejoué le script de mise  à jour des statistiques avec un taux d’échantillonnage de 10% :

Maintenant le nombre de lignes pris en compte pour l’échantillonnage a diminué, ce qui correspond à un taux de 65%. Remarquez également le nombre d’étapes utilisé par SQL Server pour constituer les statistiques qui a augmenté. Cependant nous ne sommes pas encore aux 200 steps max que peux utiliser SQL Server.

Si on continue à augmenter la taille des données présentes dans la table vous pourrez constater que le taux d’échantillonnage réel tend à se rapprocher du taux d’échantillonnage demandé avec la commande UPDATE STATISTICS. Lors de mes tests j’ai également pu remarquer que la totalité de la table était prise en compte jusqu’à une taille de données de la table égale à 8MB. Une fois ce seuil atteint le taux d’échantillonnage devient variable si on utilise UPDATE STATISTICS .. WITH SAMPLE PERCENT / ROWS. On peut se poser la question quant au comportement adopté par l’optimiseur SQL Server mais à y réfléchir, on peut se rappeler que celui-ci fonctionne sur un critère de coût. Il est vrai que la mise à jour totale des statistiques sur une table dont la taille est inférieure à 8MB reste peu coûteuse. C’est la raison pour laquelle l’optimiseur de SQL Server adoptera une telle stratégie de mise à jour. Cependant, une fois cette taille dépassée, l’échantillonnage de mise à jour des statistiques diminue proportionnellement avec la quantité de données insérée. Il est évident que dans ce cas, l’optimiseur ne peut plus adopter la même stratégie de mise à jour avec tout en utilisant un minimum de ressources.

Dans une VLDB, on constatera que l’option WITH SAMPLE est une solution souvent efficace et permet de limiter les ressources utilisées lors de cette mise à jour sur les tables de données volumineuses (qui atteignent bien souvent le Giga). Il faudra alors faire un compromis entre monopolisation des ressources et précision des statistiques … Cependant comme nous l’avons vu dans le billet l’adoption d’une telle stratégie de mise à jour des statistiques sur des petites table n’est pas nécessaire. SQL Server ignore purement le taux d’échantillonnage demandé.

Bonne mise à jour des statistiques !!!

David BARBARIN (Mikedavem)

MVP SQL Server