THE SQL Server Blog Spot on the Web

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

Kalen Delaney

Did You Know? Fragmentation, revisited....


A couple of questions arrived regarding my earlier post about fragmentation on the system tables, and since the questions really don't have anything to do with system tables, I felt they deserved a new post.

1) Using the DETAILED option includes the non-zero index-levels as well (non-leaf pages).  According to many source, this should also be actioned, seeing that this indicates "logical" fragmentation (meaning that the leaf pages are not in the order of the non-leaf keys any more).  I cannot find anything conclusive on this.  Where as the index level=0 occurrences, indicates extent fragmentation.  We are using this dmv, to check fragmentation-levels first, and based on that, do the necessary re-index/reorg.  Currently we are using the LIMITED option (which only shows leaf-level), but we seem to be missing indexes that needs to be re-indexed/re-orged.

There are two  misunderstandings reflected in this question. Although there are several types of fragmentation, the value in the avg_fragmentation_in_percent represents only logical fragmentation, in any level of any index. Yes, there can be logical fragmentation in the upper levels, which may need to be addressed in some very large tables. Extent fragmentation is only reported for heaps, and once you have a clustered index, the table itself is just the leaf level of the index.  So basically, there is no difference between the meaning of the avg_fragmentation_in_percent value no matter what level you are at. However, fragmentation is really only an issue if there are LOTS of pages. I usually say that if there are less than 100 pages, you should never worry about fragmentation. However, this this whitepaper suggests that a better cutoff would actually be 1000 pages:

It would need to be a very big table to have upper levels of indexes with more than 1000 pages, and the sys.dm_db_index_physical_stats view does show you the number of pages at each level.

The second misunderstanding involves what LIMITED means. LIMITED does not ONLY show leaf level, in fact, just the opposite. LIMITED does not evaluate pages at the leaf level at all. It can still determine the logical fragmentation because of the pointers in the upper levels, but LIMITED will return no values that require actually examining the leaf level rows. This includes values like min, max and avg record size, ghost and forwarded records counts, and the value for avg_page_space_used_in_percent. If you just want to see logical fragmentation to know if defrag or reorg might help, LIMITED should be fine.

2)   Secondly, the stats that this function return, does not seem to be reliable.  I ran the function prior to an ALTER…. REBUILD, and then again thereafter.  There were hardly any differences in the stats.  Is it now a case of the stats being unreliable, or the REBUILD not being effective?

REBUILD doesn't seem to make a difference in the avg_fragmentation_in_percent value is when there are too few pages in the table.

Published Thursday, February 28, 2008 6:00 PM by Kalen Delaney
Filed under: , ,

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



John said:

How do you define "too few pages" in the last paragraph

March 4, 2008 1:58 PM

SQL_Girl said:

Thanks for your explanation Kalen.  It is difficult to find conclusive information on this level of detail - and I desperately want to understand this.      

"The nonleaf levels of indexes are only processed when mode = DETAILED. "

The above-mentioned comes from BOL (sys.dm_db_index_physical_stats under index_level).  This is what confused me.

Also, when I run the "LIMITED" and "DETAILED" options irrespectively on quite a sizable table, the LIMITED definitely only gives me index-level 0:

Level Frag % Page Count Page space % Record Count

0 4.41 173722 85.33 16669566

1 42.09 733 84.89 173723

2 95.2 21 12.48 733

3 0 1 7.49 21

0 8.82 97557 82.30 16669572

1 58.6 696 77.90 97557

2 91.6 24 16.09 696

3 0 1 13.31 24

Only line 1 and line 5 gets picked up with “LIMITED”, therefore this table will not be re-indexed.  Though, we have found that re-indexing resulted in performance improvements.  

Should/can one fully rely on “conditional” re-indexing, or should you still try and do a “full” re-index once a week for example?  

March 5, 2008 5:00 AM

Kalen Delaney said:

Hi John

Did you read the answer to number 1? I usually think that too few pages means under 100 pages or thereabouts.


March 6, 2008 4:04 PM

Kalen Delaney said:

Yet another fragmentation post, to answer a question asked in response to a previous answer .... SQL_Girl

March 6, 2008 4:42 PM

Kalen Delaney said:

Yet another fragmentation post, to answer a question asked in response to a previous answer .... SQL_Girl

March 8, 2008 11:23 AM

JK said:


From this DMV sys.dm_db_index_physical_stats how can I differentiate Index Page Count and Table Page Count.


August 28, 2008 7:21 PM

king said:



January 16, 2018 11:30 PM

aaaa said:

mt0417 mt0417

April 16, 2018 8:41 PM

linying123 said:


May 10, 2018 8:09 PM

shenyuhang said:


June 1, 2018 7:05 PM

dongdong8 said:



June 29, 2018 2:50 AM

linying123 said:



July 16, 2018 8:35 PM

dongdong8 said:



July 23, 2018 11:31 PM

shenyuhang said:


July 23, 2018 11:39 PM

cara meningkatkan nafsu makan said:

August 3, 2018 11:45 PM

yaoxuemei said:


August 15, 2018 2:08 AM

qqq said:

August 16, 2018 12:19 AM

qqq said:

August 16, 2018 12:22 AM

chenjinyan said:


August 22, 2018 11:07 PM

shenyuhang said:


August 23, 2018 10:20 PM

obat bronkitis anak secara alami said:

August 24, 2018 7:16 PM

obat herbal kolesterol terbaik said:

August 24, 2018 11:06 PM

obat benjolan said:

September 4, 2018 12:40 AM

obat wasir said:

September 4, 2018 8:35 PM

obat katarak said:

September 17, 2018 1:57 AM

obat maag said:

September 18, 2018 7:38 PM

xiaojun said:

20180928 junda

September 27, 2018 10:57 PM

kakakaoo said:

October 8, 2018 2:19 AM

kakakaoo said:


November 8, 2018 2:03 AM

kakakaoo said:


November 8, 2018 2:04 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement