Does low fill factor affect SELECT performance?

Sometimes good intentions lead to big problems.

Imagine a system where page splits were thought to be causing a big enough performance problem that it made sense to change the fill factor setting of a lot of indexes.

Now imagine you thought the fill factor setting was for how much free space you wanted when creating or rebuilding an index instead of how full.

OUCH.

Let’s say I want to leave 5% free space on the pages after creating or rebuilding an index, but I mistakenly use FILLFACTOR=5 instead of FILLFACTOR=95. How will this affect my server?

FILLFACTOR=5

Using the Users table in the StackOverflow database, I changed the fill factor setting on the two indexes I have on the table: a clustered index/PK on Id and a non-clustered index on CreationDate, Reputation.

 

With both indexes set to 5% fill factor, the table size is 21GB.

Running a simple query against the Users table doesn’t show much of a problem.

The query returns 1,102 rows, takes less than a quarter of a second and uses about a second of CPU time, but it has high logical reads. I could improve the query performance by modifying the non-clustered index to include the DisplayName column to avoid the key lookup, but that’s not the point of this post.

Using SQLQueryStress, I tested how fast 20 threads could execute the query 50 times (1000 iterations).

The load test completed in 75 seconds.

What happens when I “fix” it?

FILLFACTOR=95

Now time to test 95% fill factor as that was the intention of the person who mistakenly used 5%. I’ll show 100% fill factor next.

 

It went from 21GB with 5% fill factor down to almost 1.5GB with 95% fill factor. Woah!

But did it help with performance? You betcha.

The duration only improved a little bit, but it used a bit less CPU time and a lot less logical reads.

The load test completed much faster.

FILLFACTOR=100

Does it improve any further with 100% fill factor? Let’s take a look.

 

It’s a little bit smaller than when it was at 95% fill factor.

Just a tad better than 95% fill factor.

Helper query to fix it

This will generate the ALTER INDEX commands to fix this issue. Modify the FILLFACTOR setting to what you want to use, the fill_factor value in the WHERE clause to the current “bad” value and the ONLINE option as needed.

Are page splits really a problem?

There are workloads where frequent page splits are a problem. I thought I had a system like this many years ago, so I tested various fill factor settings for the culprit table’s clustered index. While insert performance improved by lowering the fill factor, read performance drastically got worse. Read performance was deemed much more critical than write performance on this system. I abandoned that change and instead recommended a table design change since it made sense for that particular table.

Pick your battles

I don’t usually even bother recommending to clients to change fill factor settings unless they are using less than 100% for identity column indexes, such as the clustered index/PK on Users. It’s just not a battle that I’m willing to fight when there are more important changes to be made to improve performance. On a system using 5% fill factor for nearly all indexes, you can bet fixing the fill factor will be the first recommendation.

Previous Post
Tell Us What You Make: The 2019 Data Professional Salary Survey
Next Post
Never Judge A Query By Its Cost

14 Comments. Leave new

  • Hello. Thank you for the great article! I have a question. The script to generate ALTER INDEX commands also returns indexes on sys.objects.. if we search for indexes with fillfactor =10, for example. Shouldn’t those be filtered out from the search? Something like this: WHERE fill_factor = 10 AND is_disabled = 0 AND is_hypothetical = 0 and s.name ‘sys’;

    Otherwise we would set fillfactor to 100 to all the sys indexes as well..

    Thanks.

    Reply
  • are there any real scenarios which result in a fill_factor below 50?
    assuming random insert, pages fill up, then split into 2×50% full pages, one of which collects the row.
    So the worst case (head inserts meaning that after split one of the pages gathers no new rows) you would end up at just 50% fill factor

    Reply
    • Not sure that there are any scenarios where <50% makes sense. I've never even gone below 80%, and that was many years ago.

      Reply
  • Henrik Staun Poulsen
    December 14, 2018 3:56 am

    When thinking about setting Fill_Factor to something below 100, one should consider how many page splits per second the system is currently seeing. (I’ve just found your brilliant Power BI First Responder Kit, and this can be seen on the Perfmon page, select Page Splits/sec).
    How many page splits is too many, or enough to warrant Fill_Factor 100 ?
    2, 42, 4200?

    Reply
  • […] Tara Kizer explains the performance tradeoff when setting fill factor for an index: […]

    Reply
  • Ron Klimaszewski
    December 17, 2018 9:52 am

    I sadly had to set FF to 50 once. Had a vendor app which would perform fine in the AM, terrible by noon. The data was transient, gone in 24 hours or less. It was basically session state data for many applications and was small in size, but highly transactional and was very visible to the 1000’s of users when it slowed down.

    All of the clustered indexes used NEWID() aka GUID, and the FF was at 90% with indexes rebuilt nightly. Page splits were a huge issue, especially with the slow SAN storage at the time. The write outweighed the need of the read.

    This was on SQL 2000, before NEWSEQUENTIALID() became available which probably would have been relatively easy to implement. No longer using that app and don’t know if the vendor ever fixed their design.

    Reply
  • I’ve worked on vendor software where the default fill factor was set to 90% for ALL tables…

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}