Maintenance des index SQL Server - Défragmentation et réindexation

Bonjour à tous !

Je rencontre de plus en plus fréquemment le problème de la fragmentation des index.
En effet, lorsqu'un fort volume de données est inséré dans une table sans que cela ne soit prévu lors de la conception de la base de données, les index croissent de manière désordonnée...

Pour détecter une telle fragmentation, SQL Server 2000 offre la commande suivante :
DBCC SHOWCONTIG
Si cette commande est toujours disponible sous SQL Server 2005 - et sera amenée à disparaitre dans les versions futures -, ce dernier fournit d'autres outils plus ergonomiques et plus précis pour les administrateurs. Parmi ces outils, on notera notamment la fonction dynamique suivante :
sys.dm_db_index_physical_stats

Il existe plusieurs façons de corriger le problème de la fragmentation. Mais avant de voir ces différentes techniques, attachons-nous tout d'abord à la façon dont l'on peut éviter que cette fragmentation ne se produise...

En réalité, tout dépend du mode d'alimentation de la base de données. En effet, un batch de chargement sera traité différemment d'une insertion unitaire.
De la même manière, le milieu fonctionnel impose des contraites ne permettant pas toujours l'emploi de certaines techniques.

Voyons tout d'abord le cas du batch de chargement : lors de l'exécution d'un batch, nous insérons généralement de grandes quantité de données. Ces batches s'exécutent habituellement de nuit, période de faible activité pour la base de données.
Dans ce cas, nous privilégierons la suppression de tous les index avant le chargement, et la recréation de ces index à la fin. Ceci présente un double avantage :
- D'une part les index regénérés ne sont pas fragmentés, puisque tout frais
- D'autre part le chargement des données est beaucoup plus rapide

Je rappellerai ici que la clé primaire incluant un index unique doit également être supprimée dans ce cas - parce que non, ça n'arrive jamais qu'il y ait des personnes qui suppriment tous les index sauf celui de la clé primaire ;-).
Bien entendu, cette suppression des index implique une absence d'activité sur la base de données sur une plage horaire donnée.

Dans le cas des insertions unitaires, le volume de ces insertions est généralement prévisible et plus faible. Dans ces conditions, le mieux est de dimensionner le FILLFACTOR de l'index à une valeur assurant un espace suffisant pour son développement.
Attention à ne pas tomber dans l'excès tout de même : un FILLFACTOR inférieur à 50 dégrade très fortement les performances et s'avère donc plus pénalisant que la fragmentation des index.

Attachons-nous maintenant à une table dont les index sont largement fragmentés.

Une solution simple reste la suppression des index pour les recréer. Cette solution présente l'avantage de toujours donner satisfaction et d'éviter l'explosion des groupes de fichiers.
Cette solution peut être effectuée dans un script traditionnel (DROP/CREATE) ou à l'aide de la commande DBCC DBREINDEX.
L'inconvénient majeur de cette méthode reste la période d'inactivité de la base de données nécessaire pour la reconstruction des index, car ces opérations sont des opérations OFFLINE.
La commande DBCC DBREINDEX, toujours présente dans SQL Server 2005, possède un équivalent avec la commande ALTER INDEX avec l'option REBUILD.

L'autre solution est la défragmentation (DBCC INDEXDEFRAG). Cette solution est une solution ONLINE. C'est la principale raison d'être de l'utilisation de cette commande. Cependant, il faut prendre garde à n'effectuer cette opération qu'après le chargement et non pas en parallèle au risque d'être totalement inefficace.
Le principal inconvénient de cette solution est la place nécessaire sur les groupes de fichiers. En effet, la défragmentation déplace des blocs de données vers des emplacements libres, et par conséquent pose rapidement des problèmes de taille des groupes de fichiers si la fragmentation est très importante. De plus la défragmentation est une opération entièrement loggée générant de fait de très gros volumes dans les journaux de transactions.
Là encore, SQL Server 2005 autorise toujours la commande DBCC INDEXDEFRAG, destinée à disparaître au profit du ALTER INDEX avec l'option REORGANIZE.

En conclusion, voici un récapitulatif des points importants :
- La première des choses est de s'assurer dans le cadre du développement de la base de données que notre base de données ne se fragmentera pas ou peu. Pour cela, il ne faut pas hésiter à supprimer les index et à les recréer au cours des processus d'alimentation. La fragmentation des index n'est pas une fatalité.
- Par la suite, un examen régulier de l'état de la base de données permet de surveiller l'évolution des index et le cas échéant d'entreprendre des actions.
- Sous SQL Server 2000, on privilégiera la commande atomique DBCC DBREINDEX si l'opération peut être menée OFFLINE. On restreindra donc l'usage de la commande DBCC INDEXDEFRAG aux cas nécessitant que les données restent ONLINE.
- Sous SQL Server 2005, on utilisera la commande ALTER INDEX avec les options REBUILD et REORGANIZE qui vont bien, cette commande étant la seule destinée à être maintenue dans les prochaines versions de SQL Server.