Table of Contents

Introduction

Page split is an important factor in analyzing how often fragmentation occurs in the database. Page splits are normal because when rows are added to index, a page needs to be added to the right hand side of index to accommodate new rows and for this database engine does page splits and adds a new page. But, it can be performance bottleneck when update is done on a row and the value added is not able to fit on the page and thus causing page split.. This type of page split is nasty one as it leave space on the page which would not be utilized as when data will be inserted in table having index it would be written to last page on right hand side.

Page Split

When page split happens due to update operation, SQL Server will allocate a new page and process have to wait till a new page is allocated, at this point of time the page which needs to be updated is locked. In more granular terms a latch will be taken and this continue till page allocate and rows are inserted. This could be a deciding factor on OLTP environment. Page splits can be normal as pointed above or can be nasty. It's difficult to find Nasty page split and normal one using DMV. We can track that with fn_dblog. If we are using SQL Server 2012 we can use extended events to track nasty page splits.

As per online resources there are lot of articles which point, on how to track page splits using undocumented command fn_dblog and fn_dump_dblog. But, there are performance implications using this command in Production server and also such commands are not supported by Microsoft.

DMV to Track Page Splits

We don't need to worry about using undocumented commands to track page split because SQL Server provides a DMV to track page splits. Sys.dm_db_index_operationl_stats DMV provides a method to track page splits. If we refer to Microsoft online documentation for this DMV we will find two columns

leaf_allocation_count

bigint

Cumulative count of leaf-level page allocations in the index or heap.

For an index, a page allocation corresponds to a page split.

nonleaf_allocation_count

bigint

Cumulative count of page allocations caused by page splits above the leaf level.

0 = Heap or columnstore

We can see leaf allocation count column value corresponds to page splits which occurred for the index. Although we would not get information about whether this page split was done on right side of index when data was added or was result of row not being able to fit on a page causing extra page to be allocated  and thus fragmentation.

T-SQL Script to track Page Split

Please note that there is no page split for HEAP(A table without clustered index). Heaps have forwarding pointers.Below is a simple query which will allow we to track page splits

--Script to check page split for index

SELECT

IOS.INDEX_ID,
O.NAME AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX,
IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS
JOIN
SYS.INDEXES I
ON
IOS.INDEX_ID=I.INDEX_ID
AND IOS.OBJECT_ID = I.OBJECT_ID
JOIN
SYS.OBJECTS O
ON
IOS.OBJECT_ID=O.OBJECT_ID
WHERE O.TYPE_DESC='USER_TABLE'

Above query will give page splits for all Indexes present in database. We can add specific table name in the query to filter down page splits for indexes belonging to particular table.

We can also use EXTENDED EVENTS trace to capture page splits but that can be used for SQL server 2012 and above please refer to See Also Section for Link.

See Also

  1. Use Extended events In SQL Server 2012 to Track Page Splits
  2. SQL Server General & Database Engine Resources on the TechNet Wiki