Yet another fragmentation post, to answer a question asked in response to a previous answer....
SQL_Girl is still confused about DETAILED vs LIMITED, and I don't blame her. She reported that the BOL says: "The nonleaf levels of indexes are only processed when mode = DETAILED. " But I had said: "The second misunderstanding involves what LIMITED means. LIMITED does not ONLY show the leaf level, in fact, just the opposite. LIMITED does not evaluate pages at the leaf level at all."
So my answer actual was partly incorrect, and also partly just a confusion about what "processed" or "evaluated" means.
I was incorrect in implying that LIMITED gave you everything but the leaf. It only displays information for the leaf level. Thanks to SQL_Girl for keeping me honest here.
However, what exactly does "processed" mean? Most of the referenced post was about logical fragmentation as reported in the column avg_fragmentation_in_percent. Logical fragmentation is a measure of how 'out of order' the pages are. How can SQL Server determine whether the pages are out of order or not?
Below is a graphic that I use in my classes when discussing index structures. In the graphic, the leaf level has no logical fragmentation because the logical order, from values 1 to 80,000 is the same as the physical page numbers, 1151 to 2150. SQL Server can determine this without actually touching the leaf level at all. This information is available at Level 1. The index pages at level 1 have a pointer to every single page at the leaf level. Each index row at level 1 includes the first key value on a page at level 0, and then the file number and page number which start with that key value. When determining the value for avg_fragmentation_in_percent, SQL Server can just look at the index pages at level 1 and see the page numbers in order (all on file 1): 1151, 1152, 1153....2150. So it reports that at level 0 there is a 0 value for avg_fragmentation_in_percent (i.e. NO fragmentation). If there was fragmentation, SQL Server could determine that by looking at the level above the leaf. For LIMITED, that is basically all SQL Server does...it looks at the level above the leaf (level 1) to report whatever it can about the leaf level (level 0).
When I mentioned the term "processed" I was talking about all the other work that SQL Server does in DETAILED mode. It figures out the values for min, max and avg record size, ghost and forwarded record counts, and the value for avg_page_space_used_in_percent. It cannot get these values without actually examining every single page at the leaf level and in LIMITED mode it does not do that. It stays at the level above the leaf and does what it can from there.
So, to summarize:
In LIMITED mode, SQL Server scans only level 1 and reports the logical fragmentation of level 0.
In DETAILED mode, SQL Server scans every level, and reports on all kinds of stuff.
This is still just a summary, because there is all kinds of other information, such as when you have LOB and row-overflow data, etc. But that's for another time.
Have fun!
~Kalen