SQL Server

How Fill Factor Can Affect Performance3 min read

Fill factor determines the percentage of space to leave open for new data added in an index. Changing the fill factor value from the default of 0 (no space left open) can help in rare cases with performance problems.

In other situations, it can make performance worse by wasting disk space and having a negative effect on queries.

Glass Half Full or Half Empty

Let’s take a look at an example using the StackOverflow 2013 database. In this example, we’ll be looking for users from Ohio. Let’s create an index with a fill factor of 50:

Once we have the index created, we can run the following query to find the index size:

Which gives us:

Keep in mind the index now compared to later on when we rebuild it.  Now let’s search for Ohio users:

Note the 24502 logical reads:

Let’s see how that number changes as we alter the index fill factor.  Let’s alter the index and change fill factor to 100:

Now if we run the script to check the index size we find that it’s about half the size:

Running the same select query against the Users table now has 12363 logical reads, about half as many to return the same data:

0 and 100 Are the Same? Almost…

By default, fill factor is set to 0 which Microsoft says is the equivalent of setting fill factor to 100. Don’t bother trying to alter your index to set fill factor to 0 though, as it’s not recognized as a valid percentage:

Not Always a Quick Fix

I would call this a bit of an extreme example since I’ve only come across fill factor being set as low as 75.  I would be surprised to see fill factor set as low as 50 but anything can happen.  Either way, this example demonstrates the effect it can have on performance. If you want to check the fill factor on your indexes, run the following:

Be mindful of when and where you’re changing fill factor as the index will be locked while rebuilding. That may not be a quick process depending on index size.

Leave a Comment