Notes
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
Cette rubrique explique comment réorganiser ou reconstruire un index fragmenté dans SQL Server 2014 à l’aide de SQL Server Management Studio ou de Transact-SQL. Le moteur de base de données SQL Server gère automatiquement les index chaque fois que les opérations d’insertion, de mise à jour ou de suppression sont effectuées sur les données sous-jacentes. Avec le temps, ces modifications peuvent provoquer la fragmentation des informations de l'index dans la base de données. La fragmentation existe lorsque les index ont des pages dans lesquelles l’ordre logique, basé sur la valeur de clé, ne correspond pas à l’ordre physique dans le fichier de données. Les index fortement fragmentés peuvent dégrader les performances des requêtes et entraîner une réponse lente de votre application.
Vous pouvez remédier à la fragmentation d'index en réorganisant ou en reconstruisant un index. Pour les index partitionnés basés sur un schéma de partition, vous pouvez utiliser l’une de ces méthodes sur un index complet ou une partition unique d’un index. La reconstruction d'un index entraîne sa suppression puis sa recréation. Cela supprime la fragmentation, récupère l’espace disque en compactant les pages en fonction du paramètre de facteur de remplissage spécifié ou existant, et réorganise les lignes d’index dans les pages contiguës. Lorsque ALL est spécifié, tous les index de la table sont supprimés et reconstruits dans une seule transaction. La réorganisation d’un index utilise des ressources système minimales. Il défragmente le niveau de la feuille des index clusterisés et non clusterisés sur les tables et les vues en réorganisant physiquement les pages du niveau de la feuille pour qu'elles correspondent à l'ordre logique, de gauche à droite, des nœuds de la feuille. La réorganisation compacte également les pages d’index. Le compactage est basé sur la valeur de facteur de remplissage existante.
Dans cette rubrique
Avant de commencer :
Pour vérifier la fragmentation d’un index, utilisez :
Pour réorganiser ou reconstruire un index à l’aide de :
Avant de commencer
Détection de la fragmentation
La première étape de la détermination de la méthode de défragmentation à utiliser consiste à analyser l’index pour déterminer le degré de fragmentation. En utilisant la fonction système sys.dm_db_index_physical_stats, vous pouvez détecter la fragmentation dans un index spécifique, tous les index d’une table ou d’une vue indexée, tous les index d’une base de données ou tous les index de toutes les 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 retourné par la fonction sys.dm_db_index_physical_stats inclut les colonnes suivantes.
| Colonne | Descriptif |
|---|---|
| fragmentation_moyenne_en_pourcentage | Pourcentage de fragmentation logique (pages hors ordre dans l’index). |
| nombre_de_fragments | Nombre de fragments (pages feuille physiquement consécutives) dans l’index. |
| taille_moyenne_des_fragments_en_pages | Nombre moyen de pages d’un fragment dans un index. |
Une fois que le degré de fragmentation est connu, utilisez le tableau suivant pour déterminer la meilleure méthode pour corriger la fragmentation.
| avg_fragmentation_in_percent valeur | Déclaration corrective |
|---|---|
| > 5% et < = 30% | ALTER INDEX REORGANIZE |
| > 30% | ALTER INDEX REBUILD WITH (ONLINE = ON) 1 |
1 La reconstruction d’un index peut être exécutée en ligne ou hors connexion. La réorganisation d’un index est toujours exécutée en ligne. Pour obtenir une disponibilité similaire à l’option de réorganisation, vous devez reconstruire les index en ligne.
Conseil / Astuce
Ces valeurs fournissent une directive approximative 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 expérimentiez pour déterminer le meilleur seuil pour votre environnement. Par exemple, si un index donné est utilisé principalement pour les opérations d’analyse, la suppression de la fragmentation peut améliorer les performances de ces opérations. L’avantage en matière de performances est moins visible pour les index utilisés principalement pour les opérations de recherche. De même, la suppression de la fragmentation dans un amas (une table sans index clusterisé) est particulièrement utile pour les opérations de balayage d'index non clusterisé, mais a peu d'impact dans les opérations de recherche.
Les niveaux très faibles de fragmentation (inférieurs à 5 %) ne doivent généralement pas être traités par l’une de ces commandes, car l’avantage de supprimer une telle petite quantité de fragmentation est presque toujours largement compensé par le coût de réorganisation ou de reconstruction de l’index.
Remarque
La reconstruction ou la réorganisation de petits index ne réduit souvent pas la fragmentation. Les pages de petits index sont parfois stockées sur des étendues mixtes. Les étendues mixtes sont partagées par jusqu’à huit objets, de sorte que la fragmentation dans un petit index peut ne pas être réduite après la réorganisation ou la reconstruction.
Considérations relatives à la défragmentation d’index
Dans certaines conditions, la reconstruction d’un index cluster régénère automatiquement tout index non cluster qui référence la clé de clustering, si les identificateurs physiques ou logiques contenus dans les enregistrements d’index non cluster doivent changer.
Scénarios qui forcent la reconstruction automatique de tous les index non cluster sur une table :
- Création d’un index cluster sur une table
- Suppression d’un index clusterisé, ce qui entraîne le stockage de la table en tant que tas
- Modification de la clé de clustering pour inclure ou exclure des colonnes
Scénarios qui n’exigent pas que tous les index non cluster soient automatiquement reconstruits sur une table :
- Reconstruction d’un index cluster unique
- Reconstruction d’un index cluster non unique
- Modification du schéma d’index, par exemple l’application d’un schéma de partitionnement à un index cluster ou le déplacement de l’index cluster vers un autre groupe de fichiers
Limitations et restrictions
Les index avec plus de 128 étendues sont reconstruits en deux phases distinctes : logique et physique. Dans la phase logique, les unités d’allocation existantes utilisées par l’index sont marquées pour la désallocation, les lignes de données sont copiées et triées, puis déplacées vers de nouvelles unités d’allocation créées pour stocker l’index reconstruit. Dans la phase physique, les unités d’allocation précédemment marquées pour la désallocation sont physiquement supprimées dans de courtes transactions qui se produisent en arrière-plan et ne nécessitent pas de nombreux verrous. Pour plus d’informations sur les étendues, reportez-vous au Guide d’architecture des pages et des étendues.
L’instruction ALTER INDEX REORGANIZE nécessite que le fichier de données contenant l’index dispose d’un espace disponible, car l’opération ne peut allouer que des pages de travail temporaires sur le même fichier, et non un autre fichier dans le groupe de fichiers. Ainsi, même si le groupe de fichiers peut avoir des pages gratuites disponibles, l’utilisateur peut toujours rencontrer l’erreur 1105 : Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
La création et la reconstruction d’index non alignés sur une table avec plus de 1 000 partitions sont possibles, mais n’est pas recommandée. Ces opérations peuvent entraîner une dégradation des performances ou une consommation de mémoire excessive.
Un index ne peut pas être réorganisé ou reconstruit si le groupe de fichiers dans lequel il se trouve est hors connexion ou défini sur lecture seule. Lorsque le mot clé ALL est spécifié et qu’un ou plusieurs index se trouvent dans un groupe de fichiers hors connexion ou en lecture seule, l’instruction échoue.
Sécurité
Autorisations
Nécessite l’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 .
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 la fragmentation d’un index.
Développez le dossier Tables .
Développez la table sur laquelle vous souhaitez vérifier la fragmentation d’un index.
Développez le dossier Index.
Cliquez avec le bouton droit sur l’index dont vous souhaitez vérifier la fragmentation et sélectionnez Propriétés.
Sous Sélectionner une page, sélectionnez Fragmentation.
Les informations suivantes sont disponibles sur la page Fragmentation :
Remplissage de la page
Indique l’intégralité moyenne des pages d’index, sous la forme d’un pourcentage. 100% signifie que les pages d’index sont entièrement complètes. 50% signifie que, en moyenne, chaque page d’index est à moitié pleine.Fragmentation totale
Pourcentage de fragmentation logique. Cela indique le nombre de pages d’un index qui ne sont pas stockées dans l’ordre.Taille moyenne des lignes
Taille moyenne d'une ligne au niveau de la feuille.Profondeur
Nombre de niveaux dans l’index, y compris le niveau feuille.Enregistrements transférés
Nombre d’enregistrements dans un tas qui ont des pointeurs avant vers un autre emplacement de données. (Cet état se produit pendant une mise à jour, lorsqu’il n’y a pas suffisamment de place pour stocker la nouvelle ligne à l’emplacement d’origine.)Lignes fantômes
Nombre de lignes marquées comme supprimées, mais pas encore supprimées. Ces lignes sont supprimées par un thread de nettoyage, lorsque le serveur n’est pas occupé. Cette valeur n’inclut pas les lignes qui sont conservées en raison d’une transaction d'isolement d'instantané en cours.Type d’index
Type d’index. Les valeurs possibles sont l’index cluster, l’index non cluster et le code XML principal. Les tables peuvent également être stockées en tant que tas (sans index), mais cette page Propriétés de l’index ne peut pas être ouverte.Lignes au niveau feuille
Nombre de lignes de niveau de la feuille.Taille maximale des lignes
Taille maximale des lignes de niveau feuille.Taille de ligne minimale
Taille minimale des lignes de niveau feuille.Pages
Nombre total de pages de données.ID de partition
ID de partition de l’arborescence b contenant l’index.Lignes fantômes de version
Nombre d’enregistrements fantômes conservés en raison d’une transaction d’isolation d'un instantané en attente.
Utilisation de Transact-SQL
Pour vérifier la fragmentation d’un index
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 -- 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 pourrait retourner un ensemble de résultats similaire à ce qui suit.
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).
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 à réorganiser et sélectionnez Réorganiser.
Dans la boîte de dialogue Réorganiser les index , vérifiez que l’index correct se trouve dans la grille des index à réorganiser , puis cliquez sur OK.
Cochez la case Compacter les données de colonne d'objet volumineux pour spécifier que toutes les pages contenant des données d'objet volumineux (LOB) sont également 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 Réorganiser tout.
Dans la boîte de dialogue Réorganiser les index, vérifiez que les index corrects se trouvent dans les index à réorganiser. Pour supprimer un index de la grille réorganisée , sélectionnez l’index, puis appuyez sur la touche Supprimer.
Activez la case à cocher Compacter les données de colonne d’objet volumineux pour indiquer que toutes les pages qui contiennent des données LOB (Large Object) sont également 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 à réorganiser et sélectionnez Réorganiser.
Dans la boîte de dialogue Reconstruire les index , vérifiez que l’index correct se trouve dans la grille des index à reconstruire , puis cliquez sur OK.
Activez la case à cocher Compacter les données de colonne d’objet volumineux pour spécifier que toutes les pages qui contiennent des données LOB (Large Object) sont également compactées.
Cliquez sur OK.
Utilisation de Transact-SQL
Pour réorganiser 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.
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 sur la
Employeetable.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 requête L’exemple spécifie le mot clé
ALL. Cela reconstruit 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).
Voir aussi
Meilleures pratiques de défragmentation d’index Microsoft SQL Server 2000