You should REORGANIZE if the fragmentation is less than 30 and REBUILD above. I usually do not worry about low fragmented indexes. I use something like this which creates a table of executable code. The comment after the code is so I can copy the two columns and know the percent of fragmentation and still run the DECLARE @strSQL NVARCHAR(2000) SET @strSQL = 'IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'') RETURN; ' + 'USE ?; ' + 'SELECT ''?'' AS DBName, t.name AS TableName, ' + 'CASE WHEN avg_fragmentation_in_percent < 30 ' + 'THEN ''ALTER INDEX ['' + s.name + ''] ON ['' + t.name + ''] REORGANIZE; --'' ' + 'ELSE ''ALTER INDEX ['' + s.name + ''] ON ['' + t.name + ''] REBUILD; --'' ' + 'END AS ExecuteCode, avg_fragmentation_in_percent ' + 'FROM sys.dm_db_index_physical_stats (DB_ID(DB_NAME()), NULL, NULL , NULL, N''LIMITED'') d ' + 'INNER JOIN sysindexes s ON OBJECT_ID = s.id and d.index_id = s.indid ' + 'INNER JOIN sys.tables t ON s.id = t.object_id ' + 'ORDER BY DBName, s.name DESC; 'print @strSQL EXECUTE sp_MSForEachdB @strSQLgoExample (you will need USE AdventurWorks2008R2):ALTER INDEX [PK_Vendor_BusinessEntityID] ON [Vendor] REBUILD; -- 50
djj 
automating index rebuilds...
Posted - 2013-05-07 : 05:58:04
