In my previous post regarding indexes I presented a method of inspecting all indexes in a database using sys.dm_db_index_physical_stats. Let us develop it a little bit more.
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:
- 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.
- For each index perform rebuild or reorganization based on fragmentation.
- (optional) Place it in a maintenance plan (in Execute T-SQL Statement Task) or SQL Server Agent job to run it periodically.
First step is something already mentioned:
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
Notice variable declarations and a cursor for a future loop. Since I already described this part of the query, let’s fast forward to second step. Here’s the code:
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
Using a cursor, we can loop through all the indexes and then choose appropriate action based on value of @Frag variable. In addition, you will have a trace of actions in the script output.
Whole script is available for download here. As usual, I await your comments and suggestions.