Resolve index fragmentation by reorganizing or rebuilding indexes

• 03/19/2020
• 21 minutes to read
• • p
  • j
  • M
  • M
  • d
  • +11

Applies to: SQL Server (toutes les versions prises en charge) Base de données SQL Azure Instance gérée SQL Azure Azure Synapse Analytics Entrepôt de données parallèle

Cet article décrit comment la défragmentation de l’index se produit et discute de son impact sur les performances des requêtes. Une fois que vous avez déterminé la quantité de fragmentation qui existe pour un index, vous pouvez défragmenter un index en réorganisant un index ou en le reconstruisant en exécutant des commandes Transact-SQL dans l’outil de votre choix ou en utilisant SQL Server Management Studio.

Aperçu de la fragmentation de l’index

Qu’est-ce que la fragmentation de l’index et pourquoi devrais-je m’en soucier:

• La fragmentation existe lorsque les index ont des pages dans lesquelles l’ordre logique dans l’index, basé sur la valeur clé de l’index, ne correspond pas à l’ordre physique dans les pages d’index.
• Le moteur de base de données modifie automatiquement les index chaque fois que des opérations d’insertion, de mise à jour ou de suppression sont effectuées sur les données sous-jacentes. Par exemple, l’ajout de lignes dans une table peut entraîner la division des pages existantes dans les index rowstore pour laisser la place à l’insertion de nouvelles valeurs de clé. Au fil du temps, ces modifications peuvent entraîner la dispersion des informations de l’index dans la base de données (fragmentée). La fragmentation existe lorsque les index ont des pages dans lesquelles l’ordre logique, basé sur la valeur de la clé, ne correspond pas à l’ordre physique dans le fichier de données.
• Des index fortement fragmentés peuvent dégrader les performances des requêtes car des E/S supplémentaires sont nécessaires pour localiser les données auxquelles l’index pointe. Plus d’E / S font que votre application réagit lentement, en particulier lorsque des opérations d’analyse sont impliquées.

Détection de la quantité de fragmentation

La première étape pour décider de la méthode de défragmentation d’index à utiliser consiste à analyser l’index pour déterminer le degré de fragmentation. Vous détectez la fragmentation différemment pour les index rowstore et les index columnstore.

Détection de la fragmentation des index rowstore

À l’aide de 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 renvoyé par sys.dm_db_index_physical_stats inclut les colonnes suivantes:

Colonne	Description
avg_fragmentation_in_percent	Le pourcentage de fragmentation logique (pages hors service dans l’index).
fragment_count	Le nombre de fragments (pages feuilles physiquement consécutives) 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 meilleure méthode pour supprimer la fragmentation : RÉORGANISATION de l’INDEX ou INDEX.

avg_fragmentation_in_percent value	Corrective statement
> 5% and < = 30% 1	ALTER INDEX REORGANIZE
> 30% 1	ALTER INDEX REBUILD WITH (ONLINE = ON) 2

1 These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE et ALTER INDEX REBUILD. Cependant, 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.

2 La reconstruction d’un index peut être exécutée en ligne ou hors ligne. La réorganisation d’un index est toujours exécutée en ligne. Pour obtenir une disponibilité similaire à l’option réorganiser, vous devez reconstruire les index en ligne. Pour plus d’informations, consultez INDEXER et effectuer des opérations d’index en ligne.

Les index avec une fragmentation inférieure à 5% n’ont pas besoin d’être défragmentés car l’avantage de supprimer une si petite quantité de fragmentation est presque toujours largement compensé par le coût de la CPU encouru pour réorganiser ou reconstruire l’index. De plus, la reconstruction ou la réorganisation de petits index rowstore ne réduit généralement pas la fragmentation réelle.Jusqu’à, y compris, SQL Server 2014 (12.x), le moteur de base de données SQL Server alloue de l’espace à l’aide d’étendues mixtes. Par conséquent, les pages de petits index sont parfois stockées sur des étendues mixtes. Les extensions 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. Voir également Considérations spécifiques à la reconstruction des index rowstore. Pour plus d’informations sur les extensions, consultez les Pages et le Guide d’architecture des extensions.

