Index fragmentation reporting 'LIMITED': only root level?

Question

Index fragmentation reporting 'LIMITED': only root level?

muzak

Valued Member

Points: 50
More actions
December 24, 2010 at 3:57 am

#232293

Dear all,
I'm having quite a strange problem at a customer. Maybe this is common knowledge but I haven't found a specific problem description and solution for this one.
We are using the automatic index maintenance script from Michelle Ufford (http://sqlfool.com/2010/04/index-defrag-script-v4-0/). I guess some of you will know this script: it basically follows the general Microsoft index maintenance rule of thumb
<5% average fragmentation -> do nothing
>5 > 30% -> index reorg
> 30% -> index rebuild
I haven't have checked all the tables but on one of the biggest ones I saw a significant failure in this approach. The script uses the well known DMV sys.dm_db_index_physical_stats in order to gather statistics. Of course the default scan mode is LIMITED.
However: LIMITED seems to show only the fragmentation of the root level. In this table we had an 'avg_fragmentation_in_percent' of ~5% reported (only the root level is shown). This is also reported when using the GUI (index properties -> fragmentation).
The interesting thing is: when you use the DETAILED mode you see all index levels. And you see also that the other index levels had 98% fragmentation.
After manual rebuild everything (all levels) were again on %0. And the queries were again fast.
So my general question is this: did I do something wrong or are my assumptions wrong?
If my assumptions are correct that LIMITED reports wrong frgamentation then I can never have an automated index maintenance script (DETAILED scan is not feasible since it usually takes considerably longer).
I experienced the same behaviour in SQL Server 2008 as well.
Please let me know
Thank you!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Limited shows only the fragmentation at the leaf level, not the root. The root (by definition) has only one page. It's not showing incorrect values, it's just not showing the non-leaf levels (as per documentation)

Generally the fragmentation of the non-leaf levels is not a concern. They're much smaller than the leaf level and are usually in memory for an active table. so fragmentation has no effect.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

muzak Valued Member Points: 50 More actions · Answer 2

Thank you GillaMonster for clarifying things. I didn´t know what exactly 'limited' was showing. Should have more doing RTFMing I guess 🙂

Actually the documentation states that 'limited' scans parent levels for an index:

http://msdn.microsoft.com/en-us/library/ms188917(v=SQL.90).aspx:

The LIMITED mode is the fastest and scans the smallest number of pages. It scans all pages for a heap, but only the parent-level pages for an index, which are the pages above the leaf-level.

Anyway my concern however still is that after rebuilding this table (8 mio records) (with reported 5% fragmentation) the query took 3 hours instead of 8+. There is a lot of others things involved in the query processing but this table plays a central role.

So my question is: how can I be sure, that reported fragmentation levels are ok. Should I just rely on that info?

Thank you!

br

Muzak

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

muzak (12/24/2010)
Actually the documentation states that 'limited' scans parent levels for an index:

I never claimed otherwise.

Limited mode scans the level above the leaf because that's all it needs to do to get the page numbers (and hence to calculate fragmentation) of the leaf level. That's why several of the columns returned are null. It only reports on the leaf (which is what I suspect you're 'correcting' me on)

In summary: Scans the level above the leaf (because the leaf level is the largest). Shows the fragmentation of only the leaf.

Yes, the information is accurate. Are you sure that fragmentation was indeed the cause of the slow query? Not one of the many other things that rebuilding can affect (statistics, cached query plan)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass