Réorganisation et reconstruction d'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.

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.

Exemple

USE AdventureWorks2008R2;
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

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

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 : 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 :

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

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

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

USE AdventureWorks2008R2;
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

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

Voir aussi

Référence

Concepts

Autres ressources