Détection de la fragmentation des index columnstore

En utilisant sys.dm_db_column_store_row_group_physical_stats, vous pouvez déterminer le pourcentage de lignes supprimées dans un index, ce qui est une mesure raisonnable pour la fragmentation dans un groupe de lignes d’un index columnstore. Utilisez ces informations pour calculer la fragmentation dans un index spécifique, tous les index d’une table, tous les index d’une base de données ou tous les index de toutes les bases de données.

Le jeu de résultats renvoyé par sys.dm_db_column_store_row_group_physical_stats comprend les colonnes suivantes :

Colonne	Description
total_rows	Nombre de lignes physiques stockées dans le groupe de lignes. Pour les groupes de lignes compressées, cela inclut les lignes marquées comme supprimées.
deleted_rows	Nombre de lignes stockées physiquement dans un groupe de lignes compressées qui sont marquées pour suppression. 0 pour les groupes de lignes qui se trouvent dans le magasin delta.

Utilisez ces informations retournées pour calculer la fragmentation de l’index en utilisant cette formule:

100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Une fois que le degré de fragmentation de l’index est connu, utilisez le tableau suivant pour déterminer la meilleure méthode pour supprimer la fragmentation: RÉORGANISATION de l’INDEX ou INDEX.

computed fragmentation in percent value	Applies to version	Corrective statement
> = 20%	SQL Server 2012 (11.x) and SQL Server 2014 (12.x)	ALTER INDEX REBUILD
> = 20%	Starting with SQL Server 2016 (13.x)	MODIFIER L’INDEX RÉORGANISER

Pour vérifier la fragmentation d’un index de magasin de lignes à l’aide de Transact-SQL

L’exemple suivant trouve le pourcentage moyen de fragmentation de tous les index dans la table HumanResources.Employee dans la base de données AdventureWorks2016.

SELECT a.object_id, object_name(a.object_id) AS TableName, a.index_id, name AS IndedxName, avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID (N'AdventureWorks2016_EXT') , OBJECT_ID(N'HumanResources.Employee') , NULL , NULL , NULL) AS aINNER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;GO

L’instruction précédente renvoie un jeu de résultats similaire à ce qui suit.

object_id TableName index_id IndexName avg_fragmentation_in_percent----------- ------------ ----------- ----------------------------------------------------- ------------------------------1557580587 Employee 1 PK_Employee_BusinessEntityID 01557580587 Employee 2 IX_Employee_OrganizationalNode 01557580587 Employee 3 IX_Employee_OrganizationalLevel_OrganizationalNode 01557580587 Employee 5 AK_Employee_LoginID 66.66666666666671557580587 Employee 6 AK_Employee_NationalIDNumber 501557580587 Employee 7 AK_Employee_rowguid 0(6 row(s) affected)

Pour plus d’informations, consultez sys.dm_db_index_physical_stats.

Pour vérifier la fragmentation d’un index columnstore à l’aide de Transact-SQL

L’exemple suivant trouve le pourcentage moyen de fragmentation de tous les index de la table dbo.FactResellerSalesXL_CCI dans la base de données AdventureWorksDW2016.

SELECT i.object_id, object_name(i.object_id) AS TableName, i.index_id, i.name AS IndexName, 100*(ISNULL(SUM(CSRowGroups.deleted_rows),0))/NULLIF(SUM(CSRowGroups.total_rows),0) AS 'Fragmentation'FROM sys.indexes AS i INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups ON i.object_id = CSRowGroups.object_id AND i.index_id = CSRowGroups.index_idWHERE object_name(i.object_id) = 'FactResellerSalesXL_CCI'GROUP BY i.object_id, i.index_id, i.nameORDER BY object_name(i.object_id), i.name;

L’instruction précédente renvoie un jeu de résultats similaire à ce qui suit.

object_id TableName index_id IndexName Fragmentation----------- --------------------------- ----------- ------------------------------- ---------------114099447 FactResellerSalesXL_CCI 1 IndFactResellerSalesXL_CCI 0(1 row(s) affected)

