Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Do people rebuild indexes if non-leaf levels exceed fragmentation threshold? Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 5:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
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!


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1379089
Posted Thursday, November 1, 2012 10:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
No comments?

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1379930
Posted Thursday, November 1, 2012 10:56 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 3,135, Visits: 11,480
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.


Post #1379932
Posted Thursday, November 1, 2012 11:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 42,820, Visits: 35,948
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 2008, MVP
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

Post #1379945
Posted Saturday, November 3, 2012 7:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
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).


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1380705
Posted Sunday, November 4, 2012 11:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 42,820, Visits: 35,948
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 2008, MVP
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

Post #1380811
Posted Sunday, November 4, 2012 1:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 36,983, Visits: 31,509
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1380822
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse