Réorganiser un index SQL Server

Script pour vérifier puis réorganiser un index fragmenté dans une base de données SQL Server.

Une opération de maintenance permet de défragmenter le niveau inférieur des index en réordonnant physiquement les pages, de gauche à droite. Voici un script pour réorganiser un index SQL Server. L’opération de réorganisation compacte également les pages de l’index. Pour mieux comprendre, imaginez un livre de recettes dont les pages ne sont plus ordonnées. Vous devez alors réorganiser les pages afin d’atteindre rapidement la page souhaitée.

Comment réorganiser un index SQL Server fragmenté ?

Premièrement, considérons l’index dans ce tutoriel avec des exemples pour créer un index SQL Server non cluster sur une table des ventes. L’index utilise les principaux filtres sur la table des ventes à partir de la dimension temporelle, c’est-à-dire les colonnes année et mois. Après beaucoup d’insertion et de suppression, les données ne sont plus dans un ordre logique croissant sur les disques. C’est à dire que les index sont fragmentés.

Pour réorganiser les données d’un index et améliorer les performances il faut réorganiser les indexs régulièrement. Ou encore lorsque la fragmentation dépasse un pourcentage.

Vérifier le pourcentage de fragmentation sur toutes les tables SQL Server

Deuxièmement, cette requête permet d’afficher toutes les statistiques pour toutes les tables et indexe d’une base MSSQL. La requête n’affiche pas seulement les données mais elle les stocke dans une table en dur pour permettre de filtrer et trier les données avec la deuxième requête.

Considérer la colonne avg_fragmentation_in_percent, c’est à dire la fragmentation moyenne en pourcentage affiche donc l’information pour chaque index.

SELECT	* 
INTO	dbo.dm_db_index_physical_stats_TEMP
FROM	sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

SELECT		* 
FROM		dm_db_index_physical_stats_TEMP
ORDER BY	avg_fragmentation_in_percent DESC;

Réorganiser un index SQL Server pour optimiser les performances

A partir de quel pourcentage faut-il construire ou organiser à nouveau les index? C’est une bonne pratique SQL de réorganiser les index si le pourcentage de fragmentation se situe entre 15 et 30%. Par contre, au-dessus de 30%, construire à nouveau l’index avec la commande REBUILD.

ALTER INDEX indexYearMonth 
	ON [dbo].[SALES] REORGANIZE;

Différence entre reconstruction et réorganisation des index SQL Server

La reconstruction d’un index reconstruit les données et la structure de l’index. C’est un processus visant à récupérer l’espace inutilisé dans l’index. En effet, la reconstruction d’un index SQL consiste à recréer un index en lisant toutes les pages de données de la table. Puis les trier en fonction de leurs valeurs clés.

Tandis que la réorganisation d’un index change l’ordre des pages de l’index sans le reconstruire. Réorganiser un index est plus rapide que le reconstruire. La reconstruction mal effectuée entraîne une fragmentation. L’opération consiste à réorganiser les pages de niveau feuille afin d’améliorer l’accès aux lignes d’une table.

La réorganisation d’un index SQL peut être utile si une fragmentation s’est produite, ou si des modifications importantes ont été apportées à la table qui affectent sa structure. Cet article montre comment réorganiser un index fragmenté avec un exemple simple. Pour aller plus loin, consulter la documentation de Microsoft sur les raisons de vérifier la fragmentation de l’index et comment reconstruire et réorganiser les index.

Voici le premier tutoriel de la série sur les vues avec un script pour créer une vue SQL Server.