June 12, 2012 at 12:27 pm
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
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).
June 12, 2012 at 1:18 pm
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
Maybe take a read through these:
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply