How to rebuild all indexes in MS SQL server database

Introduction

In this post, I am gonna share a complete SQL snippet allowing to rebuild all indexes in the MS SQL server database quickly. You can use this complete SQL code directly in SQL Server Management Studio or your tool of choice.

Rebuilding the indexes can heal index fragmentation and make a key difference when it comes to performance and database maintenance.

Why should I rebuild database indexes?

Heavily fragmented indexes can degrade query performance because additional I/O is required to locate data to which the index points. More I/O causes your application to respond slowly, especially when scan operations are involved

by Microsoft docs

Should I use an online or offline option?

Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online. For more information, see INDEX and Perform Index Operations Online

by Microsoft docs

Keep in mind that rebuilding with an online option will take longer and require more disk space.
When you want to ensure related apps availability, I would recommend going for an online option.

If you want to understand exactly how index rebuilding & reorganizing works - you should read the perfect post on the MS website.

How does the script work?

In short, the script works as follows:

  • Finds indexes to rebuild and store them in a temporary table named @tempIndexTable
  • Prints selected indexes to the output with metrics
  • Calculates indexes count
  • Starts while loop and iterates sequentially over each index
  • Executes the core operation (ALTER INDEX REBUILD WITH (ONLINE = ON) ) on each index

How to determine which indexes should be rebuilt?

We are able to find indexes to rebuild with a query combining:

  • sys.dm_db_index_physical_stats
  • sys.indexes
  • sys.objects

and based on Microsoft's recommendations regarding avg_fragmentation_in_percent values:

MSSQL avg fragmentation in percent recommendations

SELECT i.[name],
s.[index_type_desc], --s.[index_type_desc]
o.[name],
s.[avg_fragmentation_in_percent],
o.type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(@DatabaseName), NULL, NULL, NULL, NULL) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE (s.avg_fragmentation_in_percent > 30 and (i.[Name] like '%IX%' OR i.[Name] like '%PK%'))

Once you run the script, you should get a list of indexes to heal with handy information such as:

  • IndexName
  • IndexType (CLUSTERED INDEX, NONCLUSTERED INDEX)
  • TableName
  • AvgFragmentationInPercent
  • ObjectTypeDescription (ex. USER_TABLE)

Here is an example report for the Umbraco 7 database:

MSSQL agv fragmentation in percent example Umbraco database

Full SQL code (original)

DECLARE @DatabaseName NVARCHAR(MAX) = 'db-name'
DECLARE @IndexName NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(MAX)
DECLARE @CurrentIndexName NVARCHAR(MAX)
DECLARE @CurrentTableName NVARCHAR(MAX)
DECLARE @CmdRebuidIndex NVARCHAR(MAX)

DECLARE @tempIndexTable TABLE
(
	RowID int not null primary key identity(1,1),	
	IndexName NVARCHAR(MAX),
	IndexType NVARCHAR(MAX),
	TableName NVARCHAR(MAX),
	AvgFragmentationInPercent FLOAT,
	ObjectTypeDescription NVARCHAR(MAX)		
)

INSERT INTO @tempIndexTable (IndexName, IndexType, TableName, AvgFragmentationInPercent, ObjectTypeDescription) (
	SELECT i.[name],
	s.[index_type_desc], --s.[index_type_desc]
	o.[name],
	s.[avg_fragmentation_in_percent],
	o.type_desc
	FROM sys.dm_db_index_physical_stats (DB_ID(@DatabaseName), NULL, NULL, NULL, NULL) AS s
	INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
	INNER JOIN sys.objects AS o ON i.object_id = o.object_id
	WHERE (s.avg_fragmentation_in_percent > 30 and (i.[Name] like '%IX%' OR i.[Name] like '%PK%'))	
)

PRINT 'Indexes to rebuild:'
SELECT * FROM @tempIndexTable;

RETURN; -- Uncomment this line if you want to run the command

DECLARE @totalCount INTEGER
SELECT @totalCount = count(1) FROM @tempIndexTable
DECLARE @counter INTEGER = 1

WHILE(@counter <= @totalCount)
BEGIN	

    SET @CurrentIndexName = (SELECT top 1 IndexName FROM @tempIndexTable WHERE RowID = @counter);
	SET @CurrentTableName = (SELECT top 1 TableName FROM @tempIndexTable WHERE RowID = @counter)
	
	PRINT 'Rebuild starting [' + @CurrentIndexName + 
	'] ON [dbo].[' + @CurrentTableName + '] at ' 
	+ convert(varchar, getdate(), 121)

	BEGIN TRY
		SET @CmdRebuidIndex = 'ALTER INDEX [' + @CurrentIndexName + '] ON [dbo].[' + @CurrentTableName + '] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
			EXEC (@CmdRebuidIndex)
			PRINT 'Rebuild executed [' + @CurrentIndexName + '] ON [dbo].[' + @CurrentTableName + '] at ' + convert(varchar, getdate(), 121)
	END TRY
	BEGIN CATCH
		PRINT 'Failed to rebuild [' + @CurrentIndexName + '] ON [dbo].[' + @CurrentTableName + ']'
		PRINT ERROR_MESSAGE()
	END CATCH

	SET @counter += 1;
END

Full SQL code (updated)

My reader Paweł found a place to improve the code (thanks!), so I am sharing fresh code here: 

DECLARE @DatabaseName NVARCHAR(MAX) = '[database]'
DECLARE @CurrentSchemaName NVARCHAR(MAX)
DECLARE @CurrentIndexName NVARCHAR(MAX)
DECLARE @CurrentTableName NVARCHAR(MAX)
DECLARE @CmdRebuidIndex NVARCHAR(MAX)

DECLARE @tempIndexTable TABLE
(
    RowID                       int not null primary key identity(1,1),   
    IndexName                   NVARCHAR(MAX),
    IndexType                   NVARCHAR(MAX),
    TableName                   NVARCHAR(MAX),
    SchemaName                  NVARCHAR(MAX),
    AvgFragmentationInPercent   FLOAT,
    ObjectTypeDescription       NVARCHAR(MAX)     
)

INSERT INTO @tempIndexTable (IndexName, IndexType, TableName, SchemaName, AvgFragmentationInPercent, ObjectTypeDescription) (
    SELECT
        i.[name],
        s.[index_type_desc], --s.[index_type_desc]
        o.[name],
        sch.name,
        s.[avg_fragmentation_in_percent],
        o.type_desc
    FROM
        sys.dm_db_index_physical_stats (DB_ID(@DatabaseName), NULL, NULL, NULL, NULL)   AS  s   INNER JOIN
        sys.indexes                                                                     AS  i   ON  s.object_id = i.object_id
                                                                                                    AND s.index_id = i.index_id INNER JOIN
        sys.objects                                                                     AS  o   ON  i.object_id = o.object_id   INNER JOIN
        sys.schemas                                                                     AS  sch ON  sch.schema_id = o.schema_id
    WHERE (s.avg_fragmentation_in_percent > 30 and (i.[Name] like '%IX%' OR i.[Name] like '%PK%'))  
)

PRINT 'Indexes to rebuild:'
SELECT * FROM @tempIndexTable;

--RETURN; -- Uncomment this line if you want to run the command

DECLARE @totalCount INTEGER
SELECT @totalCount = count(1) FROM @tempIndexTable
DECLARE @counter INTEGER = 1

WHILE(@counter <= @totalCount)
BEGIN   

    SET @CurrentIndexName = (SELECT top 1 IndexName FROM @tempIndexTable WHERE RowID = @counter);
    SET @CurrentTableName = (SELECT top 1 TableName FROM @tempIndexTable WHERE RowID = @counter);
    SET @CurrentSchemaName = (SELECT top 1 SchemaName FROM @tempIndexTable WHERE RowID = @counter);
    
    PRINT 'Rebuild starting (' + convert(VARCHAR(5), @counter) + '/' + convert(VARCHAR(5), @totalCount) + ') [' + @CurrentIndexName + 
    '] ON [' + @CurrentSchemaName + '].[' + @CurrentTableName + '] at ' 
    + convert(varchar, getdate(), 121)

    BEGIN TRY
        SET @CmdRebuidIndex = 'ALTER INDEX [' + @CurrentIndexName + '] ON [' + @CurrentSchemaName + '].[' + @CurrentTableName + '] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
            EXEC (@CmdRebuidIndex)
            PRINT 'Rebuild executed [' + @CurrentIndexName + '] ON [' + @CurrentSchemaName + '].[' + @CurrentTableName + '] at ' + convert(varchar, getdate(), 121)
    END TRY
    BEGIN CATCH
        PRINT 'Failed to rebuild [' + @CurrentIndexName + '] ON [' + @CurrentSchemaName + '].[' + @CurrentTableName + ']'
        PRINT ERROR_MESSAGE()
    END CATCH

    SET @counter += 1;
END

Final words

Once you execute the script, the MSSQL engine will automatically start rebuilding the selected indexes but be aware it can take a while. The operation time depends on the size of the indexes. 

To download the source code for this post, please visit GitHub

References

  • https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15 
Comments
Leave a Comment