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 float

DECLARE TableCursor CURSOR FOR
SELECT si.[name] as index_name,
    sdm.avg_fragmentation_in_percent,
    so.[name] as table_name
FROM 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_id

OPEN TableCursor 

FETCH NEXT FROM TableCursor INTO @IndexName, @Frag , @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    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, @TableName
END 

CLOSE TableCursor
DEALLOCATE TableCursor