Vérifiez la fragmentation des index à l’aide de SQL Server Management Studio

1. Dans l’Explorateur d’objets, Développez la base de données contenant la table sur laquelle vous souhaitez vérifier la fragmentation d’un index.
2. Développez le dossier Tables.
3. Développez la table sur laquelle vous souhaitez vérifier la fragmentation d’un index.
4. Développez le dossier Index.
5. Cliquez avec le bouton droit sur l’index dont vous souhaitez vérifier la fragmentation et sélectionnez Propriétés.
6. Sous Sélectionner une page, sélectionnez Fragmentation.

Les informations suivantes sont disponibles sur la page de fragmentation:

Valeur	Description
Plénitude de la page	Indique la plénitude moyenne des pages d’index, en pourcentage. 100% signifie que les pages d’index sont complètement pleines. 50% signifie qu’en moyenne, chaque page d’index est à moitié pleine.
Fragmentation totale	Le 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 des feuilles.
Profondeur	Le nombre de niveaux dans l’index, y compris le niveau de la feuille.
Enregistrements transférés	Le nombre d’enregistrements dans un tas qui ont des pointeurs vers un autre emplacement de données. (Cet état se produit lors d’une mise à jour, lorsqu’il n’y a pas assez d’espace pour stocker la nouvelle ligne à l’emplacement d’origine.)
Lignes fantômes	Le nombre de lignes marquées comme supprimées mais pas encore supprimées. Ces lignes seront 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’isolation d’instantané en cours.
Type d’indice	Le type d’indice. Les valeurs possibles sont l’index en cluster, l’index non en cluster et le XML principal. Les tables peuvent également être stockées sous forme de tas (sans index), mais cette page de propriétés d’index ne peut pas être ouverte.
Lignes au niveau des feuilles	Le nombre de lignes au niveau des feuilles.
Taille de ligne maximale	La taille de ligne maximale au niveau des feuilles.
Taille de ligne minimale	La taille de ligne minimale au niveau de la feuille.
Pages	Le 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	Le nombre d’enregistrements fantômes qui sont conservés en raison d’une transaction d’isolation d’instantané en cours.

Défragmenter les index en reconstruisant ou en réorganisant l’index

Vous défragmentez un index fragmenté en utilisant l’une des méthodes suivantes:

• Réorganisation de l’index
• Reconstruction de l’index

Réorganiser un index

La réorganisation d’un index utilise un minimum de ressources système et est une opération en ligne. Cela signifie que les verrous de table de blocage à long terme ne sont pas maintenus et que les requêtes ou les mises à jour de la table sous-jacente peuvent se poursuivre pendant la transaction ALTER INDEX REORGANIZE.

• Pour les index rowstore, le moteur de base de données défragmente le niveau feuille des index en cluster et non en cluster sur les tables et les vues en réorganisant physiquement les pages au niveau feuille pour qu’elles correspondent à l’ordre logique des nœuds feuilles (de gauche à droite). La réorganisation compacte également les pages d’index en fonction de la valeur du facteur de remplissage de l’index. Pour afficher le paramètre de facteur de remplissage, utilisez sys.index. Pour des exemples de syntaxe, voir Exemples : Réorganisation de Rowstore.

• Lors de l’utilisation d’index columnstore, le magasin delta peut se retrouver avec plusieurs petits groupes de lignes après l’insertion, la mise à jour et la suppression de données au fil du temps. La réorganisation d’un index de magasin de colonnes force tous les groupes de lignes dans le magasin de colonnes, puis combine les groupes de lignes en moins de groupes de lignes avec plus de lignes. L’opération réorganiser supprime également les lignes qui ont été supprimées du magasin de colonnes. La réorganisation nécessite initialement des ressources CPU supplémentaires pour compresser les données, ce qui peut ralentir les performances globales du système. Cependant, dès que les données sont compressées, les performances des requêtes s’améliorent. Pour des exemples de syntaxe, voir Exemples : réorganiser le magasin de colonnes.

Reconstruire un index

