Rebuild/Reorg Indexes (meaning of index_level in sys.dm_db_index_physical_stats)

  • Hello,

    I have been capturing index fragmentation statistics for one of our databases for few weeks now. I wanted to see how indexes are fragmented over time. I put the results of sys.dm_db_index_physical_stats in one table and started analyzing. For E.g.

    Hello

    object_name index_id avg_fragmentation_in_percent index_type_desc index_levelavg_fragment_size_in_pagespage_count

    SMMTRANSLOG 1 6.58 CLUSTERED INDEX 0 5.60 24145

    SMMTRANSLOG 1 90.3 CLUSTERED INDEX 1 1 2331

    Some of the indexes have multiple index_levels and avg_fragmentation_in_percent for such indexes is very large. Rebuilding of such indexes only seems to reduce the fragmentation of the 0 level index even when the page count for level 1(and more) indexes is over 1024.

    Hence my questions are:

    1. Why the rebuilding of the indexes does not lower the avg_fragmentation for level 1 (and more) indexes?

    2. Should I be worried about high fragmentation of such indexes?

    The MSDN article (http://msdn.microsoft.com/en-us/library/ms188917(v=sql.105).aspx/html) states that

    Index_Level is

    "Current level of the index.

    0 for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

    Greater than 0 for nonleaf index levels. index_level will be the highest at the root level of an index.

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

    Any help would be much appreciated.

    Thank you.

  • The upper levels of the indexs are not scanned, nor are they always full. No, you should not be worried about fragmentation in these levels as long as leaf level is good.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Thank you for the quick response.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply