Post reply

Fill Factor for OLTP database

  • January 31, 2019 at 4:55 pm

    #370390

    I am setting up a new OLTP database and expect to write to the database about 70% of the time and read from it around 30% of the time.

    I'm wondering what would be good practice in setting the default fill factor for this database initially as well as other best practices I may want to look into applying given this criteria and maybe how to measure and adjust this later down the road.

    Any input would be greatly appreciated.

    Thanks

  • February 1, 2019 at 2:37 am

    #2020825

    Hi,
    it depends! No one knows your workload, or the size of your database, or that  kind of used queries.

    Do you mean the index fill factor? Start with 95% and change  it to 90%, and  measure the runtime of your queries.

    But, I think, this would be a very good setup for AOAG, the primary take the input, and the secondary take the read only queries, or?

    Kind regards,
    Andreas

  • February 3, 2019 at 8:31 pm

    #2020993

    equipe9 - Thursday, January 31, 2019 4:55 PM

    I am setting up a new OLTP database and expect to write to the database about 70% of the time and read from it around 30% of the time.

    I'm wondering what would be good practice in setting the default fill factor for this database initially as well as other best practices I may want to look into applying given this criteria and maybe how to measure and adjust this later down the road.

    Any input would be greatly appreciated.

    Thanks

    You don't write to the database.  You write to tables.

    Within the tables, there are different types of indexes and that's where you set the Fill Factor.  If you have "ExpAnsive Updates" or "Out of Order" INSERTs that affect those indexes, you'll have severe problems with logical fragmentation and page density.  If you have "Sequential Inserts" into multiple silos, you have huge logical fragmentation with virtually maximum page density.  If you have "Append Only" indexes that suffer no "ExpAnsive Updates", you have virtually no logical fragmentation and always the maximum page density.  Adding a FILL FACTOR of anything less than 100 to the latter would be a waste of memory and disk space.  Adding a Fill Factor to an "Append Only" index that suffers "ExpAnsive Updates" will not prevent wanton fragmentation. 

    Every index has its own "DNA Signature" thanks to INSERT patterns.  You can change that "DNA" by getting rid of "ExpAnsive Updates" and a couple of other tricks to prevent Page Splits, which is the PRIMARY cause of both logical fragmentation and low page densities as well as a ton of blocking most people aren't even aware of and a ton of unnecessary log file activity that falls into that same category.

    Changing the default FILL FACTOR on a database (affects only indexes with unassigned FILL FACTORS) makes no sense because there are 6 different INSERT patterns (and one of those has two different categories) and, unless you actually correctly analyze an index and assign the correct FILL FACTOR based on the INSERT pattern and correctly resolve "ExpAnsive Updates", assigning a FILL FACTOR may cause more harm than good... which also goes for Index Maintenance, itself.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply