How to Avoid Page Split in SQL Server?

This is very interesting topic. How to Avoid Page Split in SQL Server? Let’s start with basics. Page split is a resource intensive operation and causes fragmentation that leads to poor performance in terms of increased I/O operations. We should take it seriously if  there are frequent page splits occur and consider to reduce its occurrences.  Here, i will explain how to check Page Split in SQL Server and how to reduce Page Split and its occurrence.

What is Page Split?

Whenever you update existing rows with the data that is bigger in size to save on their data page then Page Split operation occur to make space for this new update. Data from existing data page first move to new page that is added during Page Split operation and make room to accommodate new records. That is why this operation is resource intensive and create fragmentation. Page splits only occur when data changes in the database. If you are adding all your data at the end of the table then page split will not occur.

If your fill factor is 100 or 0, it will work in a same way. That means there is no room in leaf level page to accommodate future updates. Setting fill factor value to 0 or 100 will increase page split occurrences.

How to Check Page Split in SQL Server?

Here, i will explain how to check page split in SQL Server. Whenever Page split occurs, it will be logged in transaction log file as LOP_DELETE_SPLIT operation. We use fn_dblog function to read SQL Server transaction log file. Run below T-SQL code to see all page splits occurrence using transaction log file.

SELECT *
FROM
fn_dblog (NULL, NULL)
WHERE
[Operation] = N'LOP_DELETE_SPLIT'

You can see LOP_DELETE_SPLIT is logged under operations column. That means it’s a page split operation.

page split using fn_dblog

Paul Randel has given very good sql code to check total number of page splits occurrence. Below is the code you need to run to get this details.

SELECT
[AllocUnitName] AS N'Index',
(CASE [Context]
WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'
WHEN N'LCX_CLUSTERED' THEN N'Clustered'
ELSE N'Non-Leaf'
END) AS [SplitType],
COUNT (1) AS [SplitCount]
FROM
fn_dblog (NULL, NULL)
WHERE
[Operation] = N'LOP_DELETE_SPLIT'
GROUP BY [AllocUnitName], [Context];
GO

You can check total number of page splits are shown for each indexes in below screenshot. Now we will discuss how to avoid page split in SQL Server for such indexes.

Total number of page splits

How to Reduce Page Split Occurrences?

An index that has many random inserts and has very full pages will have an increased number of page splits. This causes more fragmentation and that is very bad for performance. We can reduce page splits by keeping some room in the data pages by setting fill factor value less than 100. The extra bytes on each page will help to minimize page splits caused by extra length in the rows. Read attached article to understand the best value for fill factor to avoid such page-split operations.

We can also use index rebuild operation with the FILL FACTOR option that will allow the page fullness to be changed to fit the query pattern on the index. This will also helpful in reducing total page-split counts.
I hope you like this article. You can drop your questions in comment section. Please follow our Facebook page and Twitter handle to get latest updates.

Manvendra Deo Singh
Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:
Summary
How to Avoid Page Split in SQL Server?
Article Name
How to Avoid Page Split in SQL Server?
Description
Page split is a resource intensive operation and can causes fragmentation that will lead to poor performance in terms of increased I/O operations. We should take it seriously if  there are frequent page splits occur and we should consider to reduce the page split occurrences.  Here, i will explain Page Split and how to reduce the number of its occurrence.
Author
Publisher Name
www.techyaz.com

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *