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.
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 |
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% 1 |
ALTER INDEX REORGANIZE |
> 30% 1 |
ALTER INDEX REBUILD WITH (ONLINE = ON) 2 |
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:
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.
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.
9 min read
-1112.jpg)
