question

hema.mutreja avatar image
hema.mutreja 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+)
indexingcountfragmentationpage
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What are the index PAD_INDEX and FILL_FACTOR settings?
0 Likes 0 ·

1 Answer

· Write an Answer
rvsc49 avatar image
rvsc49 answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.