locked
Using sys.dm_db_index_physical_stats with Default Limited and index_level = 0 RRS feed

  • Question

  • Fellow DBAs

    I ran across an item in a refresher course about index internals.

    It would be safe to say that we all have used sys.dm_db_index_physical_stats.  In my reading, it appears that when LIMITED is used as the parameter (which is the default) it does not go to the leaf level (index_level 0)

    I see queries that are using Limited as the parameter and have index_level = 0 and alloc_unit_type_deesc = 'In_row_data'

    Why is index_level = 0 needed in the WHERE if the dmv is not hitting the leaf level.

    Thanks

    MG.

    Friday, August 7, 2020 7:09 PM

Answers

  • It would be safe to say that we all have used sys.dm_db_index_physical_stats.  In my reading, it appears that when LIMITED is used as the parameter (which is the default) it does not go to the leaf level (index_level 0)

    Don't what you have been reading, but Books Online says about the column index_level:

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

    And when I ran sample query with LIMITED, all I got was a single row with index_level = 0.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by mg101 Friday, August 7, 2020 9:49 PM
    Friday, August 7, 2020 9:13 PM

All replies

  • It would be safe to say that we all have used sys.dm_db_index_physical_stats.  In my reading, it appears that when LIMITED is used as the parameter (which is the default) it does not go to the leaf level (index_level 0)

    Don't what you have been reading, but Books Online says about the column index_level:

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

    And when I ran sample query with LIMITED, all I got was a single row with index_level = 0.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by mg101 Friday, August 7, 2020 9:49 PM
    Friday, August 7, 2020 9:13 PM
  • Thx Erland. So those that do this seem to be adding a condition that is not needed for limited.

    MG

    Friday, August 7, 2020 9:49 PM
  • Thx Erland. So those that do this seem to be adding a condition that is not needed for limited.

    Could be. I tend to use DETAILED myself, as I find the column avg_page_space_used_in_percent to be the most interesting, and you only get it with DETAILED.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 7, 2020 9:58 PM
  • Thx Erland. totally understand - page space used is a great thing to have. What I was going on was the interpretations that Limited in that DVM is documented by MS as:

    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.

    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

    With that definition, I could not see why index_level = 0 was needed in a WHERE clause. When you ran with LIMITED, it seemed that it showed only index_level 0 which seems opposite of what the above paragraph seems to indicate it does - not go to leaf page (level 0).

    MG


    • Edited by mg101 Saturday, August 8, 2020 1:20 AM wording
    Saturday, August 8, 2020 1:19 AM
  • With that definition, I could not see why index_level = 0 was needed in a WHERE clause. When you ran with LIMITED, it seemed that it showed only index_level 0 which seems opposite of what the above paragraph seems to indicate it does - not go to leaf page (level 0).

    It scans the upper levels, and data is displayed for index_level = 0, apparently. Probably because that is more interesting than having the upper levels displayed separately. After all, it is mainly the leaf pages you care about.

    As for the WHERE clause, I don't know why people put that it, but it cannot hurt. And if you change LIMITED to DETAILED, you already have the filter in place.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, August 8, 2020 7:50 AM
  • thx Erland. I totally agree.

    MG

    Saturday, August 8, 2020 4:51 PM
  • the full (DETAILED) index physical stats is just an expensive operation to run. A cheap alternative is to note the avg bytes per row after an index rebuild. when this values grows significantly (assuming no change in the true nature of the data) this is a reasonable sign of index fragmentation

    jchang

    Saturday, August 8, 2020 8:10 PM