Friday, October 14, 2011

Query to find Avg Fragmentation In percent

DECLARE @dbname VARCHAR(64)
SET @dbname = 'SunlightTest'
SELECT A.object_id
, A.index_id
, [name]
, Avg_Fragmentation_In_Percent
FROM sys.dm_db_index_physical_stats (db_id(@dbname),NULL,NULL, NULL, NULL) AS A
JOIN sys.indexes B WITH(NOLOCK) ON A.Object_id = B.Object_id
AND A.Index_id = B.Index_id
WHERE Avg_Fragmentation_In_Percent >= 60
AND B.[name] IS NOT NULL

No comments: