Search code examples
sql-serverfillfactor

How do you measure SQL Fill Factor value


Usually when I'm creating indexes on tables, I generally guess what the Fill Factor should be based on an educated guess of how the table will be used (many reads or many writes).

Is there a more scientific way to determine a more accurate Fill Factor value?


Solution

  • You could try running a big list of realistic operations and looking at IO queues for the different actions.

    There are a lot of variables that govern it, such as the size of each row and the number of writes vs reads.

    Basically: high fill factor = quicker read, low = quicker write.

    However it's not quite that simple, as almost all writes will be to a subset of rows that need to be looked up first.

    For instance: set a fill factor to 10% and each single-row update will take 10 times as long to find the row it's changing, even though a page split would then be very unlikely.

    Generally you see fill factors 70% (very high write) to 95% (very high read).

    It's a bit of an art form.

    I find that a good way of thinking of fill factors is as pages in an address book - the more tightly you pack the addresses the harder it is to change them, but the slimmer the book. I think I explained it better on my blog.