SQL Canada (SQLCAN)

Client was noticing high number of Page Splits/sec in relation to Batch Request/sec; however could not figure out why.  They were not have performance issue but still was curious what was generating it?

So how do we track it?  Well short of the answer is there is no easy way to do this.   Even though perfmon has ability to tell you page splits are taking place by Page Splits/Sec in SQLServer: Access Methods; it cannot tell you where.

We can use the fn_dblog function to read the transaction log file in SQL Server 2000/2005 and in SQL Server 2008 we can rely on extended events.

  SELECT   Operation
, AllocUnitName
		 , COUNT(*) AS NumberofIncidents
    FROM sys.fn_dblog(null, null)
   WHERE Operation = N'LOP_DELETE_SPLIT'
GROUP BY Operation, AllocUnitName

Example from #1.

I am not going to repeat the work done by others here, the code or examples are from reference links below if you need additional details.

1. Daniel Adeniji’s – Learning in the Open. Microsoft – SQL Server – Page Splits. Link.
2. Microsoft Connect. SQL Server Extended Events Page/Splits Event Additions. Link.
3. SQL Server Pro. Evaluating the Page Splits/sec Value. Link.
4. SQLBlog.Com. Michael Zilberstein (Hopefully soon to be a SQL MVP ;-)). Monitoring page splits with Extended Events. Link.

Edit Notes

• March 30, 2012: I had it wrong that Michael is not a SQL MVP yet, so here to hoping he gets MVP :). Thanks for correction mate.