TSQL Script to maintain indexes based on fragmentation

Suppose you want to loop through all indexes and rebuild those which fragmentation exceeds 30 percent, and reorganize those which fragmentation is between 5 and 30 percent. Rebuilding or reorganizing all indexes is not a problem – you can prepare a maintenance plan and incorporate a particular task. But it will not solve the problem – this way you can apply only action to all the indexes in a database. You can say there is no problem in rebuilding all indexes, but there is – there is no point rebuilding those which are barely fragmented since it is a waste of resources (disk space, CPU and I/O), extra log space is being used to record all the operations and it may not give any performance boost at all. On the other hand – reorganizing some indexes may give no performance bonus, especially at high level of fragmentation, when it’s easier (and better) to rebuild.

So the problem stated in the beginning may be solved this way:

1. Extract information about all indexes to be maintained (note that the scope can be narrowed to a single table) – you need index name, table name and fragmentation.
2. For each index perform rebuild or reorganization based on fragmentation.
3. (optional) Place it in a maintenance plan (in Execute T-SQL Statement Task) or SQL Server Agent job to run it periodically.

Here is the TSQL code for reference:

————————————————–

DECLARE @IndexName varchar(255)DECLARE @TableName varchar(255)declare @Frag floatDECLARE TableCursor CURSOR FORSELECT si.[name] as index_name,    sdm.avg_fragmentation_in_percent,    so.[name] as table_nameFROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) sdm    inner join sys.indexes si on sdm.object_id = si.object_id and si.index_id = sdm.index_id    inner join sys.objects so on so.object_id = si.object_idOPEN TableCursor FETCH NEXT FROM TableCursor INTO @IndexName, @Frag , @TableNameWHILE @@FETCH_STATUS = 0BEGIN    print @TableName + ' - ' + @IndexName + '...'    if @Frag < 30 and @Frag > 5    begin        print ' REORGANIZE '        exec ('ALTER INDEX ' + @IndexName + ' ON [' + @TableName + '] REORGANIZE')    end    else if @Frag > 30    begin        print ' REBUILD '        exec ('ALTER INDEX ' + @IndexName + ' ON [' + @TableName + '] REBUILD')    end    print 'done' + char(13)    FETCH NEXT FROM TableCursor INTO @IndexName, @Frag, @TableNameEND CLOSE TableCursorDEALLOCATE TableCursor