Post reply

Fill Factor Calculation

  • December 21, 2009 at 9:22 am

    #136867

    Hi All,

    Generally for OLTP apps where the insert\update(modification) to the tables are less, fill factor can be optimally set as "100" to avoid 'Page Splits',however what will be the calculation\thinkable points to set an ideal 'fill factor' where the insert\update\delete requests are more.

    Do we have any formula\ significant way to calculate the fill factor?

    Pls assist..

    Thanks.

  • December 21, 2009 at 9:29 am

    #1094866

    fill factor depends on the flavor of inserts, so it's not something that can use a rule of thumb/assumption;

    for example, if the primary key of a table is date or input-entered order. so all new entries for an identity column or date get added to the end of the table, a fill factor of 100% is fine.

    but if your PK is a combination of lastname,firstname, then quite often you might add a new name "Smith" in the middle; in that case leaving room for new values with fill factor makes sense.

    the volumn of inserts helps you decide how much room to leave....very few inserts= a higher percentage, say 90 or 95;, tons of inserts might be closer to 50%.

    inserting details related to an order number, same as above...depends on the PK and how much gets entered.

    why don't you show us an example of the table you are looking at changing the fill factor for, and we might offer some suggestions.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 21, 2009 at 9:28 pm

    #1095237

    Don't forget about UPDATE's either. Having a tightly packed table with a FILLFACTOR of 100 on an IDENTITY column is great until you update a VARCHAR column in the logical "middle" of the table to be bigger than it's original size... then "BOOM!"... page split.

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