Performance Tuning
Database Animations: Stop Using Page Splits to Justify Lowering Fill Factor.
You’re looking at page split numbers in a monitoring tool or Perfmon, and you’ve heard that page splits are bad, so you’re lowering fill factor, expecting your page splits to go down.
You’re monitoring the wrong number.
To prove it, let’s check the page splits counter, add 10,000 pages to a table, and then check it again:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @StartingPageSplits BIGINT = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Page Splits/sec%'); DROP TABLE IF EXISTS dbo.PageSplitTest; CREATE TABLE dbo.PageSplitTest (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, BigData CHAR(8000)); INSERT INTO dbo.PageSplitTest(BigData) SELECT 'BigData' FROM generate_series(1, 10000); SELECT cntr_value - @StartingPageSplits AS PageSplits FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Page Splits/sec%'; |
I purposely designed a very special table there: each row is about 8,000 bytes on its own. No row can possibly share a page with another row. Every row needs its own 8KB page. There is simply no such thing as splitting this page to move rows around. Every new row that comes in, gets its own page, at the end of the object because we’re using an identity clustered primary key. New rows go in at the end.
So, why does this counter show over 10,000 page splits every time you run the test?
The stupid Page Splits counter includes new page allocations.
I don’t understand why this Perfmon counter was ever built this way, but it’s always been this way. Anytime a new page is added for a table, it’s called a “page split” in this counter, even when nothing is being split. So simply by doing inserts, you’re gonna get page splits, regardless of how your index or fill factor is configured.
Note: I generated the above animation with Claude Code, but everything else in the post is all me, including the T-SQL.
So why are there so many people who are so misled about lowering fill factor in order to reduce page splits? It’s because they use Copilot, I guess. I asked SSMS Copilot, “In SQL Server, will lowering fill factor reduce the Page Splits/sec Perfmon counter numbers, given the same workload?”
Bing agreed too:
ChatGPT at least mentioned that there are catches, like sequential inserts:
ChatGPT also linked to this Microsoft Learn documentation page, which does indeed say – without qualification – that Page Splits/sec are the “number of page splits per second that occur as the result of overflowing index pages.”
This is why it’s so important, now more than ever, to doubt anything that doesn’t have a demo attached.
Sometimes it feels like 1/3 of my training classes are just correcting bad information that y’all got fed from elsewhere. It’s not your fault – well, I mean it is, if you don’t ever end up in the training classes, heh!
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
The only index maintenance activity I’ve done in the last several years that made significant difference in performance has been to fix low fill factor. Things like cleaning jobs using fast ordered deletes don’t block or spill anymore even in pessimistic isolation. Reports don’t spill sorts to tempdb and compressed columns don’t make it look like compression is a waste of time anymore. The few edge cases have been in tables sensitive to page density, but the two are the same picture.