Technology: Index fragmentation contd...

Another query to get the fragmentation details across all the indexes in the current database. Earlier post on index fragmentation was http://sqlnfr.blogspot.in/2011/12/index-fragmentation.html

SELECT

o.name TableName,

i.name IndexName,

s.index_level,

s.avg_fragmentation_in_percent MaxFragmentation

FROM

sys.indexes i

INNER JOIN

sys.objects o

i.object_id = o.object_id

INNER JOIN

sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, 'DETAILED') s

s.object_id = o.object_id

AND i.index_id = s.index_id

INNER JOIN

( SELECT

OBJECT_ID,index_id ,

MIN(index_level) as index_level

FROM

sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, 'DETAILED')

GROUP BY

OBJECT_ID,index_id ) AS s2

s.object_id = s2.object_id

AND s.index_id = s2.index_id

AND s.index_level = s2.index_level

WHERE

I.type IN (1,2)

AND O.type = 'U'

ORDER BY 1,2 ;

Enjoy!!!

Technology