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. – Aaron Bertrand 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. – Adrian Torrie 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? – Martin Smith Oct 22 '13 at 10:38
  • @MartinSmith, assume they are available, how could they be used to "size" the fillfactor? – Adrian Torrie Oct 23 '13 at 0:12
up vote 0 down vote accepted

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

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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