Are you trying to prevent page splits in SQL server?

This guide is for you.

As part of our Server Management Services here at Ibmi Media, we regularly help our Customers to fix SQL related issues.

In this context, we shall look into how to prevent page splits in SQL server.

What triggers Page splits operation?

Page split is basically a resource-intensive operation. It mainly causes fragmentation that leads to poor performance in terms of increased I/O operations.
While updating an existing row with the data that is bigger in size to save on their data page then Page Split operation occurs to make space for this new update.
As a result, the data from the existing data page first move to a new page that adds up during the Page Split operation and make room to accommodate new records.

Additionally, this only happens when data changes in the database.

How to check Page Splits in SQL Server?

Generally, page split occurrences are logged in the transaction log file as LOP_DELETE_SPLIT operation. We use the fn_dblog function to read the SQL Server transaction log files. We run the below T-SQL code to see all page splits occurrence using the transaction log file;

SELECT *

FROM

fn_dblog (NULL, NULL)

WHERE

[Operation] = N’LOP_DELETE_SPLIT’

In this case, the LOP_DELETE_SPLIT logs is under the operations column. Therefore, this is a page split operation.
 

How to Reduce Page Splits Occurrences in SQL Server?

To reduce Page Splits, simply follow the following steps;
1. Increasing the fill factor on indexes
If we decrease the fill factor in the indexes then it increases the amount of empty space on each data page. The more empty space there is, the fewer page splits we will experience.
In another way, having too much unnecessary empty space can also hurt performance. Because if less data is stored per page, then it takes more disk I/O to read tables, and fewer data can be stored in the buffer cache. So we experiment to find the ideal fill factor.

2. Rebuilding the indexes more often.
Over time, any empty space created with the fill factor is used up, and more and more page splits will occur. Moreover, each time we rebuild an index, the fill factor’s empty space renews. It means that there is more room for data and fewer page splits will occur.

3. Add clustered indexes to monotonically increasing primary keys.
By doing this, we eliminate INSERT-caused page splitting entirely. Because all new records will be added to the end of the table.

4. Get a faster I/O subsystem.
Finally, faster hardware is better to help reduce page splits. The fact is that the faster hardware, the less of an issue are page splits.

[Need urgent support with SQL queries? – We are available to help you.]