SQL: The Down Side of a Low FILLFACTOR

When you create or rebuild an index in SQL Server, you can specify a FILLFACTOR value. It determines how full (as a percentage) that SQL Server should try to make the pages.

There is also one special value. Zero says “I haven’t set a value” and it applies the default, which by default, is 100%. Any value from 1 to 100 is the target percentage.

So why do people set lower values for FILLFACTOR? The argument is they know that their inserts are going to happen all over the table (i.e. not just neatly at the end of the table), and they’re trying to avoid page splits. And yes, lots of page splits can be a nasty thing. The most common problem case that people describe is where they’ve used a GUID as a clustering key (usually also a primary key) for a table. Then the inserts happen randomly throughout the table.

Now many applications do this with all their tables. SharePoint is one of the applications that has made database decisions that I really, really don’t like. Because they used GUID keys all over the place, they just routinely make all the indexes have a 70% FILLFACTOR. That’s really sad.

The problem with low values is that your data is spread across more pages, so you have to read more pages to get the same amount of data.

If you set a 50% FILLFACTOR, you now have to read twice as many pages to get the same amount of data.

And this is where the pain really occurs. Most systems spend the vast majority of their time reading, not writing. The time it takes for writes to occur might be important, but if you’re going to optimize most SQL Server systems today, you need to be optimizing for reads.

And that’s exactly the opposite of what you’re doing with a low FILLFACTOR.

I often hear people discussing the proportion of writes on most systems. People assume that systems will spend 10 or 20% of the time writing. I had a lengthy discussion with a Microsoft product group member who told me that he thought even 40 or 50% was common. I’ve worked on these systems a long time, and I can assure you, that’s not common.

Every time I measure the proportion of writes on systems, I typically see numbers like 1%. So you have to ask yourself if you want to optimize for the 1**% or for the 99%.** 

I’m not underestimating the pain that a large number of page splits can cause but you need to be really sure that that’s your biggest issue before you start dropping FILLFACTOR values. And you certainly never want to do it as a blanket rule across all your tables and indexes.

2019-03-05