Sunday, January 20, 2008

TSQL to Determine Index Fragmentation Level

Viewing index fragmentation -

SELECT OBJECT_NAME(OBJECT_ID) AS Tablename
,sysindexes.name AS Indexname
,index_type_desc
,avg_fragmentation_in_percent
,page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv
INNER JOIN sysindexes ON dmv.OBJECT_ID = sysindexes.id
AND dmv.index_id = sysindexes.indid

No comments: