In order to keep the fragmentation ratio of indices low, you should re-organize the indices from time to time on MSSQL. Microsoft describes in the link below how you can check the fragmentation of indices on tables in MSSQL and then how you can re-organize (re-build) the indices to reduce the fragmentation percentage:


It would be nice to have a procedure that automatically re-organizes the indices in case the fragmentation percentage reaches a specific threshold. The procedure below does that:

create procedure ui_reorganize_indices @db_name nvarchar(127), @table_name nvarchar(127)
    declare @avg_fragmentation_in_percent float
    declare @index_name nvarchar(127)
    declare @alter_index_command nvarchar(256)

    declare rebuild_index_cursor cursor local for 
        select name AS IndedxName, avg_fragmentation_in_percent
        FROM sys.dm_db_index_physical_stats
            (DB_ID (@db_name)
                , OBJECT_ID(@table_name)
                , NULL
                , NULL
                , NULL) AS a
        INNER JOIN sys.indexes AS b
            ON a.object_id = b.object_id
            AND a.index_id = b.index_id
            and avg_fragmentation_in_percent > 20.0;

    open rebuild_index_cursor
        fetch next from rebuild_index_cursor into @index_name, @avg_fragmentation_in_percent
        while @@fetch_status = 0
            select @alter_index_command = formatmessage('alter index %s on %s REORGANIZE', @index_name, @table_name)
            print @alter_index_command
            exec (@alter_index_command)
            fetch next from rebuild_index_cursor into @index_name, @avg_fragmentation_in_percent
    close rebuild_index_cursor

Have you guys used similar procedures to improve the health of your indices?

  • 3
    \$\begingroup\$ Fortunately someone has already done the work for you on this. Check out ola.hallengren.com/… \$\endgroup\$
    – Bee_Riii
    Commented Apr 23, 2021 at 10:03


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.