0
\$\begingroup\$

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:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

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)
as
begin 
    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
        begin
            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
        end
    close rebuild_index_cursor
end
go

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.