asked
Index maintenance for Low fragmentation but high page count
We have automated weekly Index maintenance to re-organize indexes with 5-30% fragmentation and Rebuild indexes with > 30% fragmentation (page count of 1000 +) However, what should be done for the indexes where fragmentation % is less, below 5%, but the page count is high (5000+)
I think it all depends on how your application responds to the index you are referring to. Some experts use 1000 as a high page count number these days, but it used to be 10000 and then 5000 in the past. This was a way to come up with exact numbers dbas could use. The idea behind defragging low (%fragmented) indexes is that it would not be worth the processing power to defrag anything below 5% because the gains would not be greater than the returns you could experience. To fully answer your question, I would test this out by seeing how the application behaves when the 5000+ page index, being 5% or below fragmented, and, when it is not (perform a rebuild or reorg on it). Then, you can come up with your own "thresholds" page and frag levels suitable for your specific environment.
18 People are following this question.
