0

Go to this documentation page: https://docs.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.

0
2

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.

1
  • Thanks for the answer. I guess the way they phrased it is not very clear to me. I understand what limited and detailed scanned modes do, but reading the documentation made me more confused. I understood those two paragraphs I posted as the opposite. Thank you all for your input. Nov 9 '21 at 19:55
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.