Post reply

Cluster index-index_depth

  • June 12, 2012 at 12:27 pm

    #392500

    I always thought clustered index are at root level(they directly refer data)....but recently when i was trying to query sys.dm_db_index_physical_stats i found one of the clustered index has index_depth of 4....Not sure how an index depth is defined??

  • June 12, 2012 at 1:07 pm

    #1499996

    The leaf levels of the clustered index contain the data itself, but the tree construction of the index levels above that is similar to the non-clustered index (my understanding, others can jump in if I'm off the beam here).

    And then again, I might be wrong ...
    David Webb

  • June 12, 2012 at 1:18 pm

    #1500005

    Hi Pradeep -

    The root level is part of an index, not a place where an index is located. The root level is the single index page at the top of the index structure. It is farthest from the data pages and does not refer to the data pages. It does refer to the intermediate level index pages. The bottom-most intermediate level index pages refer to the leaf level index pages which, in a clustered index, are actually the data pages of the table. A good explanation and illustration can be found here:

    http://msdn.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

    Contrast this with the architecture of non-clustered indexes, where the index leaf level is a layer that is separate from and directly above the data pages so that the leaf level pages are referring directly to rows on the data pages:

    http://msdn.microsoft.com/en-us/library/ms177484(v=sql.105)

    Index_depth is defined as the number of index levels - go to this link and search text for "index_depth":

    http://msdn.microsoft.com/en-us/library/ms188917.aspx

    So an index_depth of 4 for an index (clustered or not) is quite typical.

    Hope this helps. Let me know if further explanation is needed.

    - victor di leo

  • June 12, 2012 at 1:18 pm

    #1500006

    Maybe take a read through these:

    http://qa.sqlservercentral.com/articles/Indexing/68439/

    http://qa.sqlservercentral.com/articles/Indexing/68563/

    http://qa.sqlservercentral.com/articles/Indexing/68636/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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