Réorganisation et reconstruction d'index

Mis à jour : 17 juillet 2006

Le moteur de base de données SQL Server 2005 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.

Dans SQL Server 2005, 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.

L'algorithme de calcul de fragmentation est plus précis dans SQL Server 2005 que dans SQL Server 2000. Par conséquent, les valeurs de fragmentation semblent plus élevées. Par exemple, dans SQL Server 2000, une table n'est pas considérée comme fragmentée si sa page 11 et sa page 13 ne se trouvent pas dans la même étendue que sa page 12. Pourtant, étant donné que deux opérations d'E/S physiques seraient nécessaires pour accéder à ces deux pages, SQL Server 2005 en tient compte dans ses calculs de fragmentation.

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

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.

* 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 reconstruction ou la réorganisation des petits index ne réduit pas la fragmentation. Les pages des petits index sont stockées sur des extensions mixtes. Les extensions mixtes sont partagées par huit objets au plus ; 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 Pages et extensions.

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

En complément de la réorganisation d'un ou plusieurs index, les types de données d'objets volumineux (LOB) contenus dans l'index cluster ou la table sous-jacente sont compressé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.

* 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 : logique et 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 :

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 :

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 spécifie le mot clé ALL. Ainsi, tous les index associés à la table sont reconstruits. 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

Concepts

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

Autres ressources

ALTER INDEX (Transact-SQL)
Détermination de l'espace disque requis par les index
sys.dm_db_index_physical_stats
sys.dm_db_index_operational_stats
sys.indexes (Transact-SQL)
sys.dm_db_index_usage_stats

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications