Index fragmentation query – sql server

what is the level of fragmentation on my indexes?

SELECT TOP 50 s.NAME SchemaName
,t.name TABLENAME
,i.name AS TableIndexName
,i.[type_desc]
,p.avg_fragmentation_in_percent
,i.[fill_factor]
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 yet.

Leave a comment

You must be logged in to post a comment.