Réorganiser et reconstruire des index
Cette rubrique explique comment réorganiser ou reconstruire un index fragmenté dans SQL Server 2012 à l'aide de SQL Server Management Studio ou de Transact-SQL. 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.
Vous pouvez remédier à la fragmentation des index en réorganisant un index ou en reconstruisant un index. Dans le cas d'index partitionnés reposant sur un schéma de partition, vous pouvez utiliser les méthodes suivantes sur la totalité ou sur une partition unique d'un index. La reconstruction d'un index entraîne sa suppression puis sa reconstruction. Ceci permet d'éviter toute fragmentation, de libérer de l'espace disque en compactant les pages d'après le paramètre du facteur de remplissage spécifié ou déjà existant et en retriant les lignes de l'index en pages contiguës. Quand ALL est précisé, tous les index sur la table sont supprimés puis reconstruits en une seule transaction. La réorganisation d'un index utilise des ressources système minimes. En effet, elle défragmente le niveau feuille des index cluster et non cluster sur les tables et les vues en retriant les pages de niveau feuille de façon physique afin de resuivre l'ordre logique, c'est-à-dire de gauche à droite, des nœuds. Cette opération compacte également les pages d'index. Le compactage s'appuie sur la valeur du facteur de remplissage existante.
Dans cette rubrique
Avant de commencer :
Détection de la fragmentation
Limitations et restrictions
Sécurité
Pour vérifier la fragmentation d'un index, à l'aide de :
SQL Server Management Studio
Transact-SQL
Pour réorganiser ou reconstruire un index, à l'aide de :
SQL Server Management Studio
Transact-SQL
Avant de commencer
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.
Limitations et restrictions
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.
Vous ne pouvez pas spécifier des options d'index lors de la réorganisation d'un index.
Sécurité
Autorisations
Nécessite une autorisation ALTER sur la table ou la vue. L'utilisateur doit être membre du rôle serveur fixe sysadmin ou des rôles de base de données fixes db_ddladmin et db_owner.
.gif "Icône de flèche utilisée avec le lien Retour en haut")
[Haut de la page]
Utilisation de SQL Server Management Studio
Pour vérifier la fragmentation d'un index
Dans l'Explorateur d'objets, développez la base de données qui contient la table sur laquelle vous souhaitez vérifier une fragmentation d'index.
Développez le dossier Tables.
Développez la table sur laquelle vous souhaitez vérifier une fragmentation d'index.
Développez le dossier Index.
Cliquez avec le bouton droit sur l'index dont vous voulez vérifier la fragmentation, puis sélectionnez Propriétés.
Sous Sélectionner une page, sélectionnez Fragmentation.
Les informations suivantes sont disponibles dans la page Fragmentation :
Remplissage de la page
Indique le remplissage moyen des pages d'index, sous forme de pourcentage. 100% signifie que les pages d'index sont totalement remplies. 50% signifie qu'en moyenne, chaque page d'index est remplie à moitié.Fragmentation totale
Pourcentage de fragmentation logique. Cette valeur indique le nombre de pages dans un index qui ne sont pas stockées dans l'ordre.Taille moyenne de ligne
Taille moyenne d'une ligne de niveau feuille.Profondeur
Nombre de niveaux dans l'index, notamment le niveau feuille.Enregistrements transférés
Nombre d'enregistrements d'un segment qui contiennent des pointeurs avant vers un autre emplacement de données. (Cet état se produit pendant une mise à jour, lorsque l'espace disponible est insuffisant pour stocker la nouvelle ligne à l'emplacement d'origine.)Lignes fantômes
Nombre de lignes marquées pour la suppression qui ne sont pas encore supprimées. Ces lignes seront supprimées par un thread de nettoyage, lorsque le serveur n'est pas occupé. Cette valeur ne comprend pas les lignes qui sont conservées à cause d'une transaction d'isolement d'instantané en suspens.Type d'index
Type de l'index. Les valeurs possibles sont Index cluster, Index non-cluster et XML primaire. Les tables peuvent également être stockées en tant que segment (sans index), mais dans ce cas la page Propriétés de l'index est impossible à ouvrir.Lignes de niveau feuille
Nombre de lignes de niveau feuille.Taille maximale de ligne
Taille maximale des lignes de niveau feuille.Taille minimale de ligne
Taille minimale des lignes de niveau feuille.Pages
Nombre total de pages de données.ID de partition
ID de partition de l'arbre B (B-tree) qui contient l'index.Enregistrement de version fantôme
Nombre d'enregistrements fantômes qui sont conservés à cause d'une transaction d'isolement d'instantané en suspens.
[Haut de la page]
Utilisation de Transact-SQL
Pour vérifier la fragmentation d'un index
Dans l'Explorateur d'objets, connectez-vous à une instance de Moteur de base de données.
Dans la barre d'outils standard, cliquez sur Nouvelle requête.
Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.
USE AdventureWorks2012; GO -- Find the average fragmentation percentage of all indexes -- in the HumanResources.Employee table. SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; GOL'instruction ci-dessus peut retourner un jeu de résultats similaire au suivant.
index_id name avg_fragmentation_in_percent ----------- ----------------------------------------------------- ---------------------------- 1 PK_Employee_BusinessEntityID 0 2 IX_Employee_OrganizationalNode 0 3 IX_Employee_OrganizationalLevel_OrganizationalNode 0 5 AK_Employee_LoginID 66.6666666666667 6 AK_Employee_NationalIDNumber 50 7 AK_Employee_rowguid 0 (6 row(s) affected)
Pour plus d'informations, consultez sys.dm_db_index_physical_stats (Transact-SQL).
[Haut de la page]
Utilisation de SQL Server Management Studio
Pour réorganiser ou reconstruire un index
Dans l'Explorateur d'objets, développez la base de données qui contient la table sur laquelle vous souhaitez réorganiser un index.
Développez le dossier Tables.
Développez la table sur laquelle vous souhaitez réorganiser un index.
Développez le dossier Index.
Cliquez avec le bouton droit sur l'index que vous souhaitez réorganiser et sélectionnez Réorganiser.
Dans la boîte de dialogue Réorganiser les index, vérifiez que l'index correct figure dans la grille Index à réorganiser, puis cliquez sur OK.
Cochez la case Compacter les données de la colonne d'objets volumineux pour indiquer que toutes les pages qui contiennent des données LOB seront aussi compactées.
Cliquez sur OK.
Pour réorganiser tous les index d'une table
Dans l'Explorateur d'objets, développez la base de données qui contient la table sur laquelle vous souhaitez réorganiser les index.
Développez le dossier Tables.
Développez la table sur laquelle vous souhaitez réorganiser les index.
Cliquez avec le bouton droit sur le dossier Index et sélectionnez des types de données LOB.
Dans la boîte de dialogue Réorganiser les index, vérifiez que les index corrects sont dans Index à réorganiser. Pour supprimer un index de la grille Index à réorganiser, sélectionnez l'index et appuyez sur la touche SUPPR.
Cochez la case Compacter les données de la colonne d'objets volumineux pour indiquer que toutes les pages qui contiennent des données LOB seront aussi compactées.
Cliquez sur OK.
Pour reconstruire un index
Dans l'Explorateur d'objets, développez la base de données qui contient la table sur laquelle vous souhaitez réorganiser un index.
Développez le dossier Tables.
Développez la table sur laquelle vous souhaitez réorganiser un index.
Développez le dossier Index.
Cliquez avec le bouton droit sur l'index que vous souhaitez réorganiser et sélectionnez Réorganiser.
Dans la boîte de dialogue Reconstruire les index, vérifiez que l'index correct figure dans la grille Index à reconstruire, puis cliquez sur OK.
Cochez la case Compacter les données de la colonne d'objets volumineux pour indiquer que toutes les pages qui contiennent des données LOB seront aussi compactées.
Cliquez sur OK.
[Haut de la page]
Utilisation de Transact-SQL
Pour réorganiser un index défragmenté
Dans l'Explorateur d'objets, connectez-vous à une instance de Moteur de base de données.
Dans la barre d'outils standard, cliquez sur Nouvelle requête.
Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.
USE AdventureWorks2012; GO -- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table. ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE ; GO
Pour réorganiser tous les index d'une table
Dans l'Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.
Dans la barre d'outils standard, cliquez sur Nouvelle requête.
Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.
USE AdventureWorks2012; GO -- Reorganize all indexes on the HumanResources.Employee table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; GO
Pour reconstruire un index défragmenté
Dans l'Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.
Dans la barre d'outils standard, cliquez sur Nouvelle requête.
Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. L'exemple reconstruit un seul index portant sur la table Employee.
USE AdventureWorks2012; GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO
Pour reconstruire tous les index d'une table
Dans l'Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.
Dans la barre d'outils standard, cliquez sur Nouvelle requête.
Copiez et collez l'exemple suivant dans la fenêtre de requête. L'exemple spécifie le mot de passe ALL. Ceci permet de reconstruire tous les index associés à la table. Trois options sont spécifiées.
USE AdventureWorks2012; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO
Pour plus d'informations, consultez ALTER INDEX (Transact-SQL).
[Haut de la page]
Voir aussi
Autres ressources
Meilleures pratiques de défragmentation d'index Microsoft SQL Server 2000