Different Methods to Rebuild All Indexes for All Tables

Total: 1 Average: 5

There are several methods of rebuilding all indexes of all tables, 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.

CodingSight - Different Methods to Rebuild All Indexes for All Tables

Rebuild indexes using SQL Server database maintenance plans

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 the database maintenance plan, launch SQL Server Management Studio > expand the database instance > Management > right-click on the maintenance plan > New Maintenance Plan.

To create the 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.

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.
The next step is configuring the maintenance plan. Double-click on it and configure the options on the Rebuild Index Task

Now, let us configure the schedule.

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

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.
The New Job Schedule window will open. Let’s configure the following settings

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.

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:

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:

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.

Rebuild indexes using ALTER INDEX statement

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.

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

T-SQL Scripts for rebuilding based on the fragmentation percentage

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 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.

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:

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'
You will get to the New Job Step window to configure that step

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

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.
Our job should be executed at 1:00 AM. Accordingly, we configure the schedule

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.

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 …

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:

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

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 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.

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.