How to Rebuild Indexes in SQL Database?

Written By Charanjeet Kaur
Approved By Raja Jegan R
Updated on 24 Jan 2024
Min Reading 4 Min

This article describes the importance of rebuilding indexes in a SQL Server database. It also explains how to rebuild indexes in the SQL database using SQL Server Management Studio (SSMS) and T-SQL.

When and Why You Need to Rebuild Indexes in SQL Database?

Indexes in a SQL database gets modified by the database engine automatically whenever an INSERT, DELETE, or UPDATE operation is performed on a row in a table. However, these modifications can lead to index fragmentation. And, as the indexes become heavily fragmented, the query performance is degraded. And so, indexes in a SQL Server database need to be occasionally rebuilt to enhance database query performance.

IMPORTANT! Index fragmentation alone is not the only reason to rebuild the index. You may also need to rebuild it to fix a corrupted index of SQL Server, as a corrupted index can also cause a delay in query execution and slow response times. You can also fix clustered index corruption in SQL database by restoring the database from the backup. If the updated backup isn’t available, use Stellar Repair for MS SQL software to repair the corrupted index.

Before discussing the SQL index rebuild methods, you must first identify fragmentation in indexes. It would also help if you analyzed the extent of index fragmentation to choose the best method to fix it.

How to Detect Index Fragmentation & Determine the Degree of Fragmentation?

You can identify fragmentation in a specific index or all indexes defined on a table or in a database using the Dynamic Management Function (DMF) “sys.dm_db_index_physical_stats.”

Column	DESCRIPTION
avg_fragmentation_in_percent	Determines the percentage of logical fragmentation (out-of-order pages in the index)
fragment_count	Detect the number of fragments (physically consecutive leaf pages) in the index
avg_fragment_size_in_pages	Detects the average number of pages in one fragment in the index

Note: Refer to this link for more information about sys.dm_db_index_physical_stats.

Once you have detected index fragmentation, you must also analyze the fragmentation level to choose the best method to remove the fragmentation: INDEX REORGANIZATION or INDEX REBUILD.

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

You can reorganize an index if the level of fragmentation is low. However, you must rebuild indexes with a degree of fragmentation exceeding 30 percent. If you have a heavily fragmented, proceed with understanding the methods to rebuild an index.

How to Rebuild Index in SQL Server?

Method 1 – Use SSMS

You can rebuild a clustered index online or offline. Here, we will discuss the steps to rebuild an index online using the SQL Server Management Studio. The detailed steps are as follows:

In SSMS, from the Object Explorer box, click the plus sign to expand the SQL database containing the table on which you need to rebuild an index online.
Expand the database table on which you want to rebuild the index online.
Expand the Indexes folder, right-click the index you wish to rebuild online, and select Properties.
Select Options under Select a page.
Select Allow online DML processing, and choose True from the list, and then click OK.
Right-click the index to be rebuilt online, and then select Rebuild.
When the ‘Rebuild Indexes’ dialog box opens, make sure that the correct index is listed in the Indexes to rebuild the grid. Click OK.

Method 2 – Use T-SQL

You can also rebuild indexes using Transact SQL. The syntax to rebuild a single index is as follows:

ALTER INDEX index1 ON table1 REBUILD;

Use the following syntax to rebuild all indexes in a table:

ALTER INDEX ALL ON table1 REBUILD;

To rebuild all indexes on a table in a specified database, use the following syntax:

ALTER INDEX ALL ON dbo.table1 REBUILD;

Let’s demonstrate an example to rebuild a single index on a specified table. For this, perform these steps:

In SSMS, click Object Explorer, and then connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the code below into the query window, and then click Execute. In the example below, index_name ‘AK’ associated with a specific ‘Employee_BusinessEntity’ table will be rebuilt.

USE AdventureWorks2012;
GO
ALTER INDEX AK_Employee_BusinessEntity ON HumanResources.Employee
REBUILD;
GO

The following example demonstrates how you can rebuild all indexes on a table:

From Object Explorer, click New Query.
Copy and paste the following command into the query. In this example, the keyword ‘ALL’ is used to rebuild all indexes in the database table.

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON HumanResources.Employee
REBUILD;
GO

Conclusion

This article discussed when and why you might need to rebuild indexes in a SQL Server database; this may depend on how heavily an index is fragmented. The article also discussed the steps to rebuild indexes using SQL Server Management Studio (SSMS) and by executing queries using Transact-SQL (T-SQL). If you're not comfortable with writing queries, you can opt for SSMS for the job. In case the index you have rebuilt is corrupt, you can fix it by restoring the database from a backup or using Stellar Repair for MS SQL software. The software can help fix index corruption in the SQL Server database and restore the database to its original form without interruption.

Was this article helpful?