Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.)

share|improve this question
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. – futureSPQR Oct 22 '13 at 3:44
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? – futureSPQR 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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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