Check index density and fragmentation information for SQL Server 2005 (RPM)

Query the index density and fragmentation information for all tables in the database to provide a basis for rebuilding and collating the index, or refer to DBCC SHOWCONTIG, which is usually recommended for rebuilding over 30% fragmentatioin, otherwise it is recommended to defragment

SELECTI.name asIndexName,
O.name asTableName
S.name asSchemaName
F.index_type_desc asIndextype,
F.avg_page_space_used_in_percent asIndexdensity,
F.avg_fragmentation_in_percent asIndexfragmentation,
F.page_count asPages
fromSys.dm_db_index_physical_stats (db_id (),NULL,NULL,NULL, ‘SAMPLED') F
INNER JOINSys.objects o ono.object_id = f.object_id
INNER JOINSys.schemas s ono.schema_id = s.schema_id
INNER JOINSys.indexes I onf.object_id = i.object_id and f.index_id = i.index_id
WHEREPage_count > 50
andf.index_id > 0
ORDER byO.name, i.index_id

LIMITED | SAMPLED | Detailed | NULL | DEFAULT
These patterns affect how fragmented data is collected. Limited mode scans all pages of the heap, but for indexes, only the parent level page above the leaf level is scanned. Sampled collects 1% sample rate data in a heap or index. Detailed mode scans all pages (heap or index). Detailed is the slowest, but also the most accurate, option to perform. The effect of specifying NULL or default is the same as the limited mode.

Check index density and fragmentation information for SQL Server 2005 (RPM)