Rebuilding All Indexes in a SQL Server Database

1. Overview

As we perform regular updates, inserts, and deletions, indexes become fragmented. Fragmented indexes mean the index’s logical order doesn’t align with the disk’s physical storage. This leads to slower queries, high resource usage, and increased server I/O load.

In this tutorial, we’ll explore rebuilding indexes in one table or the entire database using Microsoft SQL Server 2022, automating index rebuilds, measuring impact, and handling common errors during the rebuild process.

2. Understanding Fragmentation

Indexes in a database become either externally or internally fragmented over time:

• External fragmentation means the logical order of index pages does not match their physical order on the disk due to frequent insertions, deletions, or updates, which leads to page splits.
• Alternatively, with internal fragmentation, unused space within an index page results in low page density. It’s caused by improper fill factor settings, deletions, and updates that leave gaps within a page.

Index fragmentation can undermine performance by forcing the database engine to work even harder to retrieve data. In addition, fragmented indexes store data pages inefficiently, causing excessive disk I/O, slower query response times, and higher memory and CPU usage.

If left unchecked, fragmentation can cause significant slowdowns, especially as tables grow larger and queries become more complex. SQL Server tracks this behavior and provides a percentage showing the fragmentation levels in a database.

2.1. Reorganization and Rebuilding

We can reorganize or rebuild our indexes based on the fragmentation level.

While reorganization is a lightweight process that rearranges the leaf-level pages (the bottom layer of the index) into the correct logical order, rebuilding is a heavier process that removes all fragmentation by dropping and recreating the index from scratch.

Furthermore, whereas reorganization is recommended for low to moderate fragmentation levels of 5% to 30%, rebuilding is applied to high fragmentation levels > 30%. However, these thresholds aren’t hard rules but a baseline. Our choice of the thresholds can be subject to other factors like the recovery model, buffer pool memory, etc.

3. Prerequisites

Before rebuilding indexes, we must:

1. Check fragmentation
2. Ensure we have permissions to rebuild the overly fragmented index
3. Back up the database to secure data

3.1. Checking Fragmentation

Let’s consider the Baeldung University database schema:

To quantify the index fragmentation levels in the University database, we need to calculate the fragmentation percentage:

USE University;
GO

SELECT 
    t.name AS Table_name,
    i.name AS Index_name,
    ps.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
INNER JOIN 
    sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
INNER JOIN 
    sys.tables t ON i.object_id = t.object_id
WHERE 
    i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
    AND ps.database_id = DB_ID()
ORDER BY 
    ps.avg_fragmentation_in_percent DESC;

We retrieved the fragmentation percentage of all indexes in the University database to help identify which ones need maintenance:

The truncated results show a list of primary (PK) and unique (UQ) indexes for each table in our database. The PK index of the Teaching table is fragmented at approximately 85%, so we’ll rebuild it.

3.2. Permissions

Rebuilding indexes in SQL Server requires specific permissions to ensure users can execute the operation successfully. Therefore, a user or role must have the ALTER permission on the target table or view before rebuilding can occur. Permissions are possible through various means:

• Implicitly via Role-Based Permissions: memberships or assignments to the db_owner, db_ddladmin, or sysadmin for a database come ready with the necessary permissions to alter the database.

• Online Index Rebuilds: applies to SQL Server’s enterprise edition and requires the ALTER + VIEW DATABASE STATE permission if using ONLINE = ON.

• Explicit Permissions: for sensitive tasks like rebuilding, we can grant explicit permissions to specific roles to our database:

IF NOT EXISTS (
  SELECT 1 
  FROM sys.database_principals 
  WHERE name = 'index_maintenance' AND type = 'R'
)
BEGIN
    CREATE ROLE index_maintenance;
END
GO

GRANT ALTER ON OBJECT::dbo.Teaching TO index_maintenance;
GO

Here, we create a role called index_maintenance after verifying that no other roles with the same name exist in the database. Next, we grant this role-specific permissions on the Teaching table alone:

Our code was executed successfully.

3.3. Backing up the Database

Before rebuilding indexes, we must always back up our database.

While index rebuilds are generally safe, unexpected issues like power outages, hardware failures, or accidental data modifications can lead to data corruption or loss. A backup in SQL Server ensures we can restore the database to its pre-build state if needed:

BACKUP DATABASE University
TO DISK = 'C:\Backups\University_FULL.bak'
WITH FORMAT,
     INIT,
     NAME = 'Full Backup Before Index Rebuild',
     SKIP,
     STATS = 10;

We perform a full database backup while adding a descriptive label to identify this backup in case anything goes wrong.

With STATS = 10, we display progress updates every 10% during the backup operation, which is helpful for large databases, so we’re not left wondering if it’s stuck:

Our database has been backed up, and we can rebuild our indexes.

4. Rebuilding an Index

We rebuild the PK index in the Teaching table:

ALTER INDEX PK__Teaching__3213E83FFA8FEA28 
ON dbo.Teaching 
REBUILD WITH (
	FILLFACTOR = 90,
	SORT_IN_TEMPDB = ON,
	STATISTICS_NORECOMPUTE = OFF);

Here:

