Réorganisation et reconstruction d'index

Le Moteur de base de données SQL Server gère automatiquement des index lorsque des opérations d'insertion, de mise à jour ou de suppression sont effectuées sur les données sous-jacentes. Au fil des modifications, les informations figurant dans l'index sont éparpillées dans la base de données (fragmentée). La fragmentation intervient lorsque des index possèdent des pages dans lesquelles l'organisation logique (reposant sur la valeur de la clé) ne correspond pas à l'organisation physique dans le fichier de données. Une fragmentation importante des index peut diminuer les performances des requêtes et ralentir la vitesse de réponse de votre application. Pour plus d'informations, consultez ce site Web de Microsoft.

Vous pouvez remédier à la fragmentation des index en réorganisant un index ou en reconstruisant un index. Dans le cas des index partitionnés reposant sur un schéma de partition, vous pouvez utiliser les méthodes suivantes sur la totalité d'un index ou sur une partition unique d'un index.

Détection de la fragmentation

Lorsque vous déterminez la méthode de défragmentation à adopter, la première étape consiste à analyser l'index pour évaluer son degré de fragmentation. La fonction système sys.dm_db_index_physical_stats vous permet de détecter la fragmentation dans un index spécifique, dans tous les index d'une table ou d'une vue indexée, dans tous les index d'une base de données ou dans tous les index de l'ensemble des bases de données. Pour les index partitionnés, sys.dm_db_index_physical_stats fournit également des informations de fragmentation pour chaque partition.

Le jeu de résultats renvoyé par la fonction sys.dm_db_index_physical_stats inclut les colonnes suivantes.

Colonne	Description
avg_fragmentation_in_percent	Pourcentage de fragmentation logique (pages non ordonnées dans un index).
fragment_count	Nombre de fragments (pages de feuille consécutives physiquement) dans l'index.
avg_fragment_size_in_pages	Nombre moyen de pages dans un fragment d'un index.

Une fois le degré de fragmentation connu, utilisez le tableau suivant pour déterminer la méthode la mieux adaptée pour corriger la fragmentation.

Valeur avg_fragmentation_in_percent	Instruction corrective
> 5 % et < = 30 %	ALTER INDEX REORGANIZE
> 30 %	ALTER INDEX REBUILD WITH (ONLINE = ON)*

* La reconstruction d'un index peut être exécutée en ligne ou hors connexion. La réorganisation d'un index s'effectue toujours en ligne. Pour obtenir le même niveau de disponibilité qu'avec l'option de réorganisation, vous devez reconstruire les index en ligne.

Ces valeurs fournissent des directives grossières pour déterminer le point auquel vous devez basculer entre ALTER INDEX REORGANIZE et ALTER INDEX REBUILD. Toutefois, les valeurs réelles peuvent varier d'un cas à l'autre. Il est important que vous fassiez des essais pour déterminer le meilleur seuil pour votre environnement.

Des niveaux très bas de fragmentation (inférieurs à 5 %) ne doivent pas être pris en compte par ces commandes, car l'avantage de la suppression d'un volume de fragmentation aussi réduit est quasiment toujours largement compensé par le coût de la réorganisation ou de la reconstruction de l'index.

[!REMARQUE]

En général, la fragmentation sur les petits index n'est pas contrôlable. Les pages des petits index sont stockées sur des extensions mixtes. Les extensions mixtes sont partagées par huit objets maximum ; par conséquent, la fragmentation dans un petit index peut ne pas être réduite après la réorganisation ou la reconstruction de l'index. Pour plus d'informations sur les extensions mixtes, consultez Fonctionnement des pages et étendues.

Exemple

L'exemple ci-dessous interroge la fonction de gestion dynamique sys.dm_db_index_physical_stats pour retourner la fragmentation moyenne pour tous les index sur la table Production.Product. D'après le tableau précédent, la solution conseillée consiste à réorganiser PK_Product_ProductID et à reconstruire les autres index.

USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

L'instruction peut retourner un jeu de résultats similaire au suivant.

index_id    name                        avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1           PK_Product_ProductID        15.076923076923077
2           AK_Product_ProductNumber    50.0
3           AK_Product_Name             66.666666666666657
4           AK_Product_rowguid          50.0

(4 row(s) affected)

Réorganisation d'un index

Pour réorganiser un ou plusieurs index, utilisez l'instruction ALTER INDEX avec la clause REORGANIZE. Cette instruction remplace l'instruction DBCC INDEXDEFRAG. Pour réorganiser une partition d'un index partitionné, utilisez la clause PARTITION de l'instruction ALTER INDEX.

