Wednesday, July 14, 2010

Detect Fragmentation SQL Server 2005

SELECT AS databaseName
, ps.OBJECT_ID AS objectID
, ps.index_id AS indexID
, ps.partition_number AS partitionNumber
, ps.avg_fragmentation_in_percent AS fragmentation
, ps.page_count
FROM sys.databases db
INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps
ON db.database_id = ps.database_id
WHERE ps.index_id > 0 
AND ps.page_count > 100 
AND ps.avg_fragmentation_in_percent > 30
OPTION (MaxDop 1);

The above script tries to find fragmented indexes in ALL the databases. If you want to run this against a specific database, replace ps.database_id with the database id from sys.databases