InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 22:37:18 GMT20http://www.sqlservercentral.com/Forums/Topic1379089-1550-1.aspxExtremely 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.Sun, 04 Nov 2012 13:18:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1379089-1550-1.aspxHonestly, I probably wouldn't bother in most cases, because of the second part of what I said.[quote]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.[/quote]Sun, 04 Nov 2012 11:52:19 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1379089-1550-1.aspxThanks 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).Sat, 03 Nov 2012 07:16:28 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic1379089-1550-1.aspx[quote][b]Michael Valentine Jones (11/1/2012)[/b][hr]Or just leave it alone.[/quote]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.Thu, 01 Nov 2012 11:18:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1379089-1550-1.aspxIf 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.Thu, 01 Nov 2012 10:56:19 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1379089-1550-1.aspxNo comments? :crying:Thu, 01 Nov 2012 10:47:49 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic1379089-1550-1.aspxI 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!Tue, 30 Oct 2012 17:19:10 GMTMarios Philippopoulos