Getting Started with Extended Events in SQL Server 2008

By:   |   Updated: 2010-10-29   |   Comments (2)   |   Related: More > Extended Events

Problem

In a previous tip, we did an overview of Extended Events and the different components that make up Extended Events. Now that you know what Extended Events offers, how do you use it to help troubleshoot performance issues? In this tip we take a step by step look at how to implement and use Extended Events.

Solution

Extended Events for SQL Server 2008 (and later versions) provides a generic tracing and troubleshooting framework which allows deeper and more granular level control of tracing which was not possible using earlier methods like DBCC, SQL Trace, Profiler, etc... These earlier methods still exist and Extended Events is not a replacement

In my last tip, "An Overview of Extended Events in SQL Server 2008" I gave you an overview of Extended Events (XEvents) in SQL Server 2008, then I talked about its capability in terms of the number of events to trace and its deeper level of troubleshooting and finally I talked about the different components of Extended Events.

In this tip, I am going to give some hands on examples to understand how this powerful tracing feature can assist you with your performance problems.


Example 1

In my first example, I am going to create an Extended Event session to monitor which SQL command execution is raising or causing a user defined exception. In Script #1, I am checking if the event session already exists and if it does then drop it. Next I am creating an event session and specifying to monitor for sqlserver.error_reported event. Next, using the ACTION clause, I am specifying what additional data I want to capture in the event data payload and also a predicate to include only user defined exceptions. Next with the ADD TARGET clause, I am specifying who will be consuming the fired event and where event data will be stored.

Script #1 - Creating an Extended Event Session

--Check if the event session is already exisiting, if yes then drop it first
IF
EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='MonitorUserDefinedException')
DROP EVENT SESSION MonitorUserDefinedException ON SERVER
GO

--Creating a Extended Event session with CREATE EVENT SESSION command

CREATE
EVENT SESSION MonitorUserDefinedException ON SERVER
--Add events to this seesion with ADD EVENT clause

ADD
EVENT sqlserver.error_reported
(

--Specify what all you want to capture event data with ACTION Clause
ACTION
(
sqlserver
.session_id,
sqlserver
.sql_text,
sqlserver
.tsql_stack
)
--Specify predicates to filter out your events
WHERE sqlserver.error_reported.error >= 50000
)

--Specify the target where event data will be written with ADD TARGET clause

ADD
TARGET package0.ring_buffer
WITH
(max_dispatch_latency = 1seconds)
GO

SQL Server provides several DMVs to verify created event sessions. The DMVs also include the actions, predicates and targets associated with these event sessions. Script #2 gives details about a specified event session and its associated objects.

Script #2 - Verify event session with its events, actions and targets

--This query will give details about event session, its events, actions, targets
SELECT
sessions.name AS SessionName, sevents.package as PackageName,
sevents
.name AS EventName,
sevents
.predicate, sactions.name AS ActionName, stargets.name AS TargetName
FROM
sys.server_event_sessions sessions
INNER
JOIN sys.server_event_session_events sevents
ON
sessions.event_session_id = sevents.event_session_id
INNER
JOIN sys.server_event_session_actions sactions
ON
sessions.event_session_id = sactions.event_session_id
INNER
JOIN sys.server_event_session_targets stargets
ON
sessions.event_session_id = stargets.event_session_id
WHERE
sessions.name = 'MonitorUserDefinedException'
GO

When you create an extended event session it does not capture data until it is started. Script #3 uses the ALTER EVENT SESSION command to start the created event session. After the event session is started it starts to monitor the events and capture event data for the specified events if it satisfies the predicates.

Script #3 - Starting event session to monitor events

--We need to enable event session to capture event and event data
ALTER
EVENT SESSION MonitorUserDefinedException
ON
SERVER STATE = START
GO
SELECT
* FROM sys.dm_xe_sessions
WHERE
name like 'MonitorUserDefinedException'
GO
RAISERROR
('User Defined Exception!!!', 16, 1)
GO

Event consumers are called targets. Targets process the events and store the event data payload. Script #4 browses the captured event data for the specified event. As I said before you can include additional data to be captured with event-data using the ACTION clause. Targets could be either synchronous or asynchronous. If it is asynchronous, event data might take some time to appear in the targets.

Script #4 - Browsing captured event data by the event session

--This query will display the captured event data for specified event session
SELECT
CAST(stargets.target_data AS XML)
FROM
sys.dm_xe_session_targets stargets
INNER
JOIN sys.dm_xe_sessions sessions
ON
sessions.address = stargets.event_session_address
WHERE
sessions.name = 'MonitorUserDefinedException'
GO

As long as an event session is in the started mode, it monitors for events. If you want to stop monitoring, you can either drop the event session in which case even the meta data about the event session will be removed from SQL Server or you can stop the event session using the ALTER EVENT SESSION command to temporarily postpone monitoring of events. Needless to say, if you are stopping an event session it will still be there in SQL Server in silent mode and can be enabled again whenever there is a need.

