locked
Is the fill factor in SQL Database 70% or 80%? RRS feed

  • Question

  • In the Microsoft White Paper Database maintenance for Office SharePoint Server 2007 paper, on page 11, Microsoft recommends setting SharePoint database Fill Factor to 70%.

    But if you go into a SharePoint 2007 with SP2 SQL database, go to the actual SharePoint Database Statistics Timer Job, locate the corresponding Stored Procedure called proc_DefragmentIndices, you will find a line that looks like this:

    REBUILD WITH FILLFACTOR = 80

    This means that whenever this SP determines an Index should be Rebuilt, it sets the Fill Factor to 80%, rather than the recommeded 70%.

    Why does Microsoft recommend 70% Fill Factor, but actually use 80% in the built-in Stored Procedure?


    • Edited by Mike Walsh FIN Thursday, May 26, 2011 10:11 AM Title made into a single sentence by re-location. Reference to other *completely different apart from the paper quoted* thread removed.
    Wednesday, May 25, 2011 7:39 PM

All replies

  • Hi,

     

    According to your description, if your table has a lot of writes, you should use a lower fill factor. 80% might work for you. If you do not specify the fill factor, then it is 100% by default. This could a lot of page splits if there is a lot of IO. Don’t set the fill factor too low though, because otherwise the query will have to read more pages. If your table is mostly reads, then 100% fill factor would be better. It all depends on how much IO your table has.

     

    Best Regards
    David Hu

     

    • Marked as answer by Emir Liu Thursday, June 2, 2011 1:34 AM
    • Unmarked as answer by jpSQLDude Thursday, June 2, 2011 2:35 AM
    Friday, May 27, 2011 1:40 AM
  • Hi,

     

    According to your description, if ....

     

    Best Regards
    David Hu

     

    I was just directly quoting Microsoft's recommendations in that official White Paper, and comparing that to what Microsoft actually does in the Stored Procedure that ships with SharePoint.

    I was just trying to figure out what Microsoft really recommends, since these two seem to be in conflict!

    Thursday, June 2, 2011 6:52 PM
  • jpSQLDude,

    That's a good catch man! We are on SharePoint  2010 and the proc rebuilds with 80% too. That coincides with what our Microsoft Field Rep stated when he came out to see us. As with any performance setting ti ultimately depends on the results you get in your environment. 70 or 800 is definitely better than 100% fill!

    Monitor regularly and adjust accordingly.

    Cheers,

    Rich


    Love them all...regardless. - Buddha
    Wednesday, December 21, 2011 5:50 PM