Welcome to ciysys blog

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:

Assumptions in this article

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

Useful links

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.