How to rebuild all indexes in MS SQL server database

ByPiotr Bach Reading Time: 6 minutes

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 to index rebuilding & reorganizing works – you should read the perfect post on the MS website.

SQL code outline

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:

Ms When To Rebuild Sql Indexes Based On Fragmentation
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:

Sql Index To Rebuild Select

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 updated 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. Once the indexes are fine, consider updating the database statistics with the command:

exec sp_updatestats

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 

Similar Posts

Reset the identity column value in MSSQL

ByPiotr Bach Reading Time: 4 minutes

How to reset the identity column value in the SQL server? To reset the current identity value for the table you should use DBCC CHECKIDENT function. It’s a great way to control the numbers sequence for the identity column. DBCC CHECKIDENT function is very useful for database maintenance and can be applied to numeric types: smallint bigint tinyint numeric integer decimal Why should use…

How to delete a large number of records from MSSQL table

ByPiotr Bach Reading Time: 6 minutes

How to delete records from the MSSQL table page by page? In this short post, I am gonna share a quick SQL snippet allowing us to remove a lot of records from the MSQL table. Contrary to appearances, deleting a large amount of data from a SQL table may not be a simple task. It is good practice to avoid long-running…