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.
|
1 2 |
ALTER INDEX PK_Users_Id ON dbo.Users REBUILD WITH (FILLFACTOR = 5); ALTER INDEX ix_CreationDate_Reputation ON dbo.Users REBUILD WITH (FILLFACTOR = 5); |
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.
|
1 2 3 4 |
SELECT Id, DisplayName, Reputation FROM dbo.Users WHERE CreationDate >= '20150101' AND Reputation >= 5000; |
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.
|
1 2 |
ALTER INDEX PK_Users_Id ON dbo.Users REBUILD WITH (FILLFACTOR = 95); ALTER INDEX ix_CreationDate_Reputation ON dbo.Users REBUILD WITH (FILLFACTOR = 95); |
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.
|
1 2 |
ALTER INDEX PK_Users_Id ON dbo.Users REBUILD WITH (FILLFACTOR = 100); ALTER INDEX ix_CreationDate_Reputation ON dbo.Users REBUILD WITH (FILLFACTOR = 100); |
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.
|
1 2 3 4 5 |
SELECT 'ALTER INDEX ' + i.name + ' ON ' + s.name + '.' + OBJECT_NAME(i.object_id) + ' REBUILD WITH (FILLFACTOR = 100, ONLINE = ON);' FROM sys.indexes i JOIN sys.objects o ON i.object_id = o.object_id JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE fill_factor = 5 AND is_disabled = 0 AND is_hypothetical = 0; |
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.
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.
Michael, it’s a starter script. Definitely filter out the system stuff.
Thank you for the reply, Tara.
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
Not sure that there are any scenarios where <50% makes sense. I've never even gone below 80%, and that was many years ago.
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?
Henrik – great question! The sad thing is that that counter also includes new page allocations. You’ll even see page splits when loading new rows into a table with a clustered index on an identity field!
I did write “Fill_Factor “” 100, but it got lost on the way.
Stupid editor!
Fill_Factor NOT EQUAL to 100
[…] Tara Kizer explains the performance tradeoff when setting fill factor for an index: […]
@Brent; yes, the Page Splits/Sec counter is for both new and old pages. But if the counter is really low, one could safely argue that delays due to page splits is NOT an issue, and hence Fill_Factor should be 100.
Yep!
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.
I’ve worked on vendor software where the default fill factor was set to 90% for ALL tables…