SQLTeam.com | Weblogs | Forums

Index with 1000+ pages


#1

Do you need to rebuild indexes that are a 1000+ pages?


#2

Some say don't bother with indexes that have less than 1000 pages, some say it's 10000. The answer is "it depends".


#3

My preference is to reORANISE rather the reBUILD large indexes.

Like Tara my definition of "Large" varies over time ...


#4

Also the number of pages may be irrelevant, if fragmentation is low.


#5

Yes, if they are fragmented, at least for the vast majority of indexes.


#6

Some Indexes, with few pages, are always above our fragmentation cutoff (even immediately after being rebuilt) which begs the decision "Ignore them, they have two few pages to need a tidy index".

I take the viewpoint that there might be a marginal improvement from a small index being "tidy", and it takes nanoseconds and negligible LOG space to rebuild them, so mine get rebuilt. Of course they get rebuilt every night, because every night they are over the fragmentation threshold, but I have no reliable way of knowing if their fragmentation is real, or just "small index side effect". I'd hate for there to be a performance gain to be had, from rebuilding, and for them to be excluded because "You're too small to be worth rebuilding" ... so they get rebuilt :smile: