Wondering how to avoid page splits in SQL server? Take a peek at this blog.

Here at Bobcares, we have seen several such SQL related queries as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at how to avoid page splits in SQL server.

Know more about Page splits

Page split is 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.

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

How we 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 the below image we can see the LOP_DELETE_SPLIT logs under the operations column. This is a page split operation.

How we Reduce Page Splits Occurrences in SQL Server

Here are some of the solutions our Support Engineers follow to reduce the page split.

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.

Lastly, we prefer faster hardware. Because the faster hardware, the less of an issue are page splits.

[Need any assistance with SQL queries? – We’ll help you]

Conclusion

In short, bad page splits not only can cause performance issues during the actual page split but also upon subsequent queries on that index before it is rebuilt. Today, we saw how our Support Engineers avoid page splits.