Rebuilding All Indexes on a Table

Sometimes you have a need to rebuild all indexes on a table.  This could be because of an ETL process, or other data loading process that renders the indexes out of date.
I recently experienced this issue on during ETL processing.

I could have scripted out all of the index rebuilds for the table and then added to an Execute SQL task within SSIS. However, if any index should be deleted or renamed, then the Execute SQL task would fail, and the ETL would stop. Also, if we added new indexes, they would not get rebuilt until we added the index to the the Execute SQL task.

So I wrote a stored procedure to rebuild all of the indexes on the a table, or, just one index on a table.

I wanted to share that procedure with you, since I think this may be a common scenario.

-- =============================================
-- spwRebuildTableIndexes
--
-- Author:        Bob Pearson
-- Create date: 2013-03-14
-- Description:    Rebuilds specified index or all indexes on a table
--
-- Updates
-- Date            Who        What
-- --------        ----    ----------------------------
--
-- =============================================
ALTER PROCEDURE [dbo].[spwRebuildTableIndexes]
    @TableName varchar(100),
    @IndexName varchar(100) = null

AS
BEGIN

    Set NOCOUNT ON;

    Declare @SQL varchar(max) = '';
   
    if @IndexName is not null
    BEGIN
        Select @SQL = 'Alter Index ' + @IndexName + ' on ' + @TableName + ' REBUILD;';
        Execute (@SQL);
        print @TableName + '.' + @IndexName + ' Rebuilt...'
        Return;
    END

    Declare cur cursor for

        Select    i.name
        from    sys.indexes i
        inner join
                sys.objects o
        on        i.object_id = o.object_id
        where    o.name = @TableName and o.type = 'U'

    for read only;

    Open Cur;

    Fetch Next from Cur into @IndexName;

    While @@FETCH_STATUS = 0
    BEGIN       
        Select @SQL = 'Alter Index ' + @IndexName + ' on ' + @TableName + ' REBUILD;';
        Execute (@SQL);
       
        print @TableName + '.' + @IndexName + ' Rebuilt...'
       
        Fetch Next from Cur into @IndexName;
    END
   
    Close Cur;
    Deallocate Cur;
END

peace

Performance Tuning and Cache

When tuning queries and indexes for performance, it's a good idea to clear the cache when running benchmarks.
For example, when you first run a query, a plan is created, and then data is fetched from the drive.
On the next execute, your query plan won't need to be created, and the results will probably come from the cache, giving a false indication of how long the query will run.

So to clear the cache, execute these two statements:

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

Then you can test with each execution being treated equally.

Take care on the FreeProcCache, as this is a server level command.   Do not run this on a production box unless you really know what you are doing!

peace