I think SQL Docs are wrong for sys.dm_db_index_physical_stats

Question

Go to this documentation page: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15

Then go to the index_level column explanation:

The nonleaf levels of indexes are only processed when mode = DETAILED.

Now go to the Scanning Modes section:

The LIMITED mode is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. For a heap, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode.

With LIMITED mode, compressed_page_count is NULL because the Database Engine only scans non-leaf pages of the B-tree and the IAM and PFS pages of the heap.

The scanning modes section is wrong, right? Limited mode only scans leaf pages. Let me know your thoughts. I wanted to check with the community before posting it to the proper channels.

Answer 1

The docs are correct in my opinion. LIMITED mode uses the same mechanism as readahead. It makes use of the fact that all parent-level/non-leaf-level pages contain a list of (pointers to) all pages at the leaf-level in key order. That's why it can use those page IDs to figure out whether the pages are in the same logical order and physical order. By the way, all other scan modes also perform a LIMITED mode scan first to find the logical fragmentation.

Answer 2

No, all works as expected.

In LIMITED mode parent-level pages are scanned and you get one row with index_level = 0.

In DETAILED mode all pages are scanned and you get several (usually 3 or 4) rows with different values for index_level, followed info about pages on that particular level. - Denis Rubashkin