By: Arshad Ali | Comments (2) | Related: > 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
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
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
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
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
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
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
Please note these two things:
- 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.
- To create, alter or drop an event session you need to have the CONTROL SERVER permission.
Next Steps
- Review An Overview of Extended Events in SQL Server 2008
- Review SQL Server Extended Events Dynamic Management Views
- Review Event Notifications in SQL Server for Tracking Changes
- Review Identify Deprecated SQL Server Code with Extended Events
- Review SQL Server Tempdb Usage and Bottlenecks tracked with Extended Events
- Review my previous tips
About the author
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
