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
ON
i.object_id = o.object_id
INNER JOIN
sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, 'DETAILED') s
ON
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
ON
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 ;
GO
|
Enjoy!!!
