SQL: Rebuild or Reorganize Indexes?

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”.

  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.