Below is a script to identify the
fragmentation levels in indexes:
SELECT OBJECT_NAME(ind.OBJECT_ID)
AS TableName,
ind.name AS IndexName,
indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent,
indexstats.avg_fragment_size_in_pages,
indexstats.page_count,
indexstats.avg_page_space_used_in_percent,
indexstats.record_count,
indexstats.ghost_record_count,
indexstats.fragment_count,
indexstats.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(DB_ID(),
NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes ind
ON ind.object_id =
indexstats.object_id
AND ind.index_id =
indexstats.index_id
WHERE
indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent
DESC
