I've read different ideas to set FILLFACTOR and then monitor fragmentation over time (#1: 5 Things About Fillfactor, #2: Database Maintenance Best Practices Part II – Setting FILLFACTOR).

Instead of making an 'educated' guess upfront and then adjusting, I was wondering:

  • If anyone knew of a (quantitative) way to determine what level of fillfactor would be needed upfront?

(I'm primarly concerned with respect to NONCLUSTERED INDEXes, as I typically create my clustered indexes on ever-increasing integers.)

  • What are you trying to do? Merely avoid page splits in your non-clustered indexes? I'm not sure how that will be possible - if your data is getting updated then it doesn't really matter what the fill factor was initially. Oct 22 '13 at 1:35
  • My thinking was to delay "major" fragmentation, thereby reducing the frequency with which REBUILD/REORGANIZE are required. Also, when REBUILD/REORGANIZE are actually called, that these operations would be faster due to a lower level of fragmentation. Oct 22 '13 at 3:44
  • 1
    Any quantitative effort would rely on you having an accurate model of the pattern of inserts, updates and deletes that the index will encounter. Do you have this? Oct 22 '13 at 10:38
  • @MartinSmith, assume they are available, how could they be used to "size" the fillfactor? Oct 23 '13 at 0:12

No generalised quantitative method exists to be followed.

One would'e come up by now (1+ years since question), nor have i come across one on the internet searches.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.