Re-organizing indices in case of fragmentation is T-SQL

Question

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://learn.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?

Answer 1

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

Ola Hallengren do have a very good one, the most DBS use it.
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
It create a SQL Server-Agent job as well; just define a scheduling with your preferences.

Answer 2

Hi @Zaheer Abbas ,

Welcome to Microsoft Q&A!
You can also take a look at this blog especially the first link : https://www.mssqltips.com/sql-server-tip-category/39/fragmentation-and-index-maintenance/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.