La réorganisation d'un index défragmente le niveau de feuille des index cluster et non-cluster des tables ou des vues. En effet, les pages du niveau de feuille sont réorganisées physiquement afin qu'elles correspondent à l'organisation logique (de gauche à droite) des nœuds de feuilles. Les performances de l'analyse des index sont meilleures lorsque les pages sont ordonnées. L'index est réorganisé dans les pages existantes qui lui sont allouées ; aucune nouvelle page n'est allouée. Si un index recouvre plusieurs fichiers, ces fichiers sont réorganisés individuellement. Les pages ne migrent pas d'un fichier à l'autre.

La réorganisation entraîne une compression des pages d'index. Toutes les pages vides créées par cette compression sont supprimées, ce qui permet de libérer de l'espace disque. La compression est basée sur la valeur du facteur de remplissage (fill factor) dans la vue de catalogue sys.indexes.

Le processus de réorganisation utilise une petite quantité de ressources système. Par ailleurs, la réorganisation est effectuée automatiquement en ligne. Elle ne conserve pas les verrous persistants à long terme ; par conséquent, elle ne bloque pas les mises à jour ou les requêtes en cours.

Réorganisez un index lorsque celui-ci est peu fragmenté. Consultez le tableau précédent pour revoir les consignes concernant la fragmentation. Cependant, en cas de fragmentation importante de l'index, vous obtiendrez de meilleurs résultats en reconstruisant l'index.

Compression des types de données d'objet volumineux

Outre la réorganisation d'un ou de plusieurs index, les types de données d'objets volumineux (LOB) contenus dans l'index cluster ou la table sous-jacente sont compactés par défaut lorsqu'un index est réorganisé. Les types de données image, text, ntext, varchar(max), nvarchar(max), varbinary(max) et xml sont des types de données d'objet volumineux. La compression de ces données peut générer une meilleure utilisation de l'espace disque :

• La réorganisation d'un index cluster spécifié entraîne la compression de toutes les colonnes LOB contenues dans le niveau feuille (lignes de données) de l'index cluster.

• La réorganisation d'un index non-cluster entraîne la compression de toutes les colonnes LOB qui sont des colonnes non-clés (incluses) dans l'index.

• Lorsque le mot clé ALL est spécifié, tous les index associés à la table ou à la vue spécifiée sont réorganisés, et toutes les colonnes LOB associées à l'index cluster, la table sous-jacente ou l'index non-cluster contenant des colonnes incluses sont compressées.

• La clause LOB_COMPACTION est ignorée en l'absence de colonne LOB.

Reconstruction d'un index

La reconstruction d'un index implique la suppression de l'index et la création d'un nouvel index. Ainsi, la fragmentation est supprimée, l'espace disque est plus important grâce à la compression des pages à l'aide du paramètre de facteur de remplissage spécifié ou existant, et les lignes d'index sont réorganisées en pages contiguës (en allouant de nouvelles pages en fonction des besoins). Ceci peut améliorer les performances du disque car le nombre de lectures de pages nécessaire pour obtenir les données demandées est moins élevé.

Vous pouvez utiliser les méthodes suivantes pour reconstruire des index cluster et non-cluster :

• ALTER INDEX avec la clause REBUILD Cette instruction remplace l'instruction DBCC DBREINDEX.

• CREATE INDEX avec la clause DROP_EXISTING.

Toutes les méthodes ont le même rôle, mais chaque méthode présente des avantages et des inconvénients que vous devez prendre en compte, comme l'indique le tableau suivant.

Fonctionnalité	ALTER INDEX REBUILD	CREATE INDEX WITH DROP_EXISTING
La définition de l'index peut être modifiée en ajoutant ou en supprimant des colonnes de clé, en modifiant l'ordre des colonnes ou en modifiant l'ordre de tri des colonnes.*	Non	Oui**
Il est possible de définir ou de modifier des options d'index.	Oui	Oui
Il est possible de reconstruire plusieurs index lors d'une seule transaction.	Oui	Non
Il est possible de reconstruire la plupart des types d'index en ligne, sans bloquer les requêtes ou les mises à jour en cours.	Oui	Oui
Il est possible de partitionner de nouveau un index qui était partitionné.	Non	Oui
Il est possible de déplacer un index dans un autre groupe de fichiers.	Non	Oui
Une quantité complémentaire d'espace disque est requise temporairement.	Oui	Oui
La reconstruction d'un index cluster entraîne la reconstruction des index non-cluster associés.	Non Sauf si le mot clé ALL est spécifié.	Non Sauf si la définition de l'index a changé.
Il est possible de reconstruire les index qui appliquent des contraintes PRIMARY KEY et UNIQUE sans pour autant supprimer puis recréer les contraintes.	Oui	Oui
Il est possible de reconstruire une partition d'un index.	Oui	Non

* Il est possible de convertir un index non-cluster en type d'index cluster en spécifiant CLUSTERED dans la définition de l'index. Pour cette opération, la valeur OFF doit être affectée à l'option ONLINE. La conversion d'un index cluster en index non-cluster n'est pas possible, quelle que soit la valeur du paramètre ONLINE.

