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.
Bingo. And the gains are clear and measurable.
For so called “Ever increasing indexes” that suffer only inserts, I agree.
I also agree for when such indexes are inserted that don’t fill rows and can have hunreds of rows per page.
I even agree with ever increasing indexes that are inserted at the end and then go through “Expansive Updates” that cause rampant page splits because… all new rows go in and fill the pages to 100 % and lowing the fill factor simply will not prevent page splits for such things.
But those aren’t the only index patters that there are.
To wit, I also agree that any index maintenance or relate Fill Factors that you do wrong is worse than doing nothing at all. (The “Brent Line” on my Random GUIDs ‘tube demonstrates that).
But, as with all else, “It Depends”. There are places where “real” page splits actually happen (such as Random GUIDs but includes other index types) where it can matter.
Virtually nothing is a panacea. Heh… “Look eye… Always look eye” (From the Karate Kid movie).
As usual, I agree with you 100%, sir!
Page splits are very high up on the list of things people worry about when they have literally every other problem biting them in the ass and surrounding areas that they’re not paying attention to.
Hahaha, that’s a great way of saying it.
“It depends” has more than one meaning there. 😉
HAHAHA
I was going to suggest your July page splits article as good companion reading, only to discover that the very bottom of that article actually called out the page split issue covered here.
This isn’t me complaining that you salami-slicing your content. After all, I learned about this today, not in July. I’m part of the problem 🙁
You occasionally snark at us for correcting you on an article without even finishing the first paragraph. Does this mean you’ve given up on us actually finishing your articles?
Hahaha, no, I’m just revisiting a lot of classic topics and adding animations now. The readers really seem to enjoy the animations – they get a ton of clicks.
Hi all,
We never decrease our fill factor on our indexes that means more page splits. We do daily rebuilds when index is fragmented more than 30%. Are we doing it right. Please correct me if I am wrong.
Check out the module on database maintenance in the year 2026 in my Mastering Index Tuning classes – we explain why that’s wrong, and what to do instead.
To back up Jeff’s point, I had a system with massive page splits on one table. The table was almost always involved with blocking issues and at times caused downtimes.
A random guid with 100% fill factor was lowered. In the years since, no more alerts.
It does depend
And, just to add a recent addition to SQL Server…
In Azure SQL DB, they just added the new Automatic Index Compaction feature, which is REORGANIZE on conditional steroids where the condition fits tables like this, that table will continue to be a problem. A lot of tables could become a problem just like they did when the old 5/30 method took to using REORGANIZE on most indexes that suffered page splits.
I’ve not used and don’t intend to need to use Azure SQL so I’v not been able to personally monitor for the BAD page splits that are going to occur. It seems the same as the problems with REORGANIZE in SQL Server on-prem.