Reconstruire un index diminue et recrée l’index. Selon le type d’index et la version du moteur de base de données, une opération de reconstruction peut être effectuée en ligne ou hors ligne. Pour la syntaxe T-SQL, voir ALTER INDEX REBUILD

• Pour les index rowstore, la reconstruction supprime la fragmentation, récupère de l’espace disque en compactant les pages en fonction du paramètre de facteur de remplissage spécifié ou existant et réordonne 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 en une seule transaction. Les contraintes de clé étrangère ne doivent pas être supprimées à l’avance. Lorsque des index de 128 extensions ou plus sont reconstruits, le moteur de base de données reporte les désallocations de page réelles et leurs verrous associés jusqu’à la validation de la transaction. Pour des exemples de syntaxe, voir Exemples : Réorganisation de Rowstore.

• Pour les index columnstore, la reconstruction supprime la fragmentation, déplace toutes les lignes dans le columnstore et récupère de l’espace disque en supprimant physiquement les lignes qui ont été logiquement supprimées de la table.

  Astuce

  À partir de SQL Server 2016 (13.x), la reconstruction de l’index columnstore n’est généralement pas nécessaire car REORGANIZE effectue l’essentiel d’une reconstruction en arrière-plan en tant qu’opération en ligne.

  Pour des exemples de syntaxe, voir Exemples : Reconstruction de ColumnStore.

Autorisations

Nécessite une autorisation ALTER sur la table ou la vue. L’utilisateur doit être membre d’au moins l’un des rôles suivants :

• rôle de base de données db_ddladmin 1
• rôle de base de données db_owner
• rôle de serveur sysadmin

1 le rôle de base de données db_ddladmin est le moins privilégié.

Supprimer la fragmentation à l’aide de SQL Server Management Studio

Pour réorganiser ou reconstruire un index

1. Dans l’Explorateur d’objets, Développez la base de données contenant la table sur laquelle vous souhaitez réorganiser un index.
2. Développez le dossier Tables.
3. Développez la table sur laquelle vous souhaitez réorganiser un index.
4. Développez le dossier Index.
5. Cliquez avec le bouton droit sur l’index que vous souhaitez réorganiser et sélectionnez Réorganiser.
6. Dans la boîte de dialogue Réorganiser les index, vérifiez que l’index correct se trouve dans la grille Index à réorganiser et cliquez sur OK.
7. Cochez la case Données de colonne d’objets volumineux compacts pour spécifier que toutes les pages contenant des données d’objets volumineux (LOB) sont également compactées.
8. Cliquez sur OK.

Pour réorganiser tous les index d’une table

1. Dans l’Explorateur d’objets, Développez la base de données contenant la table sur laquelle vous souhaitez réorganiser les index.
2. Développez le dossier Tables.
3. Développez la table sur laquelle vous souhaitez réorganiser les index.
4. Cliquez avec le bouton droit sur le dossier Index et sélectionnez Tout réorganiser.
5. 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 des index à réorganiser, sélectionnez l’index, puis appuyez sur la touche Supprimer.
6. Cochez la case Données de colonne d’objets volumineux compacts pour spécifier que toutes les pages contenant des données d’objets volumineux (LOB) sont également compactées.
7. Cliquez sur OK.

Pour reconstruire un index

1. Dans l’Explorateur d’objets, Développez la base de données contenant la table sur laquelle vous souhaitez réorganiser un index.
2. Développez le dossier Tables.
3. Développez la table sur laquelle vous souhaitez réorganiser un index.
4. Développez le dossier Index.
5. Cliquez avec le bouton droit sur l’index que vous souhaitez réorganiser et sélectionnez Reconstruire.
6. Dans la boîte de dialogue Reconstruire les index, vérifiez que l’index correct se trouve dans la grille Index à reconstruire et cliquez sur OK.
7. Cochez la case Données de colonne d’objets volumineux compacts pour spécifier que toutes les pages contenant des données d’objets volumineux (LOB) sont également compactées.
8. Cliquez sur OK.

Supprimer la fragmentation à l’aide de Transact-SQL

Pour réorganiser un index fragmenté

