Advertisements
Home > Administration, Performance Tuning, SQL 2000, SQL 2005 > How to find out what is causing page splits?

How to find out what is causing page splits?

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.
Advertisements
  1. Michael Zilberstein
    March 30, 2012 at 5:46 pm

    2 points:
    1. I’m not MVP. Not yet 🙂
    2. There was continuation:
    http://sqlblog.com/blogs/michael_zilberstein/archive/2011/04/25/35175.aspx

    • March 30, 2012 at 7:55 pm

      Heh. I thought I saw MVP by your name 😉 heh. Sorry mate, thanks for the follow up link. I am sure my customers will appreciate it :).

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: