3 Methods to Rebuild All Indexes for All Tables with T-SQL in SQL Server Database

There are several methods of how to rebuild all indexes of all tables in SQL Server, among them:

1. Using SQL Server maintenance plans.
2. Using T-SQL script based on the fragmentation percent.
3. Using ALTER INDEX command.

In this article, we’ll explore these methods and illustrate them with practical examples.

1. SQL Server Rebuild Index Maintenance Plan

The first option to review is rebuilding indexes with the database maintenance plans. The maintenance plans are available under the management folder of SQL Server Management Studio.

To create SQL database maintenance plan, launch SQL Server Management Studio > expand the database instance > Management > right-click on the maintenance plan > New Maintenance Plan.

Specify the name of the maintenance plan. Then, drag and drop Rebuild Index Task into the maintenance plan designer. Rename the task to Index Maintenance.

The next step is configuring the maintenance plan. Double-click on it and configure the options on the Rebuild Index Task as follows:

• Select AdventureWorks2017 database from the Database(s) drop-down menu.
• To rebuild indexes of all tables, select Tables and Views from the Object drop-down box.
• Check Sort results in tempdb.
• MAXDOP – set 2 (two).
• In our case, we’ll rebuild indexes only if the Fragmentation value is higher than 20%. Therefore, set 20 in the respective field.
• Click OK to save the index configuration and close the Rebuild Index Task window.

Now, let us configure the schedule.

Click on the calendar icon on top of the maintenance plan designer:

The New Job Schedule window will open. Let’s configure the following settings:

• Run the job every day. In the Schedule type menu, we select Recurring. Then, in the Frequency section, we select Occurs > Daily.
• Recurs every > 1 (day).
• Daily frequency > Occurs once at > specify the precise time. In our case, it is 1 AM.
• Click OK.

After that, save the maintenance plan.

The created maintenance plans are available in the SSMS Maintenance Plan directory. To view the schedule associated with the particular maintenance plan, check the Jobs directory under SQL Server Agent.

To test the job, right-click on its name in the Maintenance Plans directory, and select Execute from the menu:

The execution starts. When it completes successfully, you will see the following dialog box:

This was the common method of rebuilding indexes with maintenance plans. Now, let’s proceed to the next method – using the T-SQL scripts.

2. SQL Server ALTER INDEX Rebuild

The ALTER INDEX command can be used to rebuild all indexes of the table. The syntax is the following:

ALTER INDEX ALL ON [table_name] REBUILD

Note: The table_name parameter specifies the name of the table where we want to rebuild all indexes in SQL Server.

For instance, we want to rebuild all indexes of [HumanResources].[Employee]. The query should be as follows:

use AdventureWorks2017
go
ALTER INDEX ALL ON [HumanResources].[Employee] REBUILD
Go

3. SQL Server Script to Rebuild All Indexes for All Tables Based on Fragmentation

Index maintenance is resource-intensive. Besides, it locks the table where it is rebuilding the index. To avoid such complications, we must rebuild the index where SQL Server index fragmentation is higher than 40%.

To illustrate the case, I have created a T-SQL script that rebuilds indexes with a fragmentation degree higher than 30%. Let’s explore its parts and functions.

Variables and temp table declaration

First, we need to create temp tables and variables:

• @IndexFregQuery – stores the dynamic query used to populate fragmented indexes.
• @IndexRebuildQuery – holds the ALTER INDEX query.
• @IndexName – the index name we want to rebuild
• @TableName – the table name where we want to rebuild the index.
• @SchemaName – the schema name where we want to rebuild the index.
• #Fregmentedindex – the 3-columns table that stores the index name, the table name, and the schema name.

The following code declares our variables and the temp table:

declare @i int=0
declare @IndexCount int
declare @IndexFregQuery nvarchar(max)
declare @IndexRebuildQuery nvarchar(max)
declare @IndexName varchar(500)
declare @TableName varchar(500)
declare @SchemaName varchar(500)
create table #Fregmentedindex(Index_name varchar(max),table_name varchar(max),schema_name varchar(max))

Get the list of fragmented indexes

Our next step is to populate the list of indexes with the fragmentation degree of 30% or higher. We must insert those indexes into the #FregmentedIndexes table.

The query must populate the schema name, the table name, and the index name to insert them into the temp table. Have a look at that query:

set @IndexFregQuery='SELECT i.[name],o.name,sch.name
	FROM   [' + @DatabaseName + '].sys.dm_db_index_physical_stats (DB_ID('''+ @DatabaseName +'''), NULL, NULL, NULL, NULL) AS s
	INNER JOIN [' + @DatabaseName + '].sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
	INNER JOIN [' + @DatabaseName + '].sys.objects AS o ON i.object_id = o.object_id
	INNER JOIN [' + @DatabaseName + '].sys.schemas AS sch ON o.schema_id=sch.schema_id
	WHERE (s.avg_fragmentation_in_percent > 30 ) and i.name is not null'
