Apr 11, 2012

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.

CREATETABLE dbo.Note

(

NoteID intNOTNULLCONSTRAINT PK_Note PRIMARYKEYCLUSTERED(NoteID),

NoteText char(1300)NOTNULL

)

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

Insertinto 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.

3 comments:

The page_split event in Extended Events is tracking page allocations, so you get information about normal end page splits that are not problematic along with mid page splits that cause fragmentation. This is not useful in tracking problems leading to fragmentation. I show how to track problematic splits with Extended Events in this blog post: