Hi Friends,

We all know that page splits are expensive operation for the database engine, also causing excessive fragmentation. I was trying a few things with extended events and observed that we can track/monitor page splits using Extended Events.

Extended Events is a very flexible framework to troubleshoot database engine performance issues. It allows deep dive troubleshooting which is sometimes not possible using traditional troubleshooting techniques.

In this article, I want to demonstrate how you can monitor page splits using Extended Events. Not just monitor that its happening, but also extract the database in which its happening, the query that’s causing it and the file id which is the end victim of page splits.

Let us being. I have commented my code well.

First, just to show you some metadata, you can query a few DMVs to see that the default XE package has page split event.

Next, we want to create an event session based on the above event. Apart from the default payload, we want to extract the SQL query that is causing page splits, amongst, database id, etc. This can be done using Actions. We want the event data to be recorded and we choose File Target.

Finally, start the event.

Now, let us simulate page splits. The below code is very self-explanatory to generate some page splits.

Now, let us get back to the Extended Events code. Meanwhile, you can see that 2 files have been created in the folder you specified in Target section.

The file size might still show 0 KB as the event session is running and we have not yet extract anything from the target. Now let us extract the event data. Replace the filenames appropriately. Extended Events framework provides a function to read the event data file which I am using here to get the data into a temporary table.

Next, you can preview the data.

You can click on the XML link in the results pane and you will observe XML payload something like this…

We can use XQuery to read the XML event information.

When you run the above query, you can see the database name, the SQL query, etc that caused the splits. You can customize the above query to extract file id, page id, etc, if need be.

That’s it friends. Hope you enjoyed this article.

Also tweet and pingback if you wish so.

Wait… you can stop the event now, if you wish so :)