The leaf level is the lowest level of an index B-tree. In a clustered index, it contains the table’s actual data pages, ordered by the clustered key; in a nonclustered index, it contains pointers to pages in the heap or clustered index. section 11.0 section 11.12
Mistake #1: treating every B-tree level as equally important.
- Focus fragmentation analysis on level 0, which is always the leaf level, rather than averaging page density across root, intermediate, and leaf levels. section 11.4
- Remember that a low-density root page may be harmless: a single sparsely populated page can distort averages across the whole index. section 11.4
- Do not maintain indexes merely because aggregated statistics look poor; misleading averages can cause unnecessary maintenance or neglected maintenance. section 11.4
The ImpressionUID diagnostic
A detailed inspection of the ImpressionUID index showed 3,832 leaf pages at 99% density, while the intermediate and root levels had fewer pages and lower densities. Aggregating all levels suggested 66% density, but the meaningful leaf-level average was 99%. 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 detailed diagnostic query to return page density, index level, page count, and record count for each level of a named index instead of relying on double-aggregated averages. section 11.4