L’exemple suivant réorganise l’index IX_Employee_OrganizationalLevel_OrganizationalNode sur la table HumanResources.Employee dans la base de données AdventureWorks2016.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE;

L’exemple suivant réorganise l’index de magasin de colonnes IndFactResellerSalesXL_CCI sur la table dbo.FactResellerSalesXL_CCI dans la base de données AdventureWorksDW2016.

-- This command will force all CLOSED and OPEN rowgroups into the columnstore.ALTER INDEX IndFactResellerSalesXL_CCI ON FactResellerSalesXL_CCI REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Pour réorganiser tous les index d’une table

L’exemple suivant réorganise tous les index de la table HumanResources.Employee dans la base de données AdventureWorks2016.

ALTER INDEX ALL ON HumanResources.Employee REORGANIZE;

Pour reconstruire un index fragmenté

L’exemple suivant reconstruit un index unique sur la table Employee dans la base de données AdventureWorks2016.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.EmployeeREBUILD;

Pour reconstruire tous les index d’une table

L’exemple suivant reconstruit tous les index associés à la table dans la base de données AdventureWorks2016 en utilisant le mot clé ALL. Trois options sont spécifiées.

ALTER INDEX ALL ON Production.ProductREBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

Pour plus d’informations, consultez ALTER INDEX (Transact-SQL).

Gestion automatique des index et des statistiques

Tirez parti de solutions telles que Adaptive Index Defrag pour gérer automatiquement la défragmentation des index et les mises à jour des statistiques pour une ou plusieurs bases de données. Cette procédure choisit automatiquement de reconstruire ou de réorganiser un index en fonction de son niveau de fragmentation, entre autres paramètres, et de mettre à jour les statistiques avec un seuil linéaire.

Considérations spécifiques à la reconstruction des index rowstore

La reconstruction d’un index en cluster reconstruit automatiquement tout index non en cluster qui fait référence à la clé de clustering, si les identifiants physiques ou logiques contenus dans les enregistrements d’index non en cluster doivent changer.

Les scénarios suivants forcent la reconstruction automatique de tous les index non clusterisés de rowstore sur une table:

• Création d’un index en cluster sur une table
• Suppression d’un index en cluster, ce qui entraîne le stockage de la table sous forme de tas
• Modification de la clé de clustering pour inclure ou exclure des colonnes

Les scénarios suivants n’exigent pas que tous les index non en cluster rowstore soient automatiquement reconstruits sur une table:

• Reconstruction d’un index en cluster unique
• Reconstruction d’un index en cluster non unique
• Modification du schéma d’index, par exemple en appliquant un schéma de partitionnement à un index en cluster ou en déplaçant l’index en cluster vers un autre groupe de fichiers

Lorsque ALL est spécifié avec l’instruction ALTER INDEX, les index relationnels, clusterisés et non clusterisés, et les index XML de la table sont réorganisés.

Considérations spécifiques à la reconstruction d’un index columnstore

Lors de la reconstruction d’un index columnstore, le moteur de base de données lit toutes les données de l’index columnstore d’origine, y compris le magasin delta. Il combine les données dans de nouveaux groupes de lignes et compresse les groupes de lignes dans le magasin de colonnes. Le moteur de base de données défragmente le magasin de colonnes en supprimant physiquement les lignes qui ont été logiquement supprimées de la table. Les octets supprimés sont récupérés sur le disque.

Reconstruire une partition au lieu de la table entière

• La reconstruction de la table entière prend beaucoup de temps si l’index est volumineux et nécessite suffisamment d’espace disque pour stocker une copie supplémentaire de l’index pendant la reconstruction. Habituellement, il suffit de reconstruire la partition la plus récemment utilisée.
• Pour les tables partitionnées, vous n’avez pas besoin de reconstruire tout l’index columnstore car la fragmentation est susceptible de se produire uniquement dans les partitions qui ont été modifiées récemment. Les tables de faits et les tables de grande dimension sont généralement partitionnées afin d’effectuer des opérations de sauvegarde et de gestion sur des morceaux de la table.

Reconstruire une partition après de lourdes opérations DML