• FILLFACTOR = 90 leaves 10% of each page empty to reduce future fragmentation
• SORT_IN_TEMPDB = ON uses tempdb for sorting operations
• STATISTICS_NORECOMPUTE = OFF ensures that index statistics are updated, enabling query optimization

Additionally, we can rebuild all indexes related to a tableby using the ALL keyword instead of the index name. We can also set ONLINE to ON for SQL Server Enterprise Editions to avoid locking the table during the rebuild. 

Our rebuild process should complete without any hitches:

Next, we can measure how well our index rebuild performed.

4.1. Measuring Impact (Before and After)

Once indexes have been rebuilt, we can determine whether performance improvements were achieved and if further optimization is needed.

To evaluate the change, we rerun our fragmentation check code and find that the fragmentation level of the Teaching table’s primary key index has dropped from 85% to 25%. Does this mean our rebuild was useless?

The residual fragmentation is due to our fill factor settings. With 10% of each page intentionally left free to minimize future page splits, this reserved space is counted as part of fragmentation.

Alternatively, for databases whose indices are rebuilt while online, mild fragmentation can be introduced due to background data activity – inserts or updates between the rebuild and the checkpoint.

A 10-30% fragmentation after rebuilding is often acceptable, especially if tables have frequent inserts or updates. Zero fragmentation isn’t always necessary.

5. Rebuilding All Indexes in a Database

When our database contains multiple heavily used tables, rebuilding indexes one by one can be tedious and inefficient. Therefore, we can loop through every table dynamically and rebuild all indexes across the entire database:

USE University;
GO

DECLARE @TableName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);

DECLARE table_cursor CURSOR FOR
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
FROM sys.tables t
WHERE t.is_ms_shipped = 0;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON);';
    PRINT 'Rebuilding indexes on: ' + @TableName;
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM table_cursor INTO @TableName;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

We use the PRINT statement to give live feedback on progress:
The rebuild process completed successfully for all the tables in the database. However, we must remember that a full database index rebuild can be CPU and disk-intensive.

6. Automating Index Rebuilds

Automating the index rebuild process in live production environments is far more efficient than manually checking and rebuilding indexes individually. To this effect, we create a custom script to trigger a rebuild only when fragmentation exceeds 30%:

DECLARE @TableName NVARCHAR(255), @IndexName NVARCHAR(255), @SQL NVARCHAR(MAX);

DECLARE frag_cursor CURSOR FOR
SELECT 
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name),
    QUOTENAME(i.name)
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE 
    ps.avg_fragmentation_in_percent > 30
    AND i.type_desc IN ('CLUSTERED', 'NONCLUSTERED');

OPEN frag_cursor;
FETCH NEXT FROM frag_cursor INTO @TableName, @IndexName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD;';
    PRINT 'Rebuilding index: ' + @IndexName + ' on ' + @TableName;
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM frag_cursor INTO @TableName, @IndexName;
END;

CLOSE frag_cursor;
DEALLOCATE frag_cursor;

In the WHERE block, our script filters the indexes to work on based on whether or not they pass the 30% threshold we set. This makes our automation more intelligent and helps to save resources.

We can schedule this automation to run weekly for typical production databases, daily if the database experiences heavy, constant insert/update/delete activity, and during off-peak hours (such as overnight or weekends) to minimize the impact on users and live queries.

Furthermore, we can schedule this script using a SQL Server Agent Job, setting the frequency and time based on our database workload.

6.1. Should We Always Rebuild Fragmented Indexes?

While rebuilding fragmented indexes improves performance, not all indexes are worth the effort, and can be skipped based on their size, usage, or impact on queries:

• Small indexes: indexes on small tables (e.g., a few hundred rows) typically load into memory easily, so fragmentation has minimal impact.
• Rarely used indexes: if queries seldom read an index, rebuilding it won’t yield real-world benefits.
• Disabled or unused indexes: rebuilding these wastes resources without improving performance.

Before scheduling complete rebuilds, reviewing index usage statistics using the dynamic management views, sys.dm_db_index_usage_stats, is good practice, ensuring that our maintenance efforts target only the indexes that matter.

7. Common Errors and Troubleshooting

Before or during the rebuild process, we can run into some errors:

• Cannot obtain an exclusive lock on the table: another process is either reading or writing to the table. Schedule rebuilds during off-peak hours or use ONLINE=ON in Enterprise Edition.
• Insufficient disk space: rebuilding operations require temporary disk space, especially with large indexes. Use SORT_IN_TEMPDB = ON to offload sorting, and ensure tempdb and the database have sufficient free space.
• The index cannot be rebuilt online: certain indexes (e.g., those on XML or spatial data) do not support online rebuilds. Rebuild such indexes offline or exclude them from your automation logic.
• Login failed or insufficient permission: the user lacks the ALTER permissions. Granting the necessary permissions can rectify this.

8. Conclusion

In this article, we explored how to rebuild fragmented indexes in a single table and the entire database to ensure our queries run smoothly and efficiently. By rebuilding fragmented indexes in our database tables, we can improve query response time, lower memory usage, and decrease server I/O load, especially in heavily used tables.

As always, the queries used in this article are available over on GitHub.