Le but d’un index dans SQL Server est fortement similaire à l’index d’un livre en ce sens qu’il permet à l’utilisateur de retrouver une information plus rapidement que s’il fallait parcourir le livre dans son intégralité.
Les index de SQL Server sont créés au niveau des colonnes d’une table ou d’une vue. Un index contient des données permettant d’accélérer la localisation des valeurs des colonnes sur lesquelles il est construit. Lorsqu’un index est créé sur la clé primaire d’une table T, celui-ci sera utilisé par SQL Server dès qu’aura lieu une recherche d’une ligne de la table T basée sur cette clé primaire. SQL Server récupèrera dans l’index l’information lui permettant de localiser la ligne correspondante de la table T. Cela signifie que SQL Server n’est pas obligé d’effectuer un parcours complet de la table (full table scan) pour retrouver une ligne de données, ce qui est engendre un gain de performance substantiel car l’opération est plus rapide et moins consommatrice en ressources.
Les index relationnels peuvent être créés même avant que des données ne soient insérées dans la table sur laquelle ils sont basés. Il est également possible de créer des indexes sur des tables et des vues d’autres bases de données.
CREATE INDEX MyIndex ON MyTable (Column1);
Vous trouverez plus d’information sur la commande Transact-SQL CREATE INDEX sur MSDN.
Après qu’un index ait été créé pour une table T, il y aura de façon transparente des maintenances automatiques effectuées par le moteur de base de données de SQL Server dès qu’une opération INSERT, UPDATE, DELETE a lieu sur les données de la table T.
Cependant, ces maintenances automatiques vont continuellement disperser les informations contenues par l’index au sein de la base de données, fragmentant l’index au cours du temps. Résultat des courses: les index ont maintenant des pages dont l’ordre logique (basé sur la valeur de clé) diffère de l’ordre physique au sein du fichier de données. Cela signifie qu’il y a un grand pourcentage d’espace libre dans les pages d’index et que SQL Server doit lire un nombre de pages plus important lorsqu’il scanne chaque index. De plus, l’ordre des pages qui appartiennent au même index se retrouvent mélangées et cela requiert un travail supplémentaire de la part de SQL Server lors d’une lecture d’un index et particulièrement en termes d’entrées/sorties.
L’impact de la fragmentation des index sur SQL Server peut aller d’une simple baisse de l’efficacité des requêtes (pour les serveurs avec un impact faible sur les performances) jusqu’à la décision par SQL Server de ne plus utiliser les index et se remettre à faire des lectures complètes des tables (full table scans) quelle que soit la requête. Comme mentionné précédemment, les full table scans vont avoir un impact direct et important sur les performances de SQL Server. C’est la dernière alarme vous indiquant qu’il y a une fragmentation sur les index d’une base de données SQL Server.
La solution à la fragmentation des index est de les réorganiser ou de les reconstruire.
Mais, avant de considérer la maintenance des index il est important de répondre à deux questions:
1. Quel est le degré de fragmentation?
2. Quelle est l’action la plus appropriée? Réorganiser ou reconstruire?
Détecter la fragmentation
Généralement, pour résoudre un problème, il est essentiel de d’abord le localiser et d’isoler la partie affectée avant d’appliquer la solution adéquate.
La fragmentation peut être détectée facilement en appelant la fonction système sys.dm_db_index_physical_stats, qui retourne des informations sur la taille et la fragmentation des index sur des tables ou des vues de SQL Server. Cette fonction peut être exécutée pour récupérer les informations d’un index en particulier, tous les index d’une table donnée ou tous les index de la base de donnée:
Les résultats retournés après exécution de la procédure incluent les informations suivantes:
- avg_fragmentation_in_percent – pourcentage moyen de pages incorrectes dans l’index
- fragment_count – nombre de fragments dans l’index
- avg_fragment_size_in_pages – nombre moyen de pages dans un fragment d’index
Analyser les résultats d’une détection
Après que la fragmentation ait été détectée, il faut déterminer l’impact de cette fragmentation sur SQL Server puis l’action à effectuer pour chaque cas.
Il n’y a aucune information exacte sur la quantité minimum de fragmentation à partir de laquelle elle peut engendrer des problèmes spécifiques dans SQL Server et causer des problèmes de performance ou de congestion, d’autant plus que les environnements SQL Server varient d’un système à un autre.
Cependant, il existe une solution généralement acceptée basée sur le pourcentage de fragmentation (la colonne avg_fragmentation_in_percent retourné par la fonction système sys.dm_db_index_physical_stats décrite plus haut):
- Fragmentation sous 10% – pas de défragmentation requise. Il est généralement accepté dans la majorité des environnements qu’une fragmentation sous les 10% est négligeable et que son impact sur les performances est négligeable
- Fragmentation entre 10% et 30% – il est suggéré d’effectuer une réorganisation de l’index
- Fragmentation au dessus des 30% – il est suggéré de reconstruire l’index
Voici le raisonnement qui a poussé à cette solution basée sur des seuils qui pourrait vous aider à déterminer si l’on doit effectuer une reconstruction ou une réorganisation:
La réorganisation des index est un processus où SQL Server parcourt un index existant et le remet en ordre. La reconstruction d’un index est un processus lourd où l’index est supprimé et recréé de toute pièce dans une nouvelle structure, libérée de toute fragmentation ou de tout espace entre ses pages.
Alors que la réorganisation est un processus de nettoyage de structure qui laisse le système en l’état sans verrouiller les tables ou les vues affectées, le processus reconstruction verrouille la table ou la vue sur laquelle l’index considéré est construit pour toute la durée de la reconstruction. Cela signifie que de longues périodes d’arrêt peuvent survenir et ce n’est pas toujours une situation acceptable dans certains environnements.
Si nous gardons cela à l’esprit, nous pouvons clairement considérer le processus de reconstruction d’index comme la “manière forte” pour résoudre le problème, en gardant à l’esprit qu’elle a un coût à savoir le verrouillage plus ou moins long de l’objet sur lequel l’index est basé.
D’un autre côté, la réorganisation d’index est une solution à moindre coût qui résoudra le problème de la fragmentation de manière moins efficace vu que l’index nettoyé sera toujours moins performant qu’un index reconstruit de toute pièce. Mais la réorganisation est bien meilleure du point de vue efficacité puisqu’il ne verrouille pas l’objet sous-jacent pendant l’opération.
Les serveurs avec des périodes de maintenance régulières (par exemple, chaque week-end) devraient théoriquement toujours opter pour une reconstruction d’index, peu importe le pourcentage de fragmentation vu que ces environnements seront à peine affectés par des verrouillages sur les objets associés imposés par les reconstructions d’index.
Comment réorganiser et reconstruire un index:
Avec SQL Server Management Studio (SSMS):
- Dans l’explorateur d’objets, naviguer jusqu’à la base de données de votre choix en étendant le noeud Database (Base de données) sous le noeud correspondant à votre instance SQL Server
- Etendre le noeud correspondant à la base de données présentant une fragmentation sur un de ses index
- Aller dans le noeud Tables et aller sur la table avec un index fragmentée
- Développer le noeud de cette table
- Développer le noeud Indexes
-
Cliquer droit sur l’index fragmenté et sélectionner l’option Rebuild (Reconstruire) ou Reorganize (Réorganiser) dans le menu contextuel (selon l’action que vous voulez faire)
- Cliquer sur le bouton OK et attendez que le processus se termine
Réorganiser les index d’une table en Transact SQL
Munissez vous du nom de la base de données et de la table où un index fragmenté a été détecté, ajustez puis exécutez le code suivant dans SSMS pour réorganiser tous les index de la table:
USE MyDatabase; GO ALTER INDEX ALL ON MyTable REORGANIZE; GO
Reconstruction des index d’une table en Transact SQL
Munissez vous du nom de la base de données et de la table où un index fragmenté a été détecté, ajustez puis exécutez le code suivant dans SSMS pour reconstruire tous les index de la table:
USE MyDatabase; GO ALTER INDEX ALL ON MyTable REBUILD; GO
September 1, 2017
