Page Split

A SQL Server event that occurs when an index page is full and a new row must be inserted, forcing SQL Server to split the page into two half-full pages, causing fragmentation and performance overhead.

• Page splits are a primary cause of index fragmentation: They increase the number of pages SQL Server must read to satisfy a query and generate additional transaction log records.
• In heavily insert and update-intensive workloads, frequent page splits can significantly increase fragmentation over time: This degrades query performance and increases I/O activity.
• Page splits can be prevented or reduced by: Setting an appropriate fill factor to leave free space for inserts, using sequential primary keys such as IDENTITY or SEQUENCE, and performing regular index maintenance.
• DBAs can monitor page split rates using: The SQLServer:Access Methods - Page Splits/sec performance counter or Extended Events sessions.
• Non-leaf page splits occurring higher in the B-tree structure are more severe: They indicate structural fragmentation that typically requires a full index rebuild to resolve.
• Related terms: Fill Factor, Index Rebuild, Fragmentation, Bloat, IDENTITY.