Finding Page Splits

Finding Page splits by using undocumented function fn_dblog (this queries the transaction log) -

SELECT *
FROM ::fn_dblog(NULL, NULL)
WHERE operation = 'LOP_DELETE_SPLIT'

ref ; http://killspid.blogspot.com/2006/07/using-fndblog.html

You can summarise them like this -

Select COUNT(1) AS NumberOfSplits, AllocUnitName , Context
From fn_dblog(NULL,NULL)
Where operation = 'LOP_DELETE_SPLIT'
Group By AllocUnitName, Context
Order by NumberOfSplits desc 

ref - Identifying Page Splits


Other ways to monitor  page splits -

• DBCC SHOWCONFIG
• Extended events (SQL 2008+ only)

Recommended links about Page Allocation

MS CSS Sql Server Engineers : How It Works: SQL Server Page Allocations

Recommended links about Page Splits

Tony Rogerson : What is a page split and why does it happen?
SQL Server Performance : At what point should I worry about page splits?
Michelle Ufford : Page Splitting & Rollbacks
Michelle Ufford : sys.fn_physLocCracker (SQL 2008 Undoumented function)