Page Splits in SQL Server AND Entity Framework

How does SQL Server store data?

Upon adding the row to SQL Server, it does not immediately write that row to the disk.
It adds the data to the fixed-sized block called Page.

Think of the page as a piece of paper that exists in a notebook with a size of 8KB in SQL Server.

After filling up one page, the remaining rows get added to another page.

What is an Index?

Imagine you have a 500-page book and you want to find the chapter about a topic like .NET, you have two choices:

• Start reading from page one until you find it.
• Use the table of contents.

The index can be termed as the table of content of the database.
The only difference here is that SQL Server uses B+ Trees for indexes.

Instead of looking at all the rows, SQL Server simply moves down the tree to find the data on the specific page.

This makes the process much faster.

And now the main question:

What is a Page Split?

Suppose a page looks like this:

Page 1

A
B
D
E

Now you want to insert C.

But the problem is that the page is already full and there isn't any space for the new row.

SQL Server has to:

1. Allocate a new page.
2. Move roughly half of the existing rows to the new page.
3. Insert C into its correct sorted position.

Before:

Page 1

A
B
D
E

After:

Page 1          Page 2

A               C
B               D
                E

This is a Page Split.

Why are Page Splits bad?

At first glance, it seems like SQL Server only moved a few rows. But no. In reality, the operation is much more expensive.

More disk writes: Instead of performing a simple INSERT, SQL Server must:

• Allocate a new page.

• Copy rows.

• Update the B+ Tree.

This makes each write operation more costly. And moving rows and updating the B+ Tree also takes extra CPU cycles. Furthermore, when splitting the page, SQL Server needs to briefly lock both the page and the B+ Tree structure. On heavily used databases, this causes extra contention and waits for other processes.

What causes Page Splits?

The most common causes are:

• A clustered index built on random GUIDs (NEWID()).

• Updates that increase the size of existing rows.

• Completely full pages.

• An inappropriate Fill Factor.

How can you reduce Page Splits?

*- Use sequential keys:
*
Instead of:

NEWID()

Prefer:

IDENTITY

or

NEWSEQUENTIALID()

Since new rows are always inserted near the end of the index, page splits become much less frequent.

*- Configure Fill Factor
*
If you know a table receives frequent inserts or row-growth updates, you can intentionally leave free space inside every page.

For example:

FILLFACTOR = 80

This tells SQL Server to fill each page to only 80%, leaving 20% free for future inserts.

But we should remember, a very low Fill Factor isn't always better because it creates more pages, which can increase storage usage and slightly reduce read performance. The optimal value depends on your workload.

*- Rebuild indexes
*
If fragmentation becomes significant, rebuilding the index reorganizes the pages and removes fragmentation.

Fill Factor in Entity Framework Core

EF Core provides built-in support for SQL Server's Fill Factor option.

modelBuilder.Entity<Order>()
    .HasIndex(x => x.CreatedAt)
    .HasFillFactor(80);

The generated migration produces SQL similar to:

CREATE INDEX IX_Orders_CreatedAt
ON Orders(CreatedAt)
WITH (FILLFACTOR = 80);

Summary

Page Split is a process in which a new row is added to a full page. In order to free up space for the newly added row, a new page is created, the rows are moved to the new page (about half), and the B+ tree structure is updated. The process ensures the maintenance of indexes in sorted form but adds to write overhead, CPU utilization, and fragmentation in heavy workloads. Sequential keys and proper setting of Fill Factor help prevent Page Splits.