Introduction

Page splits are one of those silent performance killers in SQL Server that many DBAs have heard about but rarely analyze in depth. On the surface, inserts might look harmless – rows keep getting added, queries return results, and no errors appear. But underneath, SQL Server is constantly managing how data is physically stored on 8KB pages, and each insert has the potential to fragment your index and degrade performance over time.

Inserts at the end of a table generally work smoothly. SQL Server simply appends the new row to the current last page, or allocates a brand new page if there’s no space left. However, when you insert rows out of order into a clustered index, SQL Server must maintain logical order. It does this by splitting existing pages – taking about half the rows from the full page and moving them to a newly allocated page – to create space for the inserted value.

Page splits aren’t just an academic curiosity. They directly impact IO, consume CPU cycles to reorganize data, and can cause blocking or concurrency issues under heavy workloads. They also lead to index fragmentation, slowing down scans and seeks if not managed properly.

SQL Server provides performance counters such as Page Splits/sec and Page Allocations/sec to monitor these activities. While these counters are useful for understanding overall split rates and allocations, they don’t reveal what’s really happening at the page level. A low-level page forensic approach can show exactly which pages were split and how their structure changed. This deeper insight is especially valuable in edge cases – I have seen page splits occur even during serial inserts where they typically aren’t expected, and only detailed page analysis can uncover such differences.

That’s where page forensics come in. By inspecting individual page headers and linkage details, we can see precisely how SQL Server reorganized pages during inserts. In my own investigations, I’ve even found page splits occurring in some serial inserts where they weren’t expected – something aggregate counters alone could never highlight.

In this article, I’ll create a wide-column table to amplify page split effects. We will insert rows in order to observe normal page allocations, then insert rows out of order to force mid-page splits, and finally use page forensics to uncover what really changed under the hood – revealing insights that counters alone simply cannot provide.

Page Splits vs New Page Allocations

SQL Server exposes performance counters such as Page Splits/sec and Page Allocations/sec under the Access Methods object in sys.dm_os_performance_counters. While these counters give an overall rate, they don't differentiate the nature of page activity. For example, new page allocations are harmless – they just add a fresh page at the end. But page splits indicate that existing pages were split due to mid-table inserts or updates causing row expansions.

In this article, we go deeper than these aggregate counters. Using low-level page forensics, we will decode exactly how pages were allocated, split, and linked before and after inserts, showing differences between serial inserts (at the end) and out-of-order inserts (in the middle).

Page Forensics: What Are We Really Looking At?

To understand what happens during inserts, think like the database engine. SQL Server stores rows in 8KB data pages, linked together in a doubly linked list within the B-tree structure of an index. Each page header has critical metadata fields:

• m_pageId – page’s file and page number, e.g. (1:1500)

• m_prevPage and m_nextPage – pointers to previous and next pages in the chain

• m_slotCnt – number of slots or rows currently on the page

• m_freeCnt – free space remaining on the page in bytes

• m_type – type of page (1 for data page, 2 for index page ,10 for IAM etc..)

These can be examined using DBCC PAGE, for example:

DBCC TRACEON(3604);
GO
DBCC PAGE ('simple_talk', 1, 1500, 3);
GO

Here, 'simple_talk' is the database name, 1 is the file ID, and 1500 is the page ID. The fourth parameter (3) controls output detail level. The WITH TABLERESULTS, NO_INFOMSGS options can also be used to extract output in query-friendly formats for forensic scripts.

By analyzing these fields before and after inserts, we see whether rows were simply appended to new pages, or if SQL Server split existing pages to maintain order. This approach goes far beyond high-level counters, letting us pinpoint specific page chains affected by different insert patterns.

Capturing Page Forensics

I prepared below script to capture page-level forensic data in SQL Server. First, it turns on DBCC TRACEON(3604) to enable output to the console. It sets up your database and table context, then pulls all pages allocated to the target table using sys.dm_db_database_page_allocations, filtering for data and index pages only.

For each page, it runs DBCC PAGE with the TABLERESULTS option to read its detailed structure into a temporary table. It extracts key fields: page ID, slot count (number of rows or entries), free space, next page, and previous page pointers. These details reveal how a page is linked and how full it is, which are crucial for detecting page splits, fragmentation, or corruption.

The script then creates a combined string from these extracted values and generates a SHA-512 hash, allowing easy tracking of any changes in page content over time. Additionally, it reads the full page dump file from disk (using OPENROWSET) to compute a second hash representing the entire physical file, verifying data integrity at the file level.

Finally, it inserts all this into the PageTracking table: table name, page metadata, computed hashes, and the raw dump. This creates a robust forensic snapshot of page state, helping DBAs analyse internal changes, detect silent page splits, or validate data corruption investigations. In short, it is like taking a time-stamped MRI scan of every data page in your SQL Server table for deep internals analysis.

We will execute this script after each insert to capture the page dump once new rows are added in our lab environment. Throughout this tutorial, we will refer to it as the Page Forensic Tracker Script.

USE [simple_talk]
GO

/****** Object:  Table [dbo].[PageTracking]    Script Date: 7/1/2025 11:48:59 PM ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PageTracking](
[TableName] [sysname] NOT NULL,
[FileID] [int] NULL,
[PageID] [int] NULL,
[CaptureTime] [datetime2](7) NULL,
[PageHash] [varbinary](64) NULL,
[PageDump] [nvarchar](max) NULL,
[UsedBytes] [int] NULL,
[DumpFileHash] [varbinary](64) NULL,
[m_pageId] [nvarchar](100) NULL,
[m_slotCnt] [int] NULL,
[m_freeCnt] [int] NULL,
[m_nextPage] [nvarchar](100) NULL,
[m_prevPage] [nvarchar](100) NULL,
[CombinedHash] [varbinary](64) NULL,
[m_type] [nvarchar](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[PageTracking] ADD  DEFAULT (sysdatetime()) FOR [CaptureTime]
GO




-- Enable DBCC output to console
DBCC TRACEON(3604);
GO
-- Setup
DECLARE @dbid INT = DB_ID('simple_talk');
DECLARE @objectid INT = OBJECT_ID('dbo.test_page_splits_and_allocations');
IF OBJECT_ID('tempdb..#PageList') IS NOT NULL DROP TABLE #PageList;
SELECT allocated_page_file_id AS FileID,
       allocated_page_page_id AS PageID,
       page_type_desc
INTO #PageList
FROM sys.dm_db_database_page_allocations(@dbid, @objectid, NULL, NULL, 'DETAILED');
DECLARE @FileID INT, @PageID INT, @PageType NVARCHAR(100);
DECLARE PageCursor CURSOR FOR
SELECT FileID, PageID, page_type_desc FROM #PageList;
OPEN PageCursor;
FETCH NEXT FROM PageCursor INTO @FileID, @PageID, @PageType;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @PageType LIKE '%DATA%' OR @PageType LIKE '%INDEX%'
    BEGIN
        DECLARE @PageDump TABLE ([ParentObject] NVARCHAR(MAX), [Object] NVARCHAR(MAX), [Field] NVARCHAR(MAX), [Value] NVARCHAR(MAX));
        DECLARE @sql NVARCHAR(MAX) = 'DBCC PAGE (' + CAST(@dbid AS VARCHAR) + ', '
                                                  + CAST(@FileID AS VARCHAR) + ', '
                                                  + CAST(@PageID AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS';
BEGIN 
TRY
            INSERT INTO @PageDump ([ParentObject], [Object], [Field], [Value])
            EXEC sp_executesql @sql;
            -- Extract required fields
            DECLARE @m_pageId NVARCHAR(100) = NULL,
                    @m_slotCnt INT = NULL,
                    @m_freeCnt INT = NULL,
                    @m_nextPage NVARCHAR(100) = NULL,
                    @m_prevPage NVARCHAR(100) = NULL;
            SELECT @m_pageId = [Value]
            FROM @PageDump
            WHERE [Field] LIKE '%m_pageId%';
            SELECT @m_slotCnt = TRY_CAST([Value] AS INT)
            FROM @PageDump
            WHERE [Field] LIKE '%m_slotCnt%';
            SELECT @m_freeCnt = TRY_CAST([Value] AS INT)
            FROM @PageDump
            WHERE [Field] LIKE '%m_freeCnt%';

            SELECT @m_nextPage = [Value]
            FROM @PageDump
            WHERE [Field] LIKE '%m_nextPage%';
            SELECT @m_prevPage = [Value]
            FROM @PageDump
            WHERE [Field] LIKE '%m_prevPage%';
            -- Create combined string for hashing
            DECLARE @CombinedString NVARCHAR(MAX) = ISNULL(@m_pageId,'') + '|' +
                                                    ISNULL(CAST(@m_slotCnt AS NVARCHAR),'') + '|' +

                                                    ISNULL(CAST(@m_freeCnt AS NVARCHAR),'') + '|' +

                                                    ISNULL(@m_nextPage,'') + '|' +

                                                    ISNULL(@m_prevPage,'');


            DECLARE @CombinedHash VARBINARY(64) = HASHBYTES('SHA2_512', @CombinedString);

            -- Read file dump for file hash

            DECLARE @FilePath NVARCHAR(4000) = 'C:\Data\Page_Dumps\test_page_splits_and_allocations_'

                                               + CAST(@FileID AS NVARCHAR) + '_'

                                               + CAST(@PageID AS NVARCHAR) + '.txt';

            DECLARE @FileContent VARBINARY(MAX) = NULL;

            DECLARE @FileSql NVARCHAR(MAX) = N'SELECT @FileContentOut = BulkColumn FROM OPENROWSET(BULK ''' + @FilePath + ''', SINGLE_BLOB) AS x;';

            BEGIN TRY
                EXEC sp_executesql @FileSql, N'@FileContentOut VARBINARY(MAX) OUTPUT', @FileContentOut = @FileContent OUTPUT;
                PRINT 'Read file: ' + @FilePath + ' Size: ' + CAST(DATADATADATADATADATADATADATADATADATADATADATADATADATADATALENGTH(@FileContent) AS NVARCHAR(100));
            END TRY
            BEGIN CATCH
                PRINT 'OPENROWSET failed for ' + @FilePath;
                SET @FileContent = NULL;
            END CATCH;
            DECLARE @FileHash VARBINARY(64) = NULL;
            IF @FileContent IS NOT NULL
                SET @FileHash = HASHBYTES('SHA2_512', @FileContent);
            -- Insert into tracking table

            INSERT INTO dbo.PageTracking (
                TableName, FileID, PageID, CaptureTime,
                m_pageId, m_slotCnt, m_freeCnt, m_nextPage, m_prevPage, CombinedHash,
                PageDump, DumpFileHash
            )
            VALUES (
                'test_page_splits_and_allocations', @FileID, @PageID, SYSDATETIME(),
                @m_pageId, @m_slotCnt, @m_freeCnt, @m_nextPage, @m_prevPage, @CombinedHash,
                @FileContent, @FileHash
            );

        END TRY
        BEGIN CATCH
            PRINT 'Skipping incompatible page: FileID ' + CAST(@FileID AS VARCHAR) + ', PageID ' + CAST(@PageID AS VARCHAR);
        END CATCH
    END
    FETCH NEXT FROM PageCursor INTO @FileID, @PageID, @PageType;
END

CLOSE PageCursor;
DEALLOCATE PageCursor;
GO

Simulating the Lab

I created a table with wide fixed-length columns to ensure each row consumes substantial space, making splits easy to observe.

USE simple_talk;
GO

IF OBJECT_ID('dbo.test_page_splits_and_allocations', 'U') IS NOT NULL
    DROP TABLE dbo.test_page_splits_and_allocations;
GO

CREATE TABLE dbo.test_page_splits_and_allocations
(
    Id INT PRIMARY KEY,
    Data1 CHAR(1000),
    Data2 CHAR(1000),
    Data3 CHAR(1000)
);
GO

Using CHAR(1000) ensures each row is around 3KB, resulting in only two or three rows per 8KB page.

Serial Inserts (In-Order)

I inserted 20 rows with ascending Ids to observe how SQL Server allocates pages when data is inserted in order. This is a script which is always having ID in increasing order and so it does cause no page split or minimal page split.

This script will insert rows with ID 10,20,20.... till 200.

DECLARE @i INT = 10;

WHILE @i <= 200
BEGIN
    INSERT INTO dbo.test_page_splits_and_allocations (Id, Data1, Data2, Data3)
    VALUES (@i, REPLICATE('A',1000), REPLICATE('B',1000), REPLICATE('C',1000));

    SET @i += 10;
END

After above insert run the page forensic tracker script.

Next we will run another serial inserts , but this time id will be 210 , 220... 400, and we will execute page forensic tracker script again to get snapshot after both inserts and compare how page structures get changed after later insert.

This is worth to note insert is still serial and page splits will be minimal in this case.

DECLARE @i INT = 210;

WHILE @i <= 400
BEGIN
INSERT INTO dbo.test_page_splits_and_allocations (Id, Data1, Data2, Data3)
VALUES (@i, REPLICATE('A',1000), REPLICATE('B',1000), REPLICATE('C',1000));

SET @i += 10;
END

After running both inserts lets query PageTracking table to get snapshot of all pages after first and second serial inserts and also new pages allocated while second insert.

SELECT [TableName]
,[FileID]
,[PageID]
,[CaptureTime]
,[m_pageId]
,[m_slotCnt]
,[m_freeCnt]
,[m_nextPage]
,[m_prevPage]
,[CombinedHash]
,[m_type]
FROM [dbo].[PageTracking]
order by 1,2,3,4

GO

We get below output in tabular format after running above query(Scroll to complete right for better view, also it is more interpretable if you copy it to excel).

Explaination(Table output After Serial Insert)

We have marked every page changed after second insertion in bold. m_type 1 refers to data pages, 2 refers to index pages and 10 refer to Index allocation Mapping pages. Lets focus on data pages which changed are (2134026,2134027, 2134028, 2134029,  2134030 and 2134031).

But lets focus the changes that occured in those pages:

Page 2134026

The m_freeCnt reduced from 5083 to 2070, confirming new rows were inserted consuming space. Its m_nextPage changed from (0:0) (no next page, it was previously a leaf data page) to (1:2134029). This indicates no page split occurred here – it had enough free space to accommodate the insert, and no row movement happened; only a new child page was linked.

Page 2134029

The m_freeCnt decreased from 8063 to 2070, and m_slotCnt rose from 1 to 2, showing new rows were inserted here. Its m_nextPage changed from (0:0) to (1:2134030), and m_prevPage updated from (1:2044936) to (1:2134026). This confirms no page split occurred as it had sufficient free space, and no row movement was needed; it simply accommodated the insert and updated links.

Page 2134030

Here, m_freeCnt reduced from 8063 to 2070, and m_slotCnt increased from 1 to 2, indicating a new row insertion. m_nextPage updated from (0:0) to (1:2134031) and m_prevPage changed from (1:2044936) to (1:2134029). Again, no split occurred, and no row movement took place; the page had enough space for direct insertion and linked to the next page.

Page 2134031

For this page, m_freeCnt dropped from 8063 to 2070, and m_slotCnt rose from 1 to 2, confirming a new row insertion. Its m_nextPage changed from (0:0) to (1:2142128), and m_prevPage updated from (1:2044936) to (1:2134030). There was no split as it had ample space, and no row movement was triggered – only a straightforward insert and page chain update occurred.

Few non changing pages are 2134027, 2134028. these page showed no change in m_freeCnt (remained 5083) or m_slotCnt (remained 1), meaning no new rows were inserted, no row movement occurred, and no split took place in this page.

But as we had expected, In serial insert, we were not able to find any page being splitted or their m_prevPage or m_nextPage changed from a real data page to another data page. If a m_prevPage or m_nextPage changes from (0,0) to some data page, it does just previously it has not parent or child item and just a new page has been allocated.

Mid-Table Inserts (Out-of-Order)

Next, I inserted five rows with Ids that fall between existing rows to force mid-table inserts and observe page split behaviour.

INSERT INTO dbo.test_page_splits_and_allocations (Id, Data1, Data2, Data3)
VALUES 
(5, REPLICATE('X', 1000), REPLICATE('Y', 1000), REPLICATE('Z', 1000)),
(11, REPLICATE('X', 1000), REPLICATE('Y', 1000), REPLICATE('Z', 1000)),
(15, REPLICATE('X', 1000), REPLICATE('Y', 1000), REPLICATE('Z', 1000)),
(7, REPLICATE('X', 1000), REPLICATE('Y', 1000), REPLICATE('Z', 1000)),
(3, REPLICATE('X', 1000), REPLICATE('Y', 1000), REPLICATE('Z', 1000));
GO

When inserting into the middle of the index, SQL Server must maintain logical order. This forces existing pages to split to accommodate the inserted rows, resulting in new pages and potentially fragmented allocation.

We ran page forensic tracker script before and after execution of above non-serial inserts. And after that we executed below script to check difference in page structures like we did in serial insertions:

SELECT [TableName]
,[FileID]
,[PageID]
,[CaptureTime]
,[m_pageId]
,[m_slotCnt]
,[m_freeCnt]
,[m_nextPage]
,[m_prevPage]
,[CombinedHash]
,[m_type]
FROM [dbo].[PageTracking]
order by 1,2,3,4

GO

We get below output again in tabular format after running above query(Scroll to complete right for better view, also it is more interpretable if you copy it to excel).

Explaination(Table output After Mid Table Insert)

Now lets compare structure of page before and after inserts. The pages which are changed are marked in bold. Those pages are 2053048, 2053049, , 2053050,2053059,2053060,2053061, 2053062.(Scroll to right for better view, also export to excel for better interpretibility)

Page 2053048

m_freeCnt remained 2070, but m_nextPage changed from (1:2053050) to (1:2053061) and m_prevPage updated from (0:0) to (1:2053062). This indicates a split event occurred – it was previously linked directly to page 2053050 but now links to a new page (2053061) while also getting a new previous page (2053062). Such page chain updates without reduction in free space strongly suggest row movement due to split redistribution rather than direct insert filling space.

Page 2053049

m_slotCnt increased from 10 to 14 and m_freeCnt decreased slightly from 7966 to 7914, indicating new rows were inserted here. However, no m_nextPage or m_prevPage changes were observed, so no split or row movement occurred within this page – it was simply updated with additional index entries.

Page 2053050

m_freeCnt remained 2070, but m_prevPage changed from (1:2053048) to (1:2053060) while m_nextPage stayed at (1:2053051). This implies a split event upstream caused re-linking – the page itself was not split, but row movement from adjacent split required the chain to update its previous page pointer.

Page 2053059

Here m_slotCnt increased from 1 to 2 and m_freeCnt dropped from 8063 to 2070, with m_nextPage changing from (0:0) to (1:2053062). This confirms new rows were inserted consuming space, and a new next page was linked, suggesting a split occurred where this page remained but a new sibling page (2053062) was created to accommodate overflow rows – row movement due to split is evident.

Page 2053060

m_freeCnt dropped significantly from 8063 to 5083, indicating new rows were inserted. Its m_nextPage changed from (0:0) to (1:2053050), and m_prevPage updated from (1:2044936) to (1:2053061), showing it was inserted between pages during a split. Thus, this page likely represents a split result page where row movement occurred to balance tree structure.

Page 2053061

This page’s m_freeCnt also reduced from 8063 to 5083, and m_prevPage changed from (1:2044936) to (1:2053048) while m_nextPage updated to (1:2053060). The combination of decreased free space and changed links indicates it absorbed moved rows from a split, confirming split event with row redistribution.

Page 2053062

Here, m_freeCnt dropped from 8063 to 5083, m_nextPage changed from (0:0) to (1:2053048), and m_prevPage updated to (1:2053059). The reduction in free space along with linking back to 2053048 confirms it was newly created due to a split, receiving moved rows from the original page.

Conclusion

This forensic analysis clearly demonstrates why mid-table inserts are significantly costlier than serial inserts. In our test, we observed multiple pages undergoing splits where row movement occurred to balance data across old and new pages. Pages such as 2053048, 2053050, 2053059, and others showed evidence of chain re-linking, creation of new sibling pages, and redistribution of rows. Unlike serial inserts, which mostly add rows to the end of the index without disturbing existing pages, out-of-order or mid-table inserts force SQL Server to reorganize data structures to maintain logical order.

Such page splits are resource-intensive operations because they involve copying approximately half the rows to a new page, updating page pointers, and maintaining index consistency, often resulting in significant I/O and CPU overhead. If your workload frequently inserts data that is not strictly sequential – for example, inserting older timestamps or updating clustered key values – you will almost certainly encounter these performance penalties.

To mitigate this, consider optimising your index design and data load patterns. Using a lower fill factor can leave breathing space within pages to accommodate future inserts without immediate splits, though it increases overall storage requirements. Alternatively, adopting table partitioning based on time or ranges can isolate inserts to specific partitions, avoiding broad impact across the entire table. Understanding these low-level behaviours is crucial for DBAs and architects aiming to build scalable, insert-heavy systems without encountering sudden slowdowns due to silent page split storms during peak workloads.

Caution

This script uses DBCC PAGE, an undocumented and unsupported command intended for internal use and troubleshooting by advanced DBAs or Microsoft support engineers. Running it on large tables or in frequent automated loops may cause performance overhead due to intensive metadata reads and console output generation. Always test in non-production environments before deploying in live systems. Use with appropriate permissions and ensure you understand the risks of reading low-level page structures. Results and internal structures may vary between SQL Server versions, so validate thoroughly on your target environment before relying on it for operational decisions.