According to the Microsoft TechNet SQL Server Glossary, a page split is “the process of moving half the rows or entries in a full data or index page to a new page to make room for a new row or index entry.”
Related to this, the general Microsoft TechNet Glossary defines pages splitting as the “creation of two nonsequential pages from one page lacking the space to accept new data that must be inserted into the page because of the logical ordering of data defined in that page.”
A page split typically occurs when there is no space left on a data page for more INSERTS or UPDATES, so SQL Server transfers some data from that data page to another data page. For example, if a new row is inserted onto an already full page, this process is undertaken to create space for the new rows. Given that the data now span multiple data pages, this process is called a page split.
It should be noted that page splits are a normal process in OLTP systems. That said, too many page splits can cause fill factor-related performance issues that will slow down your system.
You can monitor page splitting activity using the Performance Monitor counter Page Splits/sec. In most cases, the page-splits-per-second counter increases as the workload activity on the server increases.
The counter measures the number of page splits per second when updating or inserting data. As a general rule, page splits lead to fragmentation and will cause your table to perform poorly. Therefore, fewer page splits leads to improved system performance.
Also keep in mind that the page-split-per-second counter by itself cannot determine if you have too many page splits, as how do you know how many page splits is too many. One possible solution is to use the Batch Requests/Sec counter as a “yardstick”. Most experts suggest the page-split-per-second counter should be running at less than 20% of the batch requests per second.
Idera’s SQL Defrag Manager is designed to control all types of fragmentation, including page splits, and automatically eliminates unnecessary page splits as a part of the tool’s system-wide defragmentation process.