Do people rebuild indexes if non-leaf levels exceed fragmentation threshold?

Question

Do people rebuild indexes if non-leaf levels exceed fragmentation threshold?

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
October 30, 2012 at 5:19 pm

#284331

I recently realized that our nightly index-maintenance process rebuilds indexes if the avg. fragmentation is higher than 30% for *any* level in the index, provided the number of pages on that level is over 100. That means a large index will be marked for a rebuild, even if the leaf level is not fragmented, as long as one or more intermediate levels exceed the 30% threshold.
Given that intermediate index levels are much smaller in size than the leaf level, does it make sense to do an index rebuild in these cases? These rebuilds can be very costly and may even disrupt business operations.
I'm curious to know what people do in this case. Do people commonly rebuild indexes if any level exceeds the frag threshold? Or focus only on the fragmentation of the leaf level?
Please share!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

No comments? :crying:

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

If the leaf level fragmentaion is low, it would probably be much faster to do an index reorganize(defrag), instead of a rebuild.

Or just leave it alone.

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

Michael Valentine Jones (11/1/2012)
Or just leave it alone.

This.

For fragmentation of a non-leaf level to be of any impact, that level needs to be > ~1000 pages and for SQL to be doing such large range scans of the table that it needs to issue read-aheads for the non-leaf levels.

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

Thanks both!

I think I will incorporate this (>1,000 pages for intermediate levels) as a criterion for rebuilding indexes with avg frag > 30% (at the intermediate level in question).

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Honestly, I probably wouldn't bother in most cases, because of the second part of what I said.

and for SQL to be doing such large range scans of the table that it needs to issue read-aheads for the non-leaf levels.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Extremely large tables containing some sort of temporal column (like a transaction date) can easily be partitioned by month (for example) for the purpose of not having to reindex old data over and over again. With such partitioning, all of this might be less of a problem than you thought. For example, it would almost guarantee that the BTREE level of the indexes would never grow large enough to have to worry about fragmentation there (as Gail pointed out).

As a side bar and depending on when updates stop happening (if they're updated at all) to a month of data in a partitioned table (or view), I'll typically do a full rebuild (doesn't take much time because it's relatively small compared to the rest of the table) of all the indexes for that month and they almost never become fragged enough (because of no updates) to even consider after that. The final unqualified full rebuild is just to save as much disk space as possible.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)