Rebuild Index in sql server

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 be rebuilding or reorganizing, 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