The FILL FACTOR

  • Comments posted to this topic are about the item The FILL FACTOR

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • What's the consequences if wrong fill factor has been set. So called poor performance will cause something like deadlock or query timeout?

  • Brian - Just wanted to mention how much I enjoyed the article. It's very well written and is a thorough introduction to the not-so-simple concept of how to set a FF to best impact your individual environment.

    Keep up the great work...Troy (The Grateful DBA)

  • Great article - I've always worried about Fill Factor as it does seem to be an area shrouded in confusion and contrasting views.

    For example, if you're always adding to the end of a table/index, e.g. an increasing Order Number, I was told fill factor should be set to zero; this makes sense to me, but wondered if anyone agreed / disagreed, or had any other useful nuggets to do with Fill Factor? Perhaps we could start a Fill Factor FAQ?

    Does anyone have any examples of where they have made changes to Fill Factor for a large table and seen noticeable performance improvements?

  • Good article. Nice explanation of how to evaluate read operations vs write operations to determine a good fillfactor.

    I have typically used this method to set index fillfactor on a per case basis.

  • I strongly suggest doing some tests with the following cases:

    A) More than 10,000 pages of data!

    B) A couple indexes, to watch behavior differences when SELECT, INSERT, UPDATE, and DELETE happens

    B1) An index on a field whose INSERTs and UPDATEs tend to be sequential (say, current date/time)

    B2) An index on a field whose INSERTs and UPDATEs tend to be arbitrary (say, date of birth, though SSN is an even better example

    C) A third fillfactor example; between 85 and 95

    D) Extra credit: Unique and non-unique

    One not uncommon case for indexes in many systems is:

    Millions of rows

    Index on SSN-like field

    DML operations are effectively on random SSN's, usually one at a time

    Single row SELECTs on the SSN-like field are common

    Bulk data SELECTs are perhaps uncommon, but certainly not rare (select SSN, col1, col2 order by SSN-like field)

    Very, very quickly you'll find that a fillfactor in the high 90's results in a much smaller index than 100 would; likewise, it needs to be rebuilt/reorganized less often (also important if one has a small maintenance window).

  • faelconn1ck (12/15/2010)


    What's the consequences if wrong fill factor has been set. So called poor performance will cause something like deadlock or query timeout?

    With poor performance i understand slow Selects or DML statements, which eventually may also cause timeouts. I doubt that these may cause deadlock errors although indirectly they will since transactions will be open for a longer duration!

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • Brian, thank you for the great article. It has shed some light on a very interesting topic.

  • Good article, Brian. It's a nice explanation of what Fillfactor does and some ideas on how to set it.

  • faelconn1ck (12/15/2010)


    What's the consequences if wrong fill factor has been set. So called poor performance will cause something like deadlock or query timeout?

    depending on the size of the table and the amount of data selected an index seek can turn into a scan because you have to search through more pages

  • Really nice article, very well written. Thanks.

    Thanks

  • Thanks for the article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks for the fine article. It raises a few questions for me.

    - How is the optimizer affected by FF?

    - If a FF of 50 is used, causing twice as many reads as a FF of 100, then could it cause the optimizer to not use an index?

    - If, in this same scenario, the leaves eventually get more filled will the optimizer start using the index because less reads are required?

    - Since I operate in a 24/7 environment, I have to try to minimize the amount of index rebuilding I do, since it locks the table for the duration of the rebuild and causes blocking for my users. I have a few fairly large tables (~10,000,000 rows, ~600,000 pages) that are heavily used for both reads/selects and DML. To minimize index rebuilding, would it make sense to go with a lower fill factor, knowing that would penalize the selects? In other words, does a lower fill factor keep an index from getting fragmented as quickly?

  • Thanks for the great article.

    We do index rebuilds every two weeks, and Fill Factor plays an important role.

    I have found that the "magic number" for FF would be the amount of index space that needs to be available between index rebuilds. Our indexes grow approximately 4% per week (8% every two weeks), so I have a FF of 90%.

    Theoretically, that keeps the system with 2% to 10% of free space in the index pages at any given time. From a general performance standpoint, you never want 100% page usage in a database that is written to. (This would be optimal in a read-only scenario.)

    This is just the tip of the iceberg on a huge topic, and proper index maintenance can sometimes make or break the performance of a set of data.

    Well, that's my two cents!

    Happy Holidays!

    -John

  • Thanks brian.

    nice article...easily understandable!

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

Viewing 15 posts - 1 through 15 (of 16 total)

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