November 7, 2006 at 12:36 pm
Hello Everyone!
I was wondering which variables (selectivity, key lengths, number of datapages consumed by the underlying table, etc.) determine both the "depth" of the branch nodes (number of levels between the root node and the leaf nodes) and the "width" of the branch nodes and leaf nodes for a given index? Also, is there a maximum number of levels of branch nodes at which point the engine will just "spread out" the leaf nodes?
Thanks!
Allen
November 8, 2006 at 1:12 pm
I don't know the answer to what you are asking, but in the old days, it all depended on the size of the fields in the index and the page reference( # of bytes). Then how many of those fit on an index page (less any buffer space defined by fill factor.) From that you could do the math to find out the depth/width.
Again in the old days, for tables with an irregular pattern of inserting they used to recommend reindexing at certain intervals.
November 10, 2006 at 5:53 am
I'd suggest a read through articles/books by Kalen Delaney or Kimberley Tripp. There's also some whitepapers in technet which cover this. Most secondary indexes are unlikely to have more than 3 "layers" - obviously depends upon the key width. selecting all the data in a secondary index will show the number of pages scanned in i/o stats - work out how many values in the leaf , work out pages subtract from total in scan - work backwards to create the tree.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply