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
  • 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:

Full SQL code 

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

Finding Indexes to heal 

Once you run the script, you should get a list of indexes to be rebuilt with additional 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:

After executing the script the MSSQL engine will start rebuilding the selected indexes and 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