Après avoir vu ce que sont la fragmentation interne et externe d’un index, voyons les différences entre les options REBUILD et REORGANIZE de l’instruction ALTER INDEX (ou respectivement DBCC DBREINDEX ou DBCC INDEXDEFRAG sous SQL Server 2000)

=> REBUILD est une opération atomique, alors que REORGANIZE ne l’est pas.
Cela signifie que si l’on arrête une instruction ALTER INDEX … REBUILD en cours d’exécution, tout le travail du moteur de base de données effectué jusqu’au moment de l’arrêt de l’exécution de l’instruction est perdu. Ce n’est pas le cas de REORGANIZE.

=> REORGANIZE ne fait que ré-arranger le niveau feuille de l’index, et n’alloue ou ne désalloue aucune page.
En revanche, REBUILD reconstruit tous les niveaux de l’index, et se sert au besoin de nouvelles pages, de façon à réduire la fragmentation interne et externe de l’index. REORGANIZE ne fait que réduire la fragmentation logique de l’index, de sorte à minimiser, si ce n’est éviter, les aller-retours dans les pages de l’index lors de la lecture de celui-ci.

=> REBUILD est exécutée hors-ligne par défaut.
Dans ce cas, on ne peut pas mettre à jour la table sous-jacente s’il s’agit de son index cluster, et on ne peut pas changer les colonnes qui participent à la définition d’un index non-cluster tant que l’instruction ALTER INDEX … REBUILD est en cours d’exécution.

REORGANIZE ne maintient pas ces verrous, et permet donc de continuer à effectuer des mises à jour sur la table cible de l’instruction, qui est toujours effectuée en ligne.

C’est donc pour cela que l’on doit utiliser :

=> REORGANIZE lorsque la fragmentation de l’index est faible (entre 5-10% et 25-30%)
=> REBUILD lorsque la fragmentation de l’index est plus élevée (plus de 25-30%).

Si l’on exécute une instruction ALTER INDEX … REBUILD en ligne avec l’option ONLINE, qui n’est disponible que dans les instances de SQL Server dont l’édition est Enterprise, deux index sont maintenus : l’ancien est supprimé et remplacé par le nouveau à la fin de l’opération. Cela permet l’accès en écriture sur la table, même si l’opération est plus longue et plus coûteuse. Mais cela est nécessaire lorsqu’on a une base de données dont la disponibilité doit être 24 x 7 x 366.

On notera qu’on ne peut pas reconstruire un index en ligne si :

– l’index est cluster et la table a au moins une colonne de type LOB (image, text, ntext varchar(max), nvarchar(max), varbinary(max)), spatial (geography et geometry) ou hierarchy, ou encore une colonne calculée,

– l’index est non-cluster, et porte sur des colonnes de type LOB, spatial ou hierarchy, ou que l’une des colonnes clé de l’index porte sur une colonne calculée.

Bonne maintenance des index !

ElSüket