Indexation – Gérer la fragmentation

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