Take the 2-minute tour ×
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.

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. –  iValueValue 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? –  iValueValue Oct 23 '13 at 0:12

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.