sys.dm_db_index_physical_stats is a SQL Server dynamic management function (DMF) for inspecting the physical state of indexes. It returns a row for each B-tree level of every index in scope, including clustered and nonclustered indexes. Use it to measure external fragmentation and page density before choosing index maintenance—not as a reason to rebuild every index automatically. section 9.2 section 11.3
The function accepts database_id, object_id, index_id, partition_number, and mode, in that order. NULL broadens object, index, or partition scope; a NULL database ID can return details for all databases. LIMITED is fastest but examines root and intermediate B-tree levels only. SAMPLED examines 1% of pages, while DETAILED scans every index page; for tables under 10,000 pages, SAMPLED uses DETAILED. section 9.2 section 11.3
Read the main output fields in context:
- avg_fragmentation_in_percent reports external fragmentation: index pages that are out of sequence. section 9.12 section 11.3
- avg_page_space_used_in_percent reports page density, helping identify internal fragmentation and pages that are not full. section 9.2 section 11.3
- index_level identifies the B-tree level; level 0 is the leaf level. page_count and record_count provide scale for interpreting density results. section 11.3 section 11.4
Inspect an index without averaging away the evidence
A tempting query groups all levels of an index and averages page density. That can make a healthy rebuilt index look fragmented: a large, full leaf level may be combined with small intermediate and root levels that naturally have low density. Instead, return one row per level and inspect index_level, page_count, record_count, and page density together. For example, query the Marketing.Impressions table in DETAILED mode and join sys.indexes to limit results to the ImpressionUID index. section 11.4 section 11.3
Listing 11.5 Returning granular details with contextual information
SELECT
i.name AS IndexName
, ips.avg_page_space_used_in_percent
AS PageDensity #1
, ips.index_level #1
, ips.page_count #1
, ips.record_count #1
FROM sys.dm_db_index_physical_stats(
DB_ID('Marketing')
, OBJECT_ID('marketing.Impressions')
, NULL
, NULL
, 'DETAILED'
) ips
INNER JOIN sys.indexes i
ON ips.index_id = i.index_id
AND ips.object_id = i.object_id
AND i.name = 'ImpressionUID' ;
Use the granular query as a diagnostic pattern: retain B-tree level, page count, and record count instead of averaging page density across the whole index. section 11.4
A safer maintenance decision distinguishes the two measures. Reorganizing can reduce small amounts of external fragmentation and improve internal fragmentation lightly, but it condenses pages only up to FILLFACTOR and does not create the free space needed to reduce later page splits. Where lower page density is needed to avoid page splits, rebuilding may be the better choice. After shrinking a data file, check fragmentation and rebuild indexes as appropriate because moved pages can be out of order. section 9.2 section 11.3