insert into #Fregmentedindex(Index_name,table_name,schema_name) exec sp_executesql @IndexFregQuery

Create a dynamic SQL Query

Finally, we must build the dynamic ALTER INDEX command and execute it.

To generate the command, we are using the WHILE loop. It iterates through the #FregmentedIndexes table and populates the schema name, the table name, and the index name to save them in @SchemaName, @TableName, and @IndexName. The parameters’ values are appended in the ALTER INDEX command.

The code is the following:

set @IndexCount=(select count(1) from #Fregmentedindex)
While (@IndexCount>@i)
begin 
(select top 1 @TableName=table_name, @IndexName=Index_name,@SchemaName= schema_name from #Fregmentedindex)
Set @IndexRebuildQuery ='Alter index [' + @IndexName +'] on ['+@DatabaseName +'].['+@SchemaName+'].[' + @TableName +'] rebuild'
exec sp_executesql @IndexRebuildQuery 
set @i=@i+1
delete from #Fregmentedindex where Index_name=@IndexName and table_name=@TableName
End

I have encapsulated the entire code in the sp_index_maintenance stored procedure created in the DBATools database. The code is following:

use DBATools
go
Create procedure sp_index_maintenance_daily
@DatabaseName varchar(50)
as
begin
declare @i int=0
declare @IndexCount int
declare @IndexFregQuery nvarchar(max)
declare @IndexRebuildQuery nvarchar(max)
declare @IndexName varchar(500)
declare @TableName varchar(500)
declare @SchemaName varchar(500)
create table #Fregmentedindex(Index_name varchar(max),table_name varchar(max),schema_name varchar(max))
set @IndexFregQuery='SELECT i.[name],o.name,sch.name
	FROM   [' + @DatabaseName + '].sys.dm_db_index_physical_stats (DB_ID('''+ @DatabaseName +'''), NULL, NULL, NULL, NULL) AS s
	INNER JOIN [' + @DatabaseName + '].sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
	INNER JOIN [' + @DatabaseName + '].sys.objects AS o ON i.object_id = o.object_id
	INNER JOIN [' + @DatabaseName + '].sys.schemas AS sch ON o.schema_id=sch.schema_id
	WHERE (s.avg_fragmentation_in_percent > 30 ) and i.name is not null'
insert into #Fregmentedindex(Index_name,table_name,schema_name) exec sp_executesql @IndexFregQuery
set @IndexCount=(select count(1) from #Fregmentedindex)
While (@IndexCount>@i)
begin 
(select top 1 @TableName=table_name, @IndexName=Index_name,@SchemaName= schema_name from #Fregmentedindex)
Set @IndexRebuildQuery ='Alter index [' + @IndexName +'] on ['+@DatabaseName +'].['+@SchemaName+'].[' + @TableName +'] rebuild'
exec sp_executesql @IndexRebuildQuery 
set @i=@i+1
delete from #Fregmentedindex where Index_name=@IndexName and table_name=@TableName
End
End

Once the procedure is ready, we can configure the SQL Job.

Expand SQL Server Agent > right-click on Jobs > New Job.

The New Job window opens where you should specify the desired job name.

To create a job step, navigate to the Steps section > the New button:

You will get to the New Job Step window to configure that step.

Enter the desired step name and enter the following code into the text box:

use DBATools
go
exec sp_index_maintenance_daily 'AdventureWorks2017'

To configure the schedule, go to Schedules > click New.

Our job should be executed at 1:00 AM. Accordingly, we configure the schedule:

• Schedule type > Recurring.
• The Frequency section > Occurs > Daily; Recurs every > 1 (one).
• The Daily frequency section > Occurs once at > 01:00:00.
• Click OK.

You will be transferred back to the New Job section. Click OK there too to create the job.

The newly created job is available in the Jobs directory under the SQL Server Agent folder.

We need now to test the job: right-click on it and select Start Job …

The job starts, and on successful completion, you will see the following message:

If we compare clustered index vs non clustered index, we’ll see that creating a unique clustered index increases the efficiency of query performance. Indexed views in SQL Server may be used to speed up query execution. Check out also how to manage indexes using SQL Index Manager.

Summary

The current article presented three functional ways of rebuilding indexes of all tables. We’ve explored them all with step-by-step guidelines and practical SQL index examples to illustrate the job configuration. The choice of the suitable variant is yours, and we hope that this article was of help to you.

Tags: indexes, rebuild indexes, sql server Last modified: September 24, 2021