Monday, October 5, 2009

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 -

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)

No comments: