Page density measures how full index pages are: low density means the same rows or entries occupy more pages, increasing the pages read during scans. It is also called internal fragmentation. The practical mistake is treating maximum density as automatically best; pages kept completely full leave little room for inserts and updates, raising the risk of costly page splits. section 11.12 section 11.1
Choose density as a workload trade-off, not a universal threshold. Data-warehouse-style workloads may suit pages 95% to 100% full, while busy transactional workloads may need 60% to 70% full to leave room for changes. SQL Server sets the leaf-level target with FILLFACTOR; PAD_INDEX can apply that setting to intermediate B-tree pages. section 11.1
Avoid these page-density maintenance mistakes:
- Do not reorganize an index expecting it to restore intentional free space. Reorganization condenses leaf pages up to FILLFACTOR, but does not lower already-full pages back to that target; rebuild instead when density needs resetting. section 11.3
- Do not average density across B-tree levels. A sparsely populated root page or small intermediate level can distort the result even when the large leaf level meets its target. section 11.4
- Do not rebuild every index. Filter maintenance by fragmentation, page count, page density, and the workload’s needs, because broad rebuilds consume substantial server resources. section 11.6
Diagnosing the ImpressionUID index
Inspect avg_page_space_used_in_percent together with index_level, page_count, and record_count. In one result, the leaf level had the target density across 3,832 pages, while a single low-density root page made the aggregated average look poor. Evaluate the leaf-level data in context before scheduling maintenance. section 11.4
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 level-by-level query to separate leaf-page density from small root and intermediate levels before acting on fragmentation statistics. section 11.4