La reconstruction d’une partition défragmente la partition et réduit le stockage sur disque. La reconstruction supprime toutes les lignes du magasin de colonnes qui sont marquées pour suppression et déplace tous les groupes de lignes du magasin delta dans le magasin de colonnes. Il peut y avoir plusieurs groupes de lignes dans le magasin delta qui ont moins d’un million de lignes.

Reconstruire une partition après le chargement des données

La reconstruction d’une partition après la date de chargement garantit que toutes les données sont stockées dans le magasin de colonnes. Lorsque des processus simultanés traitent chaque chargement de moins de 100 000 lignes dans la même partition en même temps, la partition peut se retrouver avec plusieurs magasins delta. La reconstruction déplace toutes les lignes du magasin delta dans le magasin de colonnes.

Considérations spécifiques à la réorganisation d’un index de magasin de colonnes

Lors de la réorganisation d’un index de magasin de colonnes, le moteur de base de données compresse chaque groupe de lignes delta FERMÉ dans le magasin de colonnes en tant que groupe de lignes compressé. À partir de SQL Server 2016 (13.x) et dans la base de données SQL Azure, la commande REORGANIZE effectue les optimisations de défragmentation supplémentaires suivantes en ligne :

• Supprime physiquement des lignes d’un groupe de lignes lorsque 10 % ou plus des lignes ont été logiquement supprimées. Les octets supprimés sont récupérés sur le support physique. Par exemple, si un groupe de lignes compressé de 1 million de lignes a 100K lignes supprimées, SQL Server supprimera les lignes supprimées et recompressera le groupe de lignes avec 900k lignes. Il économise sur le stockage en supprimant les lignes supprimées.

• Combine un ou plusieurs groupes de lignes compressés pour augmenter les lignes par groupe de lignes jusqu’à un maximum de 1 048 576 lignes. Par exemple, si vous importez en masse 5 lots de 102 400 lignes, vous obtiendrez 5 groupes de lignes compressés. Si vous exécutez REORGANIZE, ces groupes de lignes seront fusionnés en 1 groupe de lignes compressé de taille 512 000 lignes. Cela suppose qu’il n’y avait aucune limite de taille de dictionnaire ou de mémoire.

• Pour les groupes de lignes dans lesquels 10 % ou plus des lignes ont été logiquement supprimées, le moteur de base de données essaie de combiner ce groupe de lignes avec un ou plusieurs groupes de lignes. Par exemple, le groupe de lignes 1 est compressé avec 500 000 lignes et le groupe de lignes 21 est compressé avec un maximum de 1 048 576 lignes. Le groupe de lignes 21 a 60% des lignes supprimées, ce qui laisse 409 830 lignes. Le moteur de base de données favorise la combinaison de ces deux groupes de lignes pour compresser un nouveau groupe de lignes contenant 909 830 lignes.

Après avoir effectué des chargements de données, vous pouvez avoir plusieurs petits groupes de lignes dans le magasin delta. Vous pouvez utiliser ALTER INDEX REORGANIZE pour forcer tous les groupes de lignes dans le magasin de colonnes, puis pour combiner les groupes de lignes en moins de groupes de lignes avec plus de lignes. L’opération réorganiser supprimera également les lignes qui ont été supprimées du magasin de colonnes.

Limitations et restrictions

Les index Rowstore avec plus de 128 extensions 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 beaucoup de verrous. Pour plus d’informations sur les extensions, consultez les pages et le Guide d’architecture des extensions.

L’instruction ALTER INDEX REORGANIZE nécessite que le fichier de données contenant l’index ait de l’espace disponible, car l’opération ne peut allouer que des pages de travail temporaires sur le même fichier, pas dans un autre fichier du groupe de fichiers. Ainsi, bien que le groupe de fichiers puisse avoir des pages libres 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.

Un index ne peut pas être réorganisé ou reconstruit si le groupe de fichiers dans lequel il se trouve est hors ligne ou défini en lecture seule. Lorsque le mot clé ALL est spécifié et qu’un ou plusieurs index se trouvent dans un groupe de fichiers hors ligne ou en lecture seule, l’instruction échoue.

Statistiques :

