How to asynchronously re-build your indexes in SQL

Sometimes you want to do something asynchronously in SQL and it really wasn't designed for that.

The example shown here is to re-build indexes for all of your tables at the same time. By this, I mean that a different SPID is used for each table so that they all run at once.

Be warned, this has the potential to warm your server up somewhat. I'm lucky that when I needed to do this I had a very good server, SQL 2012 Enterprise (so I could use the ONLINE = ON option, and a FusionIO card meaning that IO performance was not a concern). I wouldn't recommend using this in a production scenario, but if you need to do this during a maintenance window and don't want to sit there waiting as you do one index at a time, then this might help.

We needed to move a lot of data to a new server and then re-build every index before we went live. The window was tight so the decision was made to use more of the servers potential. Below is the approach we used.

The basic idea is to fire off a new SQL Agent job for every statement and rattle through a list of tables to do this. The jobs are set to delete themselves once done. It's nice and simple once you have it figured out.

USE MyDatabase

GO

SET NOCOUNT ON

DECLARE @TableList TABLE (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, SchemaName VARCHAR(100), TableName VARCHAR(100))

DECLARE @SQLCommand VARCHAR(1000)

DECLARE @MaxId INT

DECLARE @Counter INT

DECLARE @MySchemaName VARCHAR(100)

DECLARE @MyTableName VARCHAR(100)

DECLARE @MyDB VARCHAR(100)

SET @MyDB = db_name()

------

INSERT INTO @TableList (SchemaName, TableName)

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.Tables ORDER BY TABLE_SCHEMA, TABLE_NAME

SET @Counter = 1

SET @MaxId = (SELECT MAX(Id)+1 FROM @TableList)

------

WHILE @Counter < @MaxId

  BEGIN

    SELECT @MySchemaName = SchemaName, @MyTableName = TableName

    FROM @TableList WHERE Id = @Counter

    SET @SQLCommand = 'ALTER INDEX ALL ON ' + @MySchemaName + '.' + @MyTableName + ' REBUILD WITH (FILLFACTOR = 90, ONLINE=ON)'

    IF EXISTS(SELECT * FROM msdb.dbo.sysjobs where name = @SQLCommand)

       EXEC msdb.dbo.sp_delete_job @job_name=@SQLCommand;

    EXEC msdb.dbo.sp_add_job @job_name=@SQLCommand, @Delete_Level=3;

    EXEC msdb.dbo.sp_add_jobserver @job_name=@SQLCommand, @server_name = N'(local)';       

    EXEC msdb.dbo.sp_add_jobstep @job_name=@SQLCommand, @database_name = @MyDB, @command=@SQLCommand, @step_name=@SQLCommand, @on_success_action=3;   

    EXEC msdb.dbo.sp_add_jobstep @job_name=@SQLCommand, @command='SELECT 1', @step_name='End', @on_success_action=1;   

    EXEC msdb.dbo.sp_start_job @job_name=@SQLCommand;

    SET @Counter = @Counter + 1

  END

GO

Oh and watch for your transaction logs when you do this (if in Full Recovery Mode). They'll get big very quickly. Make sure you have plenty of space !