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.
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?
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.
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.
SELECT Id, DisplayName, Reputation
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?
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.
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.
Does it improve any further with 100% fill factor? Let’s take a look.
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.
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.