Post reply

Setting Fill Factor wrt Performance

  • July 17, 2008 at 5:14 am

    #125004

    For improving performance, i would like to get information about Fill Factor setttings based on following real scenario.

    We have a users profile Table which has NTLogin and Domain columns whos combination make a record unique (Composite key as Primary key with FillFactor of 90%). Right now we have about 3,85,000 records in this table and we have concern abut performance when adding a new user or searching for specific users data; queries takes long time. We are planning to open doors for other domain users that may come in add their information in this table. which is likely to go for 6,00,000 users information.

    My question is, Is the FillFactor 90% is absolutely correct or we may require to set it to 50%? The reason is NTLogin is varchar and the Table index has to insert rows sequentially from A-Z. if a page is flilled and a new INSERT that starts with A then page split may occur? am I correct or NOT? setting to fillFactor to 50% may keep space on pages and page split does not occur frequently. Is my understanding corrent?

    but following links says something about FillFactor should be 70%.

    http://msdn.microsoft.com/en-us/library/aa933139(SQL.80).aspx

    http://msdn.microsoft.com/en-us/library/aa196711(SQL.80).aspx

    Shamshad Ali.

  • July 17, 2008 at 6:57 am

    #844345

    The answer is... it depends.

    You are absolutely correct in saying that what you're trying to do with a lower fill factor is avoid page splits.

    The reason why the answer is ... it depends, is because you will need to work out how many rows will fit on a page, how many rows you are adding between re-indexes, and how the new rows will be added (e.g. if all new rows are added at the end of the table, then a low fill factor is pointless).

    e.g. if you can fit 100 rows on a page, and you are adding 20 rows to the page between re-indexes, then an 80% fill factor is ideal. If you are adding 50 rows, then a 50% fill factor is more appropriate.

  • July 17, 2008 at 7:51 pm

    #844742

    Generally speaking, if there aren't a lot of inserts and the clustered key puts the inserts near the logical end of the table, you can use a high fill factor like 90% and it will help your SELECTs.

    If there are a lot of inserts and the clustered key puts the inserts near the logical end of the table, then you can still have a high fill factor.

    If there are a lot of inserts and the clustered key does not put the insers near the logical end of the table, the a relatively low fill factor is in order. You'll need to experiment with it based on the number of inserts and how often you rebuild the index to prevent page splits.

    If you have a Static table (lookup, definition, or reference table), then you can use a fill factor of 0 or 100%. Zero obviously means pretty much the same as 100%. There used to be a slight difference but I don't think the difference exists anymore.

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