How do you measure SQL Fill Factor value

Question

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?

Answer 1

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.

Answer 2

I would tend to be of the opinion that if you're after performance improvements, your time is much better spent elsewhere, tweaking your schema, optimising your queries and ensuring good index coverage. Fill factor is one of those things that you only need to worry about when you know that everything else in your system is optimal. I don't know anyone that can say that.