Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page Allocations. I really don't know what it means, or if one is better than the other.

If someone could give a simple definition of each, and also explain why Leaf or Non-leaf matters, it would be appreciated!

share|improve this question

migrated from stackoverflow.com Mar 16 '13 at 0:11

This question came from our site for professional and enthusiast programmers.

up vote 13 down vote accepted

Think about it like this: leaf level pages are the end of the road for the data search through the B-tree structure of an index. They contain the data defined in the index definition (or in the case of a clustered index, all of the table's data) and a row locator to the data row (in the case of a nonclustered index).

Non-leaf level pages contain the "road map" to the leaf level pages/data by including the key value as well as a pointer to either another non-leaf level page (depending on the B-tree depth and location of the intermediate page) or the resulting leaf level page (index page for a nonclustered index, and data page for a clustered index).

Edit: Here's a good image to put a picture to the idea.

enter image description here

share|improve this answer
    
My understanding now, is that an non-leaf request will just result in a pointer to another page. Eventually that page will be the leaf page with the actual data on it. I would assume with efficient indexes (tailored to your more frequent queries) would contain very few non-leaf steps to the leaf level pages. – meltdownmonk Mar 21 '13 at 16:50
    
That all depends on how large the data is in the index, as well as the key columns for the index. Take a look at the image I just edited into my answer. Hopefully that'll help visualize this B-tree structure easier. – Thomas Stringer Mar 21 '13 at 17:08
    
Good answer, but you should link to your sources (especially when copying images): technet.microsoft.com/en-us/library/… – MikeTeeVee Jan 20 '14 at 3:26
  • leaf = In a clustered index, where the all the row data data is. In a non-clustered index, the columns making up the index, plus any included columns and the clustered index key (if table has a clustered index)
  • non-leaf = internal index node (contains only the column data that is part of the index)

Every index is comprised of a single root node, possibly several layers of intermediate internal tree nodes and a single layer of leaf nodes.

In a clustered index, the leaf nodes are effectively the table. In a non-clustered index, the leaf nodes contain column data, any included columns and the clustered index keys.

SQL Server Index Basics

share|improve this answer
    
I thought (perhaps wrongly) that tracking of leaf/non-leaf applied to non-clustered indexes also? Your bullet points read as applicable to clustered indexes only. – Mark Storey-Smith Mar 16 '13 at 0:59
    
will make clearer... – Mitch Wheat Mar 16 '13 at 1:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.