Lors de la reconstruction des index (ALTER INDEX … REBUILD), les statistiques de colonne qui constituent la clé de l’index sont recalculées avec l’échantillonnage maximal.
Que se passe-t-il si par la suite on souhaite mettre à jour les statistiques pour les colonnes non-indexées ?
Si l’on éxécute :
UPDATE STATISTICS dbo.maTable
Sans aucune option, toutes les statistiques attachées à la table sont recalculées avec un échantillonnage par défaut.
On perd donc l’avantage de la reconstruction des index, qui utilise pour le recalcul des statistiques un échantillonnage de 100%.
Il suffit pour éviter ce comportement d’ajouter l’option COLUMNS comme suit :
UPDATE STATISTICS dbo.maTable WITH COLUMNS
Voici un petit pour comprendre ce que fait SQL Server.
Notez que je n’ai pas changé les options de statistiques dans ma base de données, ce qui signifie qu’elles sont créées et maintenues automatiquement.
Créons délibérément la table suivante :
1 2 3 4 5 | CREATE TABLE test_statistique ( test_statistique_id int NOT NULL IDENTITY CONSTRAINT PKtest_statistique PRIMARY KEY , nom_test_statistique char(4000) NOT NULL ) |
C’est à dire que pour toute ligne insérée dans la table, nous consommerons 8 (int )+ 4000 octets.
De cette façon, nous dépasserons facilement les 8Mo d’espace occupé par la table.
En effet, pour une table ayant une taille inférieure à 8Mo, SQL Server utilise l’échantillonnage maximal pour collecter les statistiques de colonne.
Ce n’est plus le cas lorsque la taille de la table dépasse les 8Mo, ou l’échantillonnage est alors utilisé.
Peuplons la table comme suit :
INSERT INTO dbo.test_statistique (nom_test_statistique) VALUES (‘ ‘)
GO 10000
Exécutons la requête suivante, de sorte que des statistiques soient automatiquement créées pour la colonne nom_test_statistique :
SELECT *
FROM dbo.test_statistique
WHERE nom_test_statistique = ‘a’
Il existe maintenant les statistiques suivantes :
Voyons comment ont été échantillonnées les statistiques :
DBCC SHOW_STATISTICS (test_statistique, _WA_Sys_00000002_7f60ed59) WITH STAT_HEADER
DBCC SHOW_STATISTICS (test_statistique, PKtest_statistique) WITH STAT_HEADER
Nous donne :
Reconstruisons l’index sous-jacent à la clé primaire :
ALTER INDEX PKtest_statistique ON dbo.test_statistique REBUILD
Puis rééxécutons DBCC SHOW_STATISTICS (test_statistique, PKtest_statistique) WITH STAT_HEADER
Nous obtenons :
L’échantillonnage est donc bien de 100% lors de la reconstruction de l’index.
C’est normal puisque la table a du être parcourue dans son entier pour construire l’index.
Exécutons maintenant :
UPDATE STATISTICS dbo.test_statistique WITH COLUMNS
Puis rééxécutons les deux instructions DBCC SHOW_STATISTICS :
Les statistiques de la clé primaire n’ont pas été touchées, et seules celles de la colonne nom_test_statistique ont été recalculées.
Voyons la différence sans l’option WITH COLUMNS :
Le bénéfice de la reconstruction de l’index de la clé primaire est donc perdu !
On l’a vu donc, il est indispensable d’utiliser l’option WITH COLUMNS de l’instruction UPDATE STATISTICS pour maintenir correctement les statistiques de vos tables.
Bonnes statistiques !
ElSüket 