Page Splits Part 3 – Identifying through Extended events

My sincere apologies to all in delaying producing part 3 of the series.  I got into a set of urgent tasks which prevented me from updating my blog.

SQL server has one more mechanism which allows us to identify page splits.  This feature called extended events introduced with SQL server 2008 and According to Microsoft sources,  eventually, it will replace SQL trace.

SQL Server 2012 extended the feature not only by introducing new events but also by introducing a new UI for capturing extended events

By using extended events we can identify the database where the page split has occurred.  As you may remember, by using performance monitor we couldn’t identify the database where the page split has occurred.

In addition, extended events provide more information, which we couldn’t gather before.

Let us have a walk-through of identifying page splits through extended events

In SQL Server 2012, extended events (commonly referred as XE) management is part of the management tab. Under management there is a section for extended events, and it has a folder named sessions.  There are two sessions already. System-health is the XE equivalent of default trace.

Let us right click and select new session, as shown in picture 1. (New session wizard too will take you to the same destination, but to understand the basics, let us bypass the wizard).

It will lead to another dialog box, shown in picture 2

As mentioned at the top of the dialog box, session name cannot be empty so, let us type the name “page split test”. As we are going to select the events manually, we don’t need a template. Similarly, we can keep the schedule related checkboxes unchecked.

Let us make the “Casualty Tracking” related checkbox checked (It has a title “Track events are related to each other”).

Now let us click on events tab on the left hand side. We’ll see another screen

Since we do not know the event names specified let us do a search.

Let use type “page split” under event library and we will end up having only one event named “page_split” let us select it and press “>” button as shown in picture 4.

Now let us click on the configure button on the top right side. This session allows us to configure the data we need to capture on the event we have selected.

Let us select all the events and capture the following data 

• Client_app_name
• Client_hostname
• Collect_system_time
• Database_name
• Sql_text (it is not visible in the picture below)

Since we are not going to track the page splits in system tables, (which carries the database_id from 1 to 4) let us add a filter by selecting the filter tab

Let us select “database_id” from the field.  You may see the red mark indicating something going wrong.  It is only because we haven’t completed the filter condition. It will go away when we complete it. Let us select “>” in the operator and type 4 in the value columns.

Now we have added a filter to say what the filtering we are doing.

We are now ready to run the session.  For additional configuration, we can visit Data storage and Advanced tabs (from the left hand side) too. But for this session we can run with this configuration only. Let us press “OK” and save the session.  Now we can see an additional session created (but stopped) under the sessions folder in management studio.

Let right click on the page split test session and start it. Once you started the session, and then again right click, you will see watch live data option enabled.  When this option is clicked another window will open (By default it may open like another window but you can drag and keep it as another floating window too. ) This window is the extended events equivalent of profiler.

Once the session is started and watch data window is opened you may see a new window with just two columns - name and timestamp.  (If you are wondering where are the other columns we have selected, please keep your patience. You will see them)

Now let us take the first example we ran during the first part of the series. 

CREATE TABLE dbo.Note

(

NoteID int NOT NULL CONSTRAINT PK_Note PRIMARY KEY CLUSTERED(NoteID),

NoteText char(1300) NOT NULL

)

Just creating a table does not make any difference in the page_split event.  We do not see any changes in the watch window

Insert into dbo.Note Values (1,REPLICATE('a', 1300))

 Now we see a new enter in the watch list.

Now we can move the columns we need from the details section to table view.

I’ll encourage you all to try the scripts provided in part 1 and identify the times page_split event occurs and the operations involved. 

When you are finished with the details, wait for my observations.

References:
http://sqlblog.com/blogs/michael_zilberstein/archive/2009/02/05/11734.aspx
This article was written for SQL Server 2008. It gives the explanation for the extended events related to page split.