** Si l'index est recréé en utilisant les mêmes nom, colonnes et ordre de tri, l'opération de tri peut être omise. Lors de l'opération de reconstruction, les lignes sont vérifiées afin de déterminer si elles sont triées pendant la construction de l'index.

Vous pouvez également reconstruire un index en supprimant d'abord l'index via l'instruction DROP INDEX, puis en le recréant via une instruction CREATE INDEX indépendante. L'exécution de ces opérations en tant qu'instructions indépendantes présente certains inconvénients ; il est donc déconseillé d'utiliser cette procédure.

Désactivation des index non-cluster pour conserver l'espace disque nécessaire lors des opérations de reconstruction

Lorsqu'un index non-cluster est désactivé, les lignes de données de l'index sont supprimées, mais la définition de l'index est conservée dans les métadonnées. L'index est activé lorsqu'il est reconstruit. Lorsque l'index non-cluster n'est pas désactivé, l'opération de reconstruction nécessite une quantité d'espace disque temporaire permettant de stocker l'ancien et le nouvel index. Cependant, en désactivant et en reconstruisant un index non-cluster dans des transactions distinctes, l'espace disque rendu disponible grâce à la désactivation de l'index peut être réutilisé pour l'opération suivante de reconstruction ou pour toute autre opération. Aucun espace supplémentaire n'est requis, à part l'espace disque temporaire nécessaire au tri, soit en principe 20 % de la taille de l'index. Si l'index non-cluster est situé sur la clé primaire, toutes les contraintes FOREIGN KEY inactives de référence sont désactivées automatiquement. Ces contraintes doivent être activées manuellement une fois que l'index a été reconstruit. Pour plus d'informations, consultez Désactivation d'index et Indications sur l'activation des index et des contraintes.

Reconstruction d'index volumineux

Les index possédant plus de 128 extensions sont reconstruits en deux phases distinctes : une phase logique et une phase physique. Dans la phase logique, les unités d'allocation utilisées par l'index sont signalées comme devant être désallouées, les lignes de données sont copiées et triées, puis elles sont déplacées vers les nouvelles unités d'allocation ayant été créées pour stocker l'index reconstruit. Dans la phase physique, les unités d'allocation préalablement signalées pour être désallouées sont supprimées physiquement dans des transactions courtes qui interviennent en arrière-plan et nécessitent peu de verrous. Pour plus d'informations, consultez Suppression et reconstruction d'objets volumineux.

Définition des options d'index

Vous ne pouvez pas spécifier des options d'index lors de la réorganisation d'un index. Les options d'index suivantes peuvent toutefois être définies lorsque vous reconstruisez un index à l'aide de l'instruction ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING :

PAD_INDEX	DROP_EXISTING (CREATE INDEX uniquement)
FILLFACTOR	ONLINE
SORT_IN_TEMPDB	ALLOW_ROW_LOCKS
IGNORE_DUP_KEY	ALLOW_PAGE_LOCKS
STATISTICS_NORECOMPUTE	MAXDOP

[!REMARQUE]

Si une opération de tri n'est pas indispensable ou si le tri peut être effectué en mémoire, l'option SORT_IN_TEMPDB est ignorée.

Qui plus est, la clause SET de l'instruction ALTER INDEX vous permet de définir les options d'index suivantes sans reconstruire l'index :

ALLOW_PAGE_LOCKS	IGNORE_DUP_KEY
ALLOW_ROW_LOCKS	STATISTICS_NORECOMPUTE

Pour plus d'informations, consultez Configuration des options d'index.

Pour reconstruire ou réorganiser un index

ALTER INDEX (Transact-SQL)

Pour reconstruire un index en supprimant puis en recréant un index en une seule opération

CREATE INDEX (Transact-SQL)

Exemples

A. Reconstruction d'un index

L'exemple suivant reconstruit un seul index.

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

B. Reconstruction de tous les index d'une table et spécification des options

L'exemple suivant indique le mot clé ALL. Ceci permet de reconstruire tous les index associés à la table. Trois options sont spécifiées.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. Réorganisation d'un index à l'aide de la compression des types de données d'objet volumineux

L'exemple suivant réorganise un seul index cluster. Étant donné que l'index contient un type de données d'objet volumineux (LOB) dans le niveau feuille, l'instruction compresse aussi toutes les pages qui contiennent les données LOB. Notez qu'il n'est pas nécessaire de spécifier l'option WITH (LOB_Compaction) car la valeur par défaut est ON.

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

Voir aussi

Référence

ALTER INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.indexes (Transact-SQL)

sys.dm_db_index_usage_stats (Transact-SQL)

Concepts

Création d'index (Moteur de base de données)

Désactivation d'index

Autres ressources

Détermination de l'espace disque requis par les index