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.
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:
http://sqlskills.com/blogs/jonathan/post/Tracking-Problematic-Pages-Splits-in-SQL-Server-2012-Extended-Events-e28093-No-Really-This-Time!.aspx
Hi Jonathan,
Nice to see you visiting my blog. Thanks for your comments. My series still continues, and I am planning to explain more details today.
One more point, If anything factually incorrect, please let me/the readers know, If anything wrong here, I like to stand corrected.
Post a Comment