SQL Server - Index Rebuild OR Reorganize

Index maintenance is one of most important task for a DBA to perform the database performance & tuning.

It is important to know to when an index requires to be rebuild or reorganized, this consideration done on the basis of INDEX FRAGMENTATION PERCENTAGE.

If fragmentation percentage is less than 30% then REORGANIZE INDEX fragmentation is more than 30% then REBUILD INDEX.

Below query will do the job, it’ll generate index rebuild or reorganize statement based on fragmentation percent level and this query will work fine in SQL Server 2005/2008 and higher versions.

There will an option included in the output script and that is ONLINE=ON. This will get executed only in Enterprise, Developer and Evaluation Edition of SQL Server. 

SELECT Db_name(a.database_id)   [Db Name],

       Object_name(a.object_id) Table_Name,

       a.index_id,

       b.NAME,

       a.avg_fragmentation_in_percent,

       record_count,

       a.avg_fragment_size_in_pages,

       page_count,

       fragment_count,

       b.fill_factor,

       a.avg_page_space_used_in_percent,

       a.record_count,

       CASE

         WHEN a.avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ' + b.NAME + ' on  '

                                                       + Db_name(a.database_id) + '.'

                                                       + Object_schema_name((a.object_id)) + '.'

                                                       + Object_name(a.object_id)

                                                       + ' REBUILD with (FILLFACTOR= 80 , SORT_IN_TEMPDB = ON, ONLINE = ON) '

         WHEN a.avg_fragmentation_in_percent < 30 THEN 'ALTER INDEX ' + b.NAME + ' on  '

                                                       + Db_name(a.database_id) + '.'

                                                       + Object_schema_name((a.object_id)) + '.'

                                                       + Object_name(a.object_id) + ' REORGANIZE  '

       END                      AS [SCRIPT]

FROM   sys.Dm_db_index_physical_stats (Db_id(), NULL, NULL, NULL, 'DETAILED') AS a

       JOIN sys.indexes AS b

         ON a.object_id = b.object_id

            AND a.index_id = b.index_id

WHERE  a.database_id = Db_id()

       AND a.avg_fragmentation_in_percent <> 0

ORDER  BY a.object_id