Rebuild and Reorganize indexes on all tables in MS database

As part of database maintenance, indexes on databases have to be rebuilt or reorganised depending on how fragmented the indexes are. From the article Reorganize and Rebuild Indexes, the advice is to reorganise index if avg_fragmentation_in_percent value is between 5 and 30 and to rebuild index if it is more than 30%.

The script below queries all fragmented indexes more than 5 percent and using a cursor a loop is performed on the results to rebuild or reorganise  indexes depending on the percentage of fragmentation using dynamic SQL, i.e.

The script can be downloaded from technet gallery  , i.e. if avg_fragmentation_in_percent value is between 5 and 30 then reorganise else rebuild.

declare @tableName nvarchar(500)
declare @indexName nvarchar(500)
declare @indexType nvarchar(55)
declare @percentFragment decimal(11,2)

declare FragmentedTableList cursor for
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, AS IndexName, indexstats.index_type_desc AS IndexType,
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
-- indexstats.avg_fragmentation_in_percent , e.g. >30, you can specify any number in percent
indexstats.avg_fragmentation_in_percent > 5
AND ind.Name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC

OPEN FragmentedTableList
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment

print 'Processing ' + @indexName + 'on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented'
if(@percentFragment<= 30)
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REBUILD; ')
print 'Finished reorganizing ' + @indexName + 'on table ' + @tableName
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REORGANIZE;')
print 'Finished rebuilding ' + @indexName + 'on table ' + @tableName
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment
CLOSE FragmentedTableList
DEALLOCATE FragmentedTableList


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s