Database Animations: You’ve Heard of Page Splits. Here’s What They Really Mean.

You’ve heard that page splits are bad, and they’re an indication that your table design is making your storage work too hard. You’ve heard that the right answer to fix it is adjusting fill factor lower, or doing regular index maintenance.

Before you watch the below animation, you’ll wanna get up to speed with how index seeks work. Then, let’s explain page splits with an animation:

That page split has a couple of effects that may not be immediately obvious when you watch it happen.

First, it amplified writes. Instead of only having to write page 1:301 for the updated row, we also had to allocate page 1:845, write to that, AND write to the b-tree’s intermediate page at 1:200 to point to the newly allocated intermediate tree too.

And, because the leaf pages are a doubly linked list – we covered that in the index seeks animation page above – that means we also have to write to page 1:302 to tell it that the “previous” page in the doubly linked list is now page 1:845, not 1:301.

So 1 single row update caused 4 writes there.

Second, the leaf pages are now out of order on disk. Before, when we wanted to scan that doubly-linked list, we just read from page 1:300, to 1:301, to 1:302… Now, we’ll read 1:300, then 1:301, then jump to 1:845, then jump back to 1:302.

This isn’t just caused by wider-column updates.

Say UserId 26837 moves from Helsinki to Jakarta, and say we’ve got an index on the Location. If we’ve recently rebuild that Location index, there may not be any free space left on the page that holds the Jakarta users.

And amusingly, BOTH the clustered and nonclustered indexes could be hit by the same update statement, of course: we could have page splits on both of them when we update indexed columns to wider values or to different values.

It’s amazing technology ever works at all, isn’t it? So much havoc and chaos behind the scenes.

Like Gnarls Barkley, this was a big deal back in 2006.

First, right about now, your Grandpa Maxtor is pointing at the screen and clapping, yelling, “SEE, THIS IS BAD BECAUSE STORAGE PREFERS SEQUENTIAL ACCESS, AND ALL THESE RANDOM WRITES ARE GONNA WREAK HAVOC ON MAH DRIVEZ!”

What ol’ graybeard is referring to is that we used to use these things called “hard drives” for database storage. They used tiny rusty magnetic frisbees to store data, and those drives relied on magnetic heads on arms that would actually move around from place to place in order to read data stored in different areas of the platter.

You’re never gonna believe how that used to work, so let’s show you. Time for another magical animation brought to you by the robots that couldn’t get a job at Pixar:

Back then, these drives could only pull off 150 operations per second, max, and you can kinda understand why when you think about these spinning frisbees and the moving arms trying to catch data flying by.

Today, it’s much less of a big deal.

There are several reasons this just doesn’t matter as much as it used to.

First off, most database servers store their data on solid state these days. While magnetic hard drives capped out at around 150 IOPs, solid state can usually do 100,000 IOPs or more, with NVMe drives doing well over 1,000,000 IOPs. In most servers, we simply don’t sweat an extra handful of logical reads or writes per transaction.

Second, we don’t just use a handful of dedicated drives per server anymore: we spread systems across giant pools of drives, like hundreds or more. The storage pool is much more able to absorb brief bursts of writes, and usually has battery-backed cache for writes to act as a shock absorber.

Next, even if you’re still using hard drives, even if you only have a handful of them, you’re probably not doing just one transaction at a time on one table at a time, in one database at a time. People pack tables & databases together like clowns into a car, and as a result, there’s no such thing as sequential storage access anymore. All storage access is random. There’s no point in optimizing for putting all your pages in a row. There’s a party on your platters, and everybody’s invited.

Furthermore, these writes are asynchronous anyway! Page writes are done long after the transaction. Time for another animation, this time to explain how write-ahead-logging works:

When you do an insert, update, or delete, SQL Server shoves the record down onto the transaction log, and yes, that part has to happen synchronously before your transaction finishes. However, everything else you did just hangs out in RAM, cached for a while. That way, if other people make changes to the same pages, we don’t have to write them to disk repeatedly. SQL Server just clears ’em out of memory later in a process called a checkpoint.

So transaction log write quantity & latency? Big deal. Data page write quantity & latency? Not so much.

I’m not saying page splits don’t matter at ALL.

I’m just saying they don’t matter much.

Furthermore, even if you thought they did matter, you’re probably looking at the Perfmon counter for Page Splits/sec, which is an absolute liar. It includes the numbers of new pages allocated to store new incoming rows, like when you add new pages to the end of a clustered index on an identity column. Those aren’t splits at all! If you wanna track the real page splits, you have to bust out a custom Extended Events session. In the words of Gnarls Barkley, I think you’re crazy.

If you’re still setting fill factor lower than 100%, or you’re doing index reorganizations trying to fix out-of-order pages on disk, it’s time to watch the How to Maintain Databases in 2026 module in my Fundamentals of Index Tuning class. (Yes, even you, Grandpa Maxtor. Especially you, and if you’re Grandpa Maxtor’s manager, you should consider getting him up to speed before he wastes more of your company’s money and resources doing things that won’t improve performance in any measurable way.)

Note: I generated the animations in this post with Claude Code, but all of the text & demos are completely written by me.