TalkToLee (1/7/2011)
The higher levels of the index are generally not large enough for fragmentation to matter, and they're far more likely to be in memory where fragmentation doesn't matter.
Make sense but how would you determine the threshold of 'large enough' a ratio?
From previous post:
Typically the aprox threshold where you start worrying about rebuilding is somewhere around 1000 pages
1000 pages is a rough estimate given by one of the people that wrote the storage engine code for the aprox size of an index level where fragmentation starts to have an effect on scan performance.
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