2019-03-19

Tracking Page Split

When you insert rows into a table they go on a page, your row will have a row length and you can get only so many rows on the 8Kb data page. When that row’s length increases because you update a varchar column for instance, SQL Server needs to move the other rows along in order to make room for your modification, if the combined new length of all the rows on the page will no longer fit on that page then SQL Server allocates a new page and moves rows to the right or left of your modification onto it – that is called a 'page split'.
Page splits are normal and can't be avoided especially in an OLTP database. But, it can be performance bottleneck on your DML workload. Page split leaves space on data pages, and also increases the chances of deadlock and blocking as page locks will be acquired during the update statement executing. We can specify fill factor to reduce potential page splits by providing enough space for index expansion as data is added to the underlying table.
SQL Server provides a DMV to track page splits. sys.dm_db_index_operational_stats gives you the cumulative count of page splits. Below is the query which will allow us to track page splits:
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 AS nonleaf_page_split_for_index
FROM sys.dm_db_index_operational_stats(DB_ID(N'DB_NAME'), NULL, NULL, NULL) AS ios
JOIN sys.indexes AS i ON ios.index_id = i.index_id AND ios.[object_id] = i.[object_id]
JOIN sys.objects AS o ON ios.[object_id] = o.[object_id]
WHERE o.[type_desc] ='user_table'
ORDER BY page_split_for_index DESC;