The query below 1 can be used to identify those indexes that should be rebuilt (using ALTER INDEX REBUILD WITH (ONLINE = ON), or those that should be reorganized (USING ALTER INDEX REORGANIZE):
SELECT object_name(a.object_id) as TableName, a.index_id, a.Index_Type_Desc, b.name AS IndexNmae, avg_fragmentation_in_percent, CASE WHEN avg_fragmentation_in_percent > 5 AND avg_fragmentation_in_percent <= 30 THEN 'REORGANISE' WHEN avg_fragmentation_in_percent > 30 THEN 'REBUILD' END AS Suggestion FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a LEFT OUTER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE avg_fragmentation_in_percent > 5 ORDER BY object_name(a.object_id) GO
This information was obtained from http://msdn.microsoft.com/en-us/library/ms189858.aspx, but I have enhanced the query to return the table name and a “suggestion”.
- Please ensure you test any script taken from my website on a test/development machine, before running on a production server. ↩