• Lorsqu’un index est créé ou reconstruit, les statistiques sont créées ou mises à jour en analysant toutes les lignes du tableau. Cependant, à partir de SQL Server 2012 (11.x), les statistiques ne sont pas créées ou mises à jour en analysant toutes les lignes de la table lorsqu’un index partitionné est créé ou reconstruit. Au lieu de cela, l’optimiseur de requêtes utilise l’algorithme d’échantillonnage par défaut pour générer ces statistiques. Pour obtenir des statistiques sur les index partitionnés en analysant toutes les lignes de la table, utilisez la clause CRÉER des STATISTIQUES ou METTRE À JOUR des STATISTIQUES avec la clause FULLSCAN.

• Lorsqu’un index est réorganisé, les statistiques ne sont pas mises à jour.

Un index ne peut pas être réorganisé lorsque ALLOW_PAGE_LOCKS est désactivé.

Jusqu’à SQL Server 2017 (14.x), la reconstruction d’un index de magasin de colonnes en cluster est une opération hors ligne. Le moteur de base de données doit acquérir un verrou exclusif sur la table ou la partition pendant la reconstruction. Les données sont hors ligne et indisponibles pendant la reconstruction, même lorsque vous utilisez NOLOCK, l’isolation des Snapshots validés en lecture (RCSI) ou l’isolation des Snapshots.À partir de SQL Server 2019 (15.x), un index de magasin de colonnes en cluster peut être reconstruit en utilisant l’option ONLINE = ON.

Pour une table Azure Synapse Analytics (anciennement Azure Synapse Analytics) avec un index de magasin de colonnes en cluster ordonné, ALTER INDEX REBUILD va trier à nouveau les données à l’aide de TempDB. Surveillez TempDB pendant les opérations de reconstruction. Si vous avez besoin de plus d’espace TempDB, agrandissez l’entrepôt de données. Redimensionner une fois la reconstruction de l’index terminée.

Pour une table Azure Synapse Analytics (anciennement Azure Synapse Analytics) avec un index de magasin de colonnes en cluster ordonné, ALTER INDEX REORGANIZE ne trie pas à nouveau les données. Pour utiliser les données, utilisez ALTER INDEX REBUILD.

Utilisation de la RECONSTRUCTION d’INDEX pour récupérer des pannes matérielles

Dans les versions antérieures de SQL Server, vous pouvez parfois reconstruire un index non clusterisé rowstore pour corriger les incohérences causées par des pannes matérielles.À partir de SQL Server 2008, vous pouvez toujours être en mesure de réparer ces incohérences entre l’index et l’index en cluster en reconstruisant un index non en cluster hors ligne. Cependant, vous ne pouvez pas réparer les incohérences d’index non regroupées en reconstruisant l’index en ligne, car le mécanisme de reconstruction en ligne utilise l’index non regroupé existant comme base pour la reconstruction et conserve ainsi l’incohérence. La reconstruction de l’index hors ligne peut parfois forcer une analyse de l’index en cluster (ou du tas) et ainsi supprimer l’incohérence. Pour assurer une reconstruction à partir de l’index en cluster, supprimez et recréez l’index non en cluster. Comme pour les versions antérieures, nous vous recommandons de récupérer les incohérences en restaurant les données affectées à partir d’une sauvegarde ; cependant, vous pourrez peut-être réparer les incohérences d’index en reconstruisant l’index non regroupé hors ligne. Pour plus d’informations, consultez DBCC CHECKDB (Transact-SQL).

Voir aussi

• Guide d’architecture et de conception de l’Index SQL Server
• Effectuer des opérations d’Index En ligne
• MODIFIER l’INDEX (Transact-SQL)
• Défragmentation adaptative de l’Index
• CRÉER DES STATISTIQUES (Transact-SQL)
• METTRE À JOUR LES STATISTIQUES (Transact-SQL)
• Index Columnstore Performances de requête
• Commencez avec Columnstore pour des analyses opérationnelles en temps réel
• Index de magasin de colonnes pour l’entreposage de données
• Index de magasin de colonnes et stratégie de fusion pour les groupes de lignes