Recently I had to brush up my skills on index fragmentation and as a result pulled this query together from a number of useful resources on the net. Just replace ‘DATABASE_NAME’ and run it against the specified database to receive a list of indexes that need attention.
SELECT OBJECT_NAME(iStats.object_id), i.name, iStats.*, i.* FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') iStats INNER JOIN sys.indexes i ON iStats.object_id = i.object_id AND iStats.index_id = i.index_id AND iStats.index_id 0 WHERE (avg_fragmentation_in_percent > 10 OR avg_page_space_used_in_percent < 75) ORDER BY iStats.avg_fragmentation_in_percent DESC
Internal Fragmentation is represented by the ‘avg_page_space_used_in_percent’ column which occurs when there are INSERTS, UPDATES, DELETES to a table leaving empty space within an allocated page. The poor allocation of space leads to more I/O as more pages are needed to hold same data which can cause poor query performance. Internal fragmentation is indicated when this value falls below 75
External Fragmentation is represented by the ‘avg_fragmentation_in_percent’ column. One instance when it can occur is when all records from a page are deleted and the page gets de-allocated from the index which creates gaps and increase the fragmentation. External fragmentation is indicated when this value exceeds 10.
Here are some good resources to that explain the two types of fragmentation in more detail:
And here is a query that will generate either index rebuild statements or reorganisation statements.
SELECT DISTINCT * FROM ( SELECT 'ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + t.name + '] REBUILD' AS stm FROM sys.indexes i INNER JOIN sys.tables t ON t.object_id = i.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') iStats ON t.object_id = iStats.object_id AND i.index_id = iStats.index_id WHERE iStats.avg_fragmentation_in_percent > 10 AND i.name IS NOT NULL ) AS tmp