How to Reorganize and Rebuild Indexes in MS SQL?

Indexes are the objects of MS SQL database files. These schema objects act like a table of contents of the database. These are used to improve the data retrieval operations on an MS SQL database table. 

However, with time, the table can get fragmented due to continuous INSERT and UPDATE operations. Also, like other objects, indexes are also prone to corruption. If the indexes get corrupted, you can receive errors like:

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

To resolve the errors associated with corruption in indexes or when the fragmentation level of the indexes increases, you can rebuild or reorganize the indexes. In this article, we'll discuss how to reorganize and rebuild the indexes in MS SQL Server. We'll also mention an advanced MS SQL database repair tool that can restore indexes and other objects from corrupted SQL database files quickly with complete precision.

Check the Fragmentation Percentage of Indexes

Before reorganizing the indexes, you need to know the percentage of fragmentation. You can use the below command to check the percentage of the index fragmentation:

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'
    );

Reorganizing Indexes in SQL Server

If the fragmentation level of the index is between 10 and 30, it is recommended to reorganize the index. You can use the following code in T-SQL to reorganize the index:

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

Alternatively, you can use the SQL Server Management Studio (SSMS) to reorganize the indexes. Follow the below steps:

• In SSMS, connect to your SQL Server instance.
• In Object Explorer, expand the database.
• Expand the Tables folder and then the Indexes folder.
• Right-click on the index you need to reorganize, and then click Reorganize.

Rebuilding Indexes in MS SQL

If the fragmentation is higher, then you can rebuild the indexes. You can use the DBCC DBREINDEX command to rebuild an index. Here’s how to execute this command:

DBCC DBREINDEX
(
    table_name
    [ , index_name [ , fillfactor ] ]
)
    [ WITH NO_INFOMSGS ]

This command does not support spatial indexes and memory-optimized column store indexes. 

Alternatively, you can use the ALTER INDEX command:

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

You can also rebuild the indexes by using the graphical user interface in SSMS. Here are the steps:

• In SSMS, in Object Explorer, expand the database containing the table on which you need to rebuild an index.
• Expand the Tables folder and then the table on which you need to rebuild an index.
• Expand the Indexes folder, right-click the index you need to rebuild, and select Rebuild.

Repair the SQL Server Database

If reorganizing or rebuilding the indexes does not work, then you can try the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option to repair the database. Here’s how to use this command:

First, change the mode of the database to SINGLE_USER by using the below command:

ALTER DATABASE BusinessEntity SET SINGLE_USER

Next, run the DBCC CHECKDB command as given below to repair the database:

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

After repair, set the database mode to MULTI_USER by executing the below command:

ALTER DATABASE BusinessEntity SET MULTI_USER

The above DBCC CHECKDB command can help you resolve all the errors that are related to corruption in indexes. It can repair both clustered and non-clustered indexes in SQL databases. However, it does not guarantee complete data recovery and can result in data loss. To prevent data loss, you can use any professional MS SQL repair tools. They are designed with advanced algorithms to recover all the objects, including clustered and non-clustered indexes, stored procedures, triggers, etc., from the corrupt database. 

Conclusion

You can reduce index fragmentation by reorganizing or rebuilding the index. In this article, we have explained how to check the fragmentation percentage in indexes and how to rebuild the indexes.