Using Extended Events in SQL Server

Extended events are the result of full WMI integration with the SQL Server environment, and allow you to monitor virtually any event in the SQL environment, even the Configuration Manager. WMI integration has been in SQL since 2005, you may even have used it without knowing. For example, have you ever created a DDL trigger? If so, you have harnessed the WMI SQL provider.
In Denali, Microsoft have really ramped-up there publicity, even giving them a GUI in Object Explorer.
The example I want to talk about, however, will work fine in SQL 2008, as well as Denali, and that is how to use them to monitor Page Splits on your Server. This can be helpful in tracking down a rouge process, in the event that you are experiencing unexpectedly high levels of index fragmentation.

To do this, the first thing that you need to do, is create an Event Session on the server. This is similar to setting up an Event Notification, and is using the same underlying technology.
In this case, we will want to add an event, that looks at the sqlserver.pagesplits event specifier.  Within this event, we will need to add an action, and in here, we will specify the data that we want to capture. For our purposes, we will want to capture the Session ID, the client app, the Database ID and the SQL statement that caused the page split to occur.
Next, we will need to add a target. Here, we are giving a target specifier, made up of the event package name, and the target type package0.ring_buffer, and we will use an option that adds 5 seconds latency, by holding the event in buffer for 5 seconds before writing it. We need to use the ring_buffer event, because we want the raw data, rather than rolled-up aggregates.

The syntax to create this Event Session is as follows…

…The next step, is to turn the session on. This is done with a simple ALTER EVENT SESSION statement…

…Now, as I am running this on a test server, I created a table with the following definition…

…And this will allow me to generate some page splits, by using the following “lazy man’s loop” (Don’t forget to cancel the query when you are finished!!!)…

…I then ran the following query, which interrogates the event, through the dm_xe_session_targets DMO…

…This query produces an XML document, showing the page splits that have occurred. If you look for spikes in here, it can help you track down a problem process.

SELECT CAST(xst.target_data as xml) FROM sys.dm_xe_session_targets xst
join sys.dm_xe_sessions xe
ON (xe.address = xst.event_session_address)
WHERE xe.name = 'pagesplits'

WHILE 1=1
BEGIN
 INSERT INTO test(col1)
 VALUES (1)
END

CREATE TABLE [dbo].[test](
 [col1] [int] NULL
)
GO
CREATE NONCLUSTERED INDEX [test] ON [dbo].[test]
(
 [col1] ASC
)

ALTER EVENT SESSION pagesplits ON SERVER STATE = start

CREATE EVENT SESSION pagesplits ON SERVER
ADD EVENT sqlserver.page_split
(
ACTION
     (
     sqlserver.session_id,
     sqlserver.client_app_name,
     sqlserver.database_id ,
     sqlserver.sql_text
     )
)
ADD TARGET package0.ring_buffer
WITH (max_dispatch_latency=5seconds)

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom