THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Fragmentation Internals

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 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!


Published Thursday, March 6, 2008 2:42 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Ola Hallengren said:

>Yes, there can be logical fragmentation in the upper levels, which may need to be addressed in some very large tables.

>If you just want to see logical fragmentation to know if defrag or reorg might help, LIMITED should be fine.

1. As I understand it this means that if you have a very large table, you could have a real problem with logical fragmentation in the upper levels that will not be addressed if you're relying on sys.dm_db_index_physical_stats in LIMITED mode. The DETAILED mode is not an option in scenarios with very large tables, because of the leaf level scan and the time that takes.

>For LIMITED, that is basically all SQL Server looks at the level above the leaf (level 1) to report whatever it can about the leaf level (level 0)

2. Couldn't SQL Server do the same for the upper levels? SQL Server could look at level 2 to report about level 1 and so on. As I understand it this means that SQL Server could report logical fragmentation about all levels without scanning the leaf level. Is this a correct understanding?

Ola Hallengren

March 17, 2008 3:42 PM

aaaa said:

mt0417 mt0417

April 16, 2018 9:02 PM

chenyingying said:


May 6, 2018 10:45 PM

linying123 said:


May 10, 2018 8:08 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement