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 understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95?

Is there something I'm missing?

share|improve this question
add comment

1 Answer

up vote 8 down vote accepted

Just throwing out a few suggestions here as to why Microsoft would do this:

  1. They have geared their default to be best possible OLAP performance (less page reads with a fill factor of 0/100)
  2. They are assuming that INSERTed data will most likely be at the end of the table, making the extra space per page useless
  3. They are assuming that typically UPDATEd data will not lengthen row data very often, causing a page split

This is just guessing here. The only people that can really answer that question accurately is the SQL Server team themselves.

share|improve this answer
4  
Right, these seem like sensible theories, and are far more likely to be appropriate for the typical customer than picking some arbitrary fill factor where, in a lot of cases, you're guaranteed to permanently waste space on a page. –  Aaron Bertrand Mar 29 '12 at 13:43
    
I can't believe I got an answer that I can agree with. I didn't expect there existed a reasonable answer but your #2 and #3 points are very sensible. Thanks! –  NTDLS Mar 29 '12 at 15:01
    
@NTDLS no problem, glad to help. –  Thomas Stringer Mar 29 '12 at 22:40
add comment

Your Answer

 
discard

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.