Index fragmentation query – sql server

what is the level of fragmentation on my indexes?

SELECT TOP 50 s.NAME SchemaName
, AS TableIndexName
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) p --'DETAILED'
INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
inner join sys.tables t on i.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id

WHERE p.avg_fragmentation_in_percent > 1
AND s.NAME ='mySchema'
ORDER BY avg_fragmentation_in_percent desc

Tags: ,

Friday, March 2nd, 2012 SQL Server No Comments