Je continue ma série sur les index avant de faire une petite pause pour
préparer une traduction d'article sur les clauses WHERE dynamiques d'un
collègue MVP suédois.
Dans ce post, je vais m'intéresser à la fragmentation des index. J'espère
que le terme fragmentation rappelera à certains le temps où on passait notre
samedi après midi à défragmenter nos disques de 40Mo (oui, méga-octets) pour
gagner quelques octets et installer Phantasmagoria.
Le principe est le même, l'idée est de "ranger" un index pour gagner un peu
de place et surtout qu'il soit plus performant. La
fragmentation est un processus "naturel", dû aux modifications des données. La
base vit, grandit et donc il faut opérer de la maintenance
régulièrement.
On a 2 types de fragmentation :
- celle qui touche le niveau feuille de l'index et qui correspond à
un ordre sur le disque différent de l'ordre logique dans l'index. Le résultat
est trop d'accès disque lors de scans sur ce niveau
- celle qui touche l'arbre de l'index et qui correspond à des pages trop
vides ou trop pleines
On peut utiliser 2 approches pour traiter avec la fragmentation :
- Traiter systématiquement l'ensemble de sa base de données. C'est la méthode
que j'utilise pour des petites volumétries et quand on dispose d'une fenêtre de
tir pour faire les opérations de maintenance sans gêner personne (par exemple
une base qui est utilisée avec des horaires de bureau).
- Traiter uniquement les index fragmentés et de façon intelligente. C'est la
méthode que j'utilise pour des grosses volumétries et des bases en accès
24/7.
Pour le premier cas, on fait juste un plan de maintenance dans lequel on
demande une réorganisation ou une reconstruction des index sur toute la base.
Sauf cas contraire, je fais un REORGANIZE tous les soirs et un REBUILD le
week-end (cela ne reste qu'une stratégie parmi d'autres). Pour les puristes,
les instructions T-SQL sont :
ALTER INDEX IX_abcd ON maTable
REORGANIZE
ALTER INDEX IX_abcd ON maTable REBUILD
Pour le second cas, il faut déterminer si un index a besoin d'une
reconstruction ou d'une réorganisation et connaître la volumétrie (et par
conséquent le temps que ça va prendre) pour limiter l'impact sur la
production.
On connait la fragmentation d'un index avec la vue système
sys.dm_db_index_physical_stats et plus précisément le champ
avg_fragmentation_in_percent. Ainsi on peut connaître pour
chaque index son niveau de fragmentation :
SELECT DB_NAME(database_id),
OBJECT_NAME(object_id), index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
DB_ID('MaBase'),
OBJECT_ID('MaTable'),
NULL, NULL, NULL)
Je ne vais pas rentrer ici dans le détail du pourcentage remonté par SQL
Server, je vais lui faire confiance pour le calcul (note : SQL Server
2008 est plus précis dans le calcul car il prend en compte plus de cas).
Sur l'action a mener en fonction de la fragmentation, on trouve toutes les
stratégies. Selon Microsoft, on réorganise pour une fragmentation < 30% et
on reconstruit au-delà. Moi je n'ai pas de seuil arrêté, il m'arrive de faire
un REBUILD quand la fragmentation est > 10%.
Voila, ce post est loin d'être exhaustif car je ne parle pas de la
fragmentation par niveau de l'index, du paramètre FILL_FACTOR ou de la
reconstruction ONLINE qui permet de reconstruire un index avec peu d'impact sur
la production (version Enterprise uniquement).
PS : pour connaître le nom de l'index visé (la requête ci-dessus ne remonte
que l'id), il va falloir faire une jointure sur sys.indexes.
Mise à Jour : pour illustrer le problème de la
fragmentation d'un index, voici 2 images montrant un index "propre" et un index
fragmenté.
Index non-fragmenté
Index fragmenté
Et comme il faut rendre à César ce qui lui appatient, voici mes sources :
http://technet.microsoft.com/en-us/magazine/cc671165.aspx
