How to find the fragmentation in indexes?

Below is the query to get the fragmentation of indexes in database.

SELECT DB_NAME(database_id), OBJECT_NAME(p.object_id), AS indexname, index_type_desc, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) AS p
INNER JOIN sys.indexes AS s ON p.index_id = s.index_id and p.object_id= s.object_id
WHERE --page_count >= 100 and
avg_fragmentation_in_percent >= 10
ORDER BY avg_fragmentation_in_percent DESC

No comments:

Post a Comment