Rebuilding indexes to maintain the query performance

Published on: 24th May 2010

Updated on: 14th May 2021

Overview

The data operation such as inserting new records, updating records and deleting records will cause index fragmented. This is because the data changes must be physically reflected in the data storage and the index storage. And fragmented index degrades the query performance. To avoid the data operation affecting the query performance, we have to rebuild the indexes periodically.

To do that, you have to understand the types of rebuilding process:

• ALTER INDEX..REBUILD or DBCC DBREINDEX (deprecated) is an offline reindexing. It will drop the existing index and then build a new one. As a result, the new index will have the optimized structure for query. The disadvantage of offline reindexing is that it takes a longer time as compared to online reindexing because.

• In case you have a mission critical database, you may consider using ALTER INDEX..REORGANIZE or DBCC INDEXDEFRAG (deprecated) to do the online reindexing. This reindexing process basically scans the index leaf and moves to a better optimized location within the index storage.

Assumptions in this article

• You know what an index is.
• Sufficient index has covered most of your queries.

The stored proc that helps to reindex

The following stored proc is showing how to get all the index names, generate dynamic query and execute it.

if exists( select * from sys.objects where name ='mgt_reindex')
    drop proc mgt_reindex
go

create proc mgt_reindex
as
begin

    declare
        @tb_name sysname,
        @idx_name sysname,
        @sql nvarchar(500)

    declare cr cursor
    for

        select
            o.name as tb_name, i.name as idx_name
        from sys.indexes i
        inner join sys.objects o on o.object_id = i.object_id
        where o.type='u'
            and i.name is not null
            and i.type = 2--non clustered
        order by o.name, i.name

    open cr

    fetch next from cr
    into @tb_name, @idx_name

    while (@@fetch_status = 0)
    begin

        set @sql = 'alter index '
                    + @idx_name
                    + ' on ' + @tb_name
                    + ' rebuild '

        exec sp_executesql @sql  

        fetch next from cr
        into @tb_name, @idx_name

    end

    close cr
    deallocate cr

end
go

The strategy of reindexing

• It is advisable to do an online reindexing on a weekly basis.

• Then, followed by an offline reindexing on a monthly basis.

• If you have a large database, you may consider splitting the reindexing process into a few smaller jobs. This can be done by,

  1. Finding out the number of records in each table.
  2. Decide how many reindexing jobs to be created based on the table size. The most important requirement is that we should avoid reindexing all large tables in the same job. For example, you have five huge transaction tables. In this case, you might want to create five reindexing jobs which run on a monthly basis and which should not run at the same time.

• Finally, create a schedule task to run the stored proc.

Useful links

• For more information on reindex, please visit the following page.

  https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-dbreindex-transact-sql?view=sql-server-ver15

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.