Comment réorganiser et reconstruire les index dans MS SQL ?

Les index sont les objets des fichiers de base de données MS SQL. Ces objets de schéma agissent comme une table des matières de la base de données. Ils sont utilisés pour améliorer les opérations de récupération de données sur une table de base de données MS SQL.

Cependant, avec le temps, la table peut devenir fragmentée en raison d’opérations continues d’INSERTION et de MISE À JOUR. De plus, comme d’autres objets, les index sont également sujets à la corruption. Si les index sont corrompus, vous pouvez rencontrer des erreurs telles que :

Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_Advertise_BrandCopy_Price_Stock' (ID 69). Possible missing or invalid keys for the index row matching:

​

Msg 8955, Level 16, State 1, Line 1

​

Data row (1:11226494:9) identified by (Id = 11078215) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 137.50 and NSFPQtyInStock = 0 and Id = 11078215'.

​

Msg 8951, Level 16, State 1, Line 1

Pour résoudre les erreurs associées à la corruption des index ou lorsque le niveau de fragmentation des index augmente, vous pouvez reconstruire ou réorganiser les index. Dans cet article, nous discuterons de comment réorganiser et reconstruire les index dans MS SQL Server. Nous mentionnerons également un outil avancé de réparation de base de données MS SQL qui peut restaurer les index et d’autres objets à partir de fichiers de base de données SQL corrompus rapidement et avec une précision complète.

Vérifier le pourcentage de fragmentation des index

Avant de réorganiser les index, vous devez connaître le pourcentage de fragmentation. Vous pouvez utiliser la commande ci-dessous pour vérifier le pourcentage de fragmentation de l’index :

SELECT 

    OBJECT_NAME(object_id) tableName12,

    index_id,

    index_type_desc,

    avg_fragmentation_in_percent,

    page_count

FROM 

    sys.dm_db_index_physical_stats (

        DB_ID('Adventureworks2019'), 

        OBJECT_ID('[Person].[Person]'), 

        NULL, NULL, 'DETAILED'

    );

​

Réorganiser les index dans SQL Server

Si le niveau de fragmentation de l’index est compris entre 10 et 30, il est recommandé de réorganiser l’index. Vous pouvez utiliser le code suivant en T-SQL pour réorganiser l’index :

ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]

REORGANIZE;

​

Alternativement, vous pouvez utiliser le SQL Server Management Studio (SSMS) pour réorganiser les index. Suivez les étapes ci-dessous :

• Dans SSMS, connectez-vous à votre instance SQL Server.
• Dans l’Explorateur d’objets, développez la base de données.
• Développez le dossier Tables puis le dossier Indexes.
• Faites un clic droit sur l’index que vous devez réorganiser, puis cliquez sur Réorganiser.

Reconstruction des index dans MS SQL

Si la fragmentation est élevée, vous pouvez reconstruire les index. Vous pouvez utiliser la commande DBCC DBREINDEX pour reconstruire un index. Voici comment exécuter cette commande :

DBCC DBREINDEX

(

    table_name

    [ , index_name [ , fillfactor ] ]

)

    [ WITH NO_INFOMSGS ]

​

Cette commande ne prend pas en charge les index spatiaux et les index de colonnes en mémoire optimisée.

Alternativement, vous pouvez utiliser la commande ALTER INDEX :

ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]

REBUILD;

​

Vous pouvez également reconstruire les index en utilisant l’interface utilisateur graphique dans SSMS. Voici les étapes :

• Dans SSMS, dans l’Explorateur d’objets, développez la base de données contenant la table sur laquelle vous devez reconstruire un index.
• Développez le dossier Tables puis la table sur laquelle vous devez reconstruire un index.
• Développez le dossier Indexes, cliquez avec le bouton droit sur l’index que vous devez reconstruire, et sélectionnez Reconstruire.

Réparer la base de données SQL Server

Si la réorganisation ou la reconstruction des index ne fonctionne pas, vous pouvez essayer la commande DBCC CHECKDB avec l’option REPAIR_ALLOW_DATA_LOSS pour réparer la base de données. Voici comment utiliser cette commande :

Tout d’abord, changez le mode de la base de données en SINGLE_USER en utilisant la commande ci-dessous :

ALTER DATABASE BusinessEntity SET SINGLE_USER

Ensuite, exécutez la commande DBCC CHECKDB comme indiqué ci-dessous pour réparer la base de données :

DBCC CHECKDB (N ’BusinessEntity’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; 

GO

​

Après la réparation, rétablissez le mode de la base de données en MULTI_USER en exécutant la commande ci-dessous :

ALTER DATABASE BusinessEntity SET MULTI_USER

La commande DBCC CHECKDB ci-dessus peut vous aider à résoudre toutes les erreurs liées à la corruption des index. Elle peut réparer à la fois les index regroupés et non regroupés dans les bases de données SQL. Cependant, elle ne garantit pas une récupération complète des données et peut entraîner une perte de données. Pour éviter la perte de données, vous pouvez utiliser des outils professionnels de réparation MS SQL. Ils sont conçus avec des algorithmes avancés pour récupérer tous les objets, y compris les index regroupés et non regroupés, les procédures stockées, les déclencheurs, etc., à partir de la base de données corrompue. 

Conclusion

Vous pouvez réduire la fragmentation des index en réorganisant ou reconstruisant l’index. Dans cet article, nous avons expliqué comment vérifier le pourcentage de fragmentation dans les index et comment reconstruire les index.