Les développeurs l’ignorent trop souvent mais les index en cluster sont très importants pour les performances et les opérations de maintenance des bases de données. C’est tellement vrai qu’il n’est pas possible par exemple d’utiliser les tables avec SQL Azure sans index en cluster : S’il est possible de créer des tables sous SQL Azure, l’insertion de lignes dans lesdites tables est interdite en l’absence d’index en cluster.

SQL Server fonctionne parfaitement avec des index en cluster, à l’image de IBM DB2, ces index sont essentiels pour le bon fonctionnement de SQL Server. A l’opposé, avec Oracle, les index en cluster qui s’appellent Table Organisé en Index (IOT) restent l’exception.

Qu’est-ce que sont les index en cluster ?

Les index en cluster sont identiques aux index non cluster. Il s’agit dans les deux cas d’index B-TREE (arbre balancé, c’est-à-dire que quelque-soit la donnée recherchée, il faudra toujours parcourir le même nombre de niveau intermédiaire pour arriver à ladite donnée). La seule différence n’est donc pas à chercher dans les niveaux racine et intermédiaires de l’index, mais au niveau feuille. Dans un index non cluster, le niveau feuille contient la liste des pointeurs vers la ligne de la table sous-jacente. Dans le cas d’un index en cluster, c’est la table elle-même que l’on retrouve au niveau feuille. Etant donné que la table ne peut apparaitre qu’une fois dans la base de données, cela explique qu’il ne peut exister au plus qu’un seul index en cluster par table.

Alors quel est l’avantage des index en cluster pour SQL Server ?

L’avantage premier est d’abord en termes de performance. Par exemple, lorsque l’index en cluster est utilisé pour rechercher une donnée, le nombre de pages à lire est moindre qu’un index non cluster : Au niveau feuille, nous avons la donnée et non un pointeur de ligne, ainsi nous faisons l’économie de la page de pointeurs de ligne.

Un autre avantage est que la table est stockée triée par rapport aux colonnes de la clé d’indexation (que nous appellerons ici colonnes-clés pour simplifier la lecture) de l’index en cluster. Dans le cas de clause “ORDER BY” sur les colonnes-clés, le temps de traitement est réduit.

Il existe aussi un avantage significatif pour les index en cluster, c’est la défragmentation des tables. SQL Server ne sait pas défragmenter les tables : aucune fonction-système ne permet de le faire. Alors pour défragmenter une table, nous défragmentons son index en cluster. Comme la défragmentation des index (REORGANIZE et REBUILD), défragmente aussi le niveau feuille, la défragmentation des index en cluster permet la défragmentation de la table. N’oublions pas que la défragmentation permet de réduire le nombre de pages à charger pour l’index/table, donc un gain intéressant en termes de performance.

Quel est le meilleur choix pour les colonnes-clés des index en cluster ?

Il s’agit d’une question importante. En effet, nous avons vu ci-dessus que la table est triée du fait de l’existence d’un index en cluster. En cas d’ajout d’une nouvelle ligne (INSERT), celle-ci est ajouté à l’endroit correspondant au tri. Donc, deux cas de figure se présentent :

1. Le plus simple : la/les colonnes-clé de la nouvelle ligne ont la plus grande valeur de la table, alors la nouvelle ligne est insérée simplement à la fin de l’index. Cette opération est simple et peu couteuse.
2. Si la/les colonnes-clé de la nouvelle ligne ont une valeur quelconque par rapport à l’index, alors la ligne sera insérée dans l’index. Si l’index est peu fragmenté (cas normal si les opérations de maintenance sont régulièrement exécutées), alors un éclatement des pages (SPLIT) de la table (ainsi que probablement des pages des niveaux intermédiaires de l’index) va se produire. Le résultat est une fragmentation de l’index et donc un nombre de pages de la table et de l’index en cluster plus important. De plus, l’opération de SPLIT est couteuse en opérations.

En résumé, pour éviter la fragmentation importante de l’index en cluster et de la table sous-jacente, il est possible de jouer sur le paramètre FILL FACTOR, augmenter d’autant le nombre de pages. Nous préfèrerons l’autre stratégie à savoir bien choisir les colonnes-clé de l’index. L’idée ici est de trouver des colonnes qui sont insérés présentant une évolution croissante, comme par exemple une colonne auto-incrémentée (IDENTITY) ou une date (dans le cas d’un journal d’évènements par exemple). A l’opposé, des noms de personnes ou des GUID sont de piètres candidats comme colonnes-clés. Enfin, autre possibilité, la table peut n’être mise à jour d’occasionnellement et de manière limitée.

Bien à vous.