Script #5 - Stop or Drop event session

--You can stop event session to capture event data
ALTER
EVENT SESSION MonitorUserDefinedException
ON
SERVER STATE = STOP
GO

--To remove a event session, use DROP EVENT SESSION command

DROP
EVENT SESSION MonitorUserDefinedException ON SERVER
GO

Example 2

In this example, I am going to create an event session to monitor expensive queries running on the server. Here I am using the sql_statement_completed event to monitor sql completion, but as specified only executions which have taken either more than 1000 CPU cycles or has a run duration greater than 10000. I will also be capturing additional information using the ACTION clause in event data. Apart from that, I will be using an asynchronous file target. Please note I have specified two files, one is to store event data and the other is for storing meta data. Both of these files are required with this type of target. In the image below you can see captured data in XML format.

For this example you need to make sure that SQL Server can create files for the path used for FILENAME and METADATAFILE. This example queries the AdventureWorks database. If you have this database installed you can just run the entire query. If not, you will need to replace the query with a query that hits one of your databases.

Script #6 - Extended event for monitor expensive queries

--Check if the event session is already exisiting, if yes then drop it first
IF
EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='MonitorExpensiveQuery')
DROP EVENT SESSION MonitorExpensiveQuery ON SERVER
GO

--Creating a Extended Event session with CREATE EVENT SESSION command

CREATE
EVENT SESSION MonitorExpensiveQuery ON SERVER
--Add events to this seesion with ADD EVENT clause

ADD
EVENT sqlserver.sql_statement_completed
(

--Specify what all additional information you want to capture
--event data with ACTION Clause
ACTION
(
sqlserver
.database_id,
sqlserver
.session_id,
sqlserver
.username,
sqlserver
.client_hostname,
sqlserver
.sql_text,
sqlserver
.tsql_stack
)
--Specify predicates to filter out your events
WHERE sqlserver.sql_statement_completed.cpu > 1000
OR sqlserver.sql_statement_completed.duration > 10000
)

--Specify the target where event data will be written with ADD TARGET clause
ADD TARGET package0.asynchronous_file_target
(

SET FILENAME = N'D:\ExpensiveQuery.xet',
METADATAFILE
= 'D:\ExpensiveQuery.xem'
)

GO

--This query will give details about event session, its events, actions, targets

SELECT
sessions.name AS SessionName, sevents.package as PackageName,
sevents
.name AS EventName,
sevents
.predicate, sactions.name AS ActionName, stargets.name AS TargetName
FROM
sys.server_event_sessions sessions
INNER
JOIN sys.server_event_session_events sevents
ON
sessions.event_session_id = sevents.event_session_id
INNER
JOIN sys.server_event_session_actions sactions
ON sessions.event_session_id = sactions.event_session_id
INNER
JOIN sys.server_event_session_targets stargets
ON
sessions.event_session_id = stargets.event_session_id
WHERE
sessions.name = 'MonitorExpensiveQuery'
GO

--We need to enable event session to capture event and event data

ALTER
EVENT SESSION MonitorExpensiveQuery
ON
SERVER STATE = START
GO
--Run a test query against AdventureWorks database

SELECT
* FROM AdventureWorks.Sales.SalesOrderHeader H
INNER
JOIN AdventureWorks.Sales.SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID
GO

--This query will display the captured event data for specified event session

SELECT
CAST(event_data AS XML) event_data, *
FROM
sys.fn_xe_file_target_read_file
(
'D:\ExpensiveQuery*.xet',
'D:\ExpensiveQuery*.xem'
, NULL, NULL)
GO

--You can stop event session to capture event data

ALTER
EVENT SESSION MonitorExpensiveQuery
ON
SERVER STATE = STOP
GO

--To remove a event session, use DROP EVENT SESSION command

IF
EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='MonitorExpensiveQuery')
DROP EVENT SESSION MonitorExpensiveQuery ON SERVER
GO

Extended Events for SQL Server 2008

Please note these two things:

  1. When an extended event is created it does not get started on its own you need to use the ALTER EVENT SESSION command to start or stop the event session as per your need.
  2. To create, alter or drop an event session you need to have the CONTROL SERVER permission.
Next Steps


Last Updated: 2010-10-29


get scripts

next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, January 28, 2013 - 6:16:43 AM - Suresh Back To Top

 

Hi, is there an extended event where I can know username\login of a particular query executed in past?


Friday, October 29, 2010 - 12:06:50 PM - santosh vaddepalli Back To Top

when I run the first script#1 my connection lost,and when I try to login again Iam getting error 233 .

TITLE: Microsoft SQL Server Management Studio

------------------------------

 

Failed to connect to server teknosol\apple2008. (Microsoft.SqlServer.ConnectionInfo)

 

------------------------------

ADDITIONAL INFORMATION:

 

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

 

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

 

------------------------------

BUTTONS:

 

OK

------------------------------



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools