SQL Server Extended Events via T-SQL

Extended Events gives you the ability to monitor and collect different events and system information from SQL Server and correlate these events for later analysis.  It is much easier to perform analysis using Extended Events as all the data is collected in a single source.  Also, as newer features have been added to SQL Server over the years, such as AlwaysON and ColumnStore indexes as examples, events to monitor these features are only available in extended events and are not available in SQL Profiler or SQL Trace.  SQL Server 2016 has over 1320 Extended Events whereas every version since SQL Server 2008 has had only 180 SQL Trace events.  Also, since it was designed from scratch, monitoring using Extended Events has been shown to have less resource overhead than its older counterparts.

With its initial release in 2008 there was no GUI available to manage Extended Events so database administrators had to configure it using TSQL which was not the most straightforward task.  This led to many people (myself included) to still use the original trace tools for the most part.  Starting with SQL Server 2012 a GUI tool was introduced which allowed you to configure events and even perform some analysis from within the interface.  SSMS v17 even added XEvent Profiler which allows you to configure a real-time monitor similar to how SQL Profiler was used.  This along with all the other features mentioned above has more and more database professionals using Extended Events to monitor their SQL Server environments.

In the rest of this tutorial we will walk through everything you need to know in order to start using Extended Events to monitor you own SQL Server environments.  Topics will include the following:

  1. Architecture
  2. Creating Extended Event Sessions
  3. Managing Extended Event Sessions
  4. Viewing Extended Events Target Data
  5. Extended Events vs. SQL Profiler/Trace 
  6. Built-in Extended Event Sessions
  7. Sample Custom Extended Event Sessions
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRunningQuery')
DROP EVENT SESSION LongRunningQuery ON SERVER
GO

-- Create Event
CREATE EVENT SESSION LongRunningQuery
ON SERVER

-- Add event to capture event
ADD EVENT sqlserver.rpc_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
WHERE (
duration > 1000 
AND sqlserver.client_hostname <> 'A' 
)
),
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
WHERE (
duration > 1000
AND sqlserver.client_hostname <> 'A'
)
),

ADD EVENT sqlserver.module_end
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
WHERE (
duration > 1000000
AND sqlserver.client_hostname <> 'A'
)
)
ADD TARGET package0.asynchronous_file_target(
SET filename='C:\New folder\LongRunningQuery.xet', metadatafile='C:\New folder\LongRunningQuery.xem'),
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 SECONDS, TRACK_CAUSALITY = ON)
GO
 
-- Enable Event,
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=START
GO
 
DECLARE	@XMLLongRunning XML
SELECT	@XMLLongRunning = CAST(dt.target_data AS XML)
FROM sys.dm_xe_session_targets dt
JOIN	sys.dm_xe_sessions ds
		ON ds.Address = dt.event_session_address
JOIN	sys.server_event_sessions ss
		ON ds.Name = ss.Name
WHERE dt.target_name = 'ring_buffer'
AND ds.Name = 'LongRunningQuery'
 
select T.N.value('local-name(.)', 'varchar(max)') as Name,
       T.N.value('.', 'varchar(max)') as Value
from @XMLLongRunning.nodes('/*/@*') as T(N) 

-- Stop the event
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=STOP
GO
 
-- Clean up. Drop the event
DROP EVENT SESSION LongRunningQuery
ON SERVER
GO
 
------------------------------
--Shred XML for easy reading--
------------------------------
--pull into temp table for speed and to make sure the ID works right
if object_id('tempdb..#myxml') is not null
DROP TABLE #myxml
CREATE TABLE #myxml (id INT IDENTITY, actual_xml XML)
INSERT INTO #myxml
SELECT CAST(event_data AS XML)
FROM sys.fn_xe_file_target_read_file
('C:\New folder\LongRunningQuery*.xet',
'C:\New folder\LongRunningQuery*.xem',
NULL, NULL)
 
--Now toss into temp table, generically shredded
if object_id('tempdb..#ParsedData') is not null
DROP TABLE #ParsedData
CREATE TABLE #ParsedData (id INT, Actual_Time DATETIME, EventType sysname, ParsedName sysname, NodeValue VARCHAR(MAX))
INSERT INTO #ParsedData 
SELECT id,
DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), UTC_Time) AS Actual_Time,
EventType,
ParsedName,
NodeValue
FROM (
SELECT id,
A.B.value('@name[1]', 'varchar(128)') AS EventType,
A.B.value('./@timestamp[1]', 'datetime') AS UTC_Time,
X.N.value('local-name(.)', 'varchar(128)') AS NodeName,
X.N.value('../@name[1]', 'varchar(128)') AS ParsedName,
X.N.value('./text()[1]', 'varchar(max)') AS NodeValue
FROM [#myxml]
CROSS APPLY actual_xml.nodes('/*') AS A (B)
CROSS APPLY actual_xml.nodes('//*') AS X (N)
) T
WHERE NodeName = 'value'
DECLARE @SQL AS VARCHAR (MAX)
DECLARE @Columns AS VARCHAR (MAX)
SELECT @Columns=
COALESCE(@Columns + ',','') + QUOTENAME(ParsedName)
FROM
(
SELECT DISTINCT ParsedName
FROM #ParsedData		
WHERE ParsedName <> 'tsql_stack'
) AS B
SET @SQL='
SELECT Actual_Time, EventType,' + @Columns + ' FROM
(
SELECT id, EventType, Actual_Time, ParsedName, NodeValue FROM
#ParsedData ) AS source
PIVOT
(max(NodeValue) FOR source.ParsedName IN (' + @columns + ')
)AS pvt order by actual_time, attach_activity_id'
EXEC (@sql) 
--The following example shows how to drop an event session.
DROP EVENT SESSION evt_spin_lock_diagnosis
ON SERVER

--Lists all the event session definitions that exist in SQL Server.
SELECT  * FROM sys.server_event_sessions 

--Returns a row for each action on each event of an event session.
SELECT  * FROM sys.server_event_session_actions 

--Returns a row for each event in an event session
SELECT  * FROM sys.server_event_session_events 

--Returns a row for each customizable column that was explicitly set on events and targets
SELECT  * FROM sys.server_event_session_fields 

--Returns a row for each event target for an event session.
SELECT  * FROM sys.server_event_session_targets 

--view the Extended Events equivalents to SQL Trace events using Query Editor
USE MASTER;
GO

SELECT DISTINCT
tb.trace_event_id,
te.name AS 'Event Class',
em.package_name AS 'Package',
em.xe_event_name AS 'XEvent Name',
tb.trace_column_id,
tc.name AS 'SQL Trace Column',
am.xe_action_name as 'Extended Events action'
FROM (sys.trace_events te LEFT OUTER JOIN sys.trace_xe_event_map em
ON te.trace_event_id = em.trace_event_id) LEFT OUTER JOIN sys.trace_event_bindings tb
ON em.trace_event_id = tb.trace_event_id LEFT OUTER JOIN sys.trace_columns tc
ON tb.trace_column_id = tc.trace_column_id LEFT OUTER JOIN sys.trace_xe_action_map am
ON tc.trace_column_id = am.trace_column_id
where tc.name like '%completed%'
ORDER BY te.name, tc.name

select * from sys.trace_Events

--To get the fields for all events 
select p.name package_name, o.name event_name, c.name event_field, c.type_name field_type, c.column_type column_type
from sys.dm_xe_objects o
join sys.dm_xe_packages p
on o.package_guid = p.guid
join sys.dm_xe_object_columns c
on o.name = c.object_name
where o.object_type = 'event'
order by package_name, event_name

SQL Server Extended Events via T-SQL

--WHEN USING TSQL TO CREATE AN EXTENDED EVENT, THESE ARE THE 'STEPS' TO FOLLOW:
--1:  EXECUTE THE SCRIPT TO DROP ANY EXISTING SESSIONS

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

--2: CREATE AN EVENT SESSION
CREATE EVENT SESSION FindProblemQueries
ON SERVER

--3: SELECT THE EVENTS YOU WANT TO CAPUTRE WITH THE ACTIONS (GLOBAL FIELDS) AND PREDICATES (FILTERS)
ADD EVENT sqlserver.sql_statement_completed
(ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
WHERE (duration > 1000
AND sqlserver.client_hostname = 'DESKTOP-QMOOH4U'))

--4 ADD THE TARGET (WHERE YOU WANT TO SAVE THE FILES)
ADD TARGET package0.asynchronous_file_target(
SET filename='C:\New folder\FindProblemQueries.xet', metadatafile='C:\New folder\FindProblemQueries.xem'),

--SAVE IN MEMORY
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 SECONDS, TRACK_CAUSALITY = ON)
GO
 
--STEPS 1 THROUGH 4 SHOULD BE EXECUTED AT ONCE
--5: START THE SESSION

ALTER EVENT SESSION FindProblemQueries ON SERVER
STATE=START
GO
 
 --INSERT DATA
 SELECT TOP 1000 [Fname]
      ,[Lname]
      ,[Phone]
  FROM [SQL2].[dbo].[People2]
 
--7: START THE SESSION
ALTER EVENT SESSION FindProblemQueries ON SERVER
STATE=STOP
GO
 
--8: DROP THE SESSION (DROPPING THE SESSION DOES NOT DELETE THE FILES!!!)
DROP EVENT SESSION FindProblemQueries
ON SERVER
GO

--9: CREATE A TEMP TABLE FOR ANALYSIS
if object_id('tempdb..#myxml') is not null
DROP TABLE #myxml
CREATE TABLE #myxml (id INT IDENTITY, actual_xml XML)
INSERT INTO #myxml
SELECT CAST(event_data AS XML)
FROM sys.fn_xe_file_target_read_file
('C:\New folder\FindProblemQueries*.xet',
'C:\New folder\FindProblemQueries*.xem',
NULL, NULL)
 
--10: INSERT INTO TEMP TABLE TO VIEW DATA COLLECTED
if object_id('tempdb..#ParsedData') is not null
DROP TABLE #ParsedData
CREATE TABLE #ParsedData (id INT, Actual_Time DATETIME, EventType sysname, ParsedName sysname, NodeValue VARCHAR(MAX))
INSERT INTO #ParsedData 
SELECT id,
DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), UTC_Time) AS Actual_Time,
EventType,
ParsedName,
NodeValue
FROM (
SELECT id,
A.B.value('@name[1]', 'varchar(128)') AS EventType,
A.B.value('./@timestamp[1]', 'datetime') AS UTC_Time,
X.N.value('local-name(.)', 'varchar(128)') AS NodeName,
X.N.value('../@name[1]', 'varchar(128)') AS ParsedName,
X.N.value('./text()[1]', 'varchar(max)') AS NodeValue
FROM [#myxml]
CROSS APPLY actual_xml.nodes('/*') AS A (B)
CROSS APPLY actual_xml.nodes('//*') AS X (N)
) T
WHERE NodeName = 'value'
DECLARE @SQL AS VARCHAR (MAX)
DECLARE @Columns AS VARCHAR (MAX)
SELECT @Columns=
COALESCE(@Columns + ',','') + QUOTENAME(ParsedName)
FROM
(
SELECT DISTINCT ParsedName
FROM #ParsedData
WHERE ParsedName <> 'tsql_stack'
) AS B
SET @SQL='
SELECT Actual_Time, EventType,' + @Columns + ' FROM
(
SELECT id, EventType, Actual_Time, ParsedName, NodeValue FROM
#ParsedData ) AS source
PIVOT
(max(NodeValue) FOR source.ParsedName IN (' + @columns + ')
)AS pvt order by actual_time, attach_activity_id'
EXEC (@sql) 

--Lists all the event session definitions that exist in SQL Server.
SELECT  * FROM sys.server_event_sessions 

--Returns a row for each action on each event of an event session.
SELECT  * FROM sys.server_event_session_actions 

--Returns a row for each event in an event session
SELECT  * FROM sys.server_event_session_events 

--Returns a row for each customizable column that was explicitly set on events and targets
SELECT  * FROM sys.server_event_session_fields 

--Returns a row for each event target for an event session.
SELECT  * FROM sys.server_event_session_targets 

--view the Extended Events equivalents to SQL Trace events using Query Editor
USE MASTER;
GO

SELECT DISTINCT
tb.trace_event_id,
te.name AS 'Event Class',
em.package_name AS 'Package',
em.xe_event_name AS 'XEvent Name',
tb.trace_column_id,
tc.name AS 'SQL Trace Column',
am.xe_action_name as 'Extended Events action'
FROM (sys.trace_events te LEFT OUTER JOIN sys.trace_xe_event_map em
ON te.trace_event_id = em.trace_event_id) LEFT OUTER JOIN sys.trace_event_bindings tb
ON em.trace_event_id = tb.trace_event_id LEFT OUTER JOIN sys.trace_columns tc
ON tb.trace_column_id = tc.trace_column_id LEFT OUTER JOIN sys.trace_xe_action_map am
ON tc.trace_column_id = am.trace_column_id
where tc.name like '%completed%'
ORDER BY te.name, tc.name

select * from sys.trace_Events

--To get the fields for all events 
select p.name package_name, o.name event_name, c.name event_field, 
c.type_name field_type, c.column_type column_type
from sys.dm_xe_objects o
join sys.dm_xe_packages p
on o.package_guid = p.guid
join sys.dm_xe_object_columns c
on o.name = c.object_name
where o.object_type = 'event'
order by package_name, event_name

SQL Server Extended Events via T-SQL. All the events that are available

--all the events that are available
SELECT p.name, c.event, k.keyword, c.channel, c.description FROM
   (
   SELECT event_package = o.package_guid, o.description, 
   event=c.object_name, channel = v.map_value
   FROM sys.dm_xe_objects o
   LEFT JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
   INNER JOIN sys.dm_xe_map_values v ON c.type_name = v.name 
   AND c.column_value = cast(v.map_key AS nvarchar)
   WHERE object_type = 'event' AND (c.name = 'CHANNEL' or c.name IS NULL)
   ) c LEFT JOIN 
   (
   SELECT event_package = c.object_package_guid, event = c.object_name, 
   keyword = v.map_value
   FROM sys.dm_xe_object_columns c INNER JOIN sys.dm_xe_map_values v 
   ON c.type_name = v.name AND c.column_value = v.map_key 
   AND c.type_package_guid = v.object_package_guid
   INNER JOIN sys.dm_xe_objects o ON o.name = c.object_name 
   AND o.package_guid = c.object_package_guid
   WHERE object_type = 'event' AND c.name = 'KEYWORD' 
   ) k
   ON
   k.event_package = c.event_package AND (k.event=c.event or k.event IS NULL)
   INNER JOIN sys.dm_xe_packages p ON p.guid = c.event_package
ORDER BY 
--keyword , channel, event,
c.description

--To view which actions are available, use the following query:
SELECT p.name AS 'package_name', xo.name AS 'action_name', xo.description, xo.object_type
FROM sys.dm_xe_objects AS xo
JOIN sys.dm_xe_packages AS p
   ON xo.package_guid = p.guid
WHERE 
--xo.object_type = 'action'
--AND 
(xo.capabilities & 1 = 0 
OR xo.capabilities IS NULL)
ORDER BY p.name, xo.name

--To view which predicates are available for an event, use the following query, 
--replacing event_name with the name of the event for which you want to add a predicate:
SELECT *
FROM sys.dm_xe_object_columns
--WHERE object_name = 'event_name'
--AND column_type = 'data'

--view which global predicate sources are available, use the following query:
SELECT p.name AS package_name, xo.name AS predicate_name
   , xo.description, xo.object_type
FROM sys.dm_xe_objects AS xo
JOIN sys.dm_xe_packages AS p
   ON xo.package_guid = p.guid
--WHERE xo.object_type = 'pred_source'
--ORDER BY p.name, xo.name

--To view the list of available targets, use the following query:
SELECT p.name AS 'package_name', xo.name AS 'target_name'
   , xo.description, xo.object_type 
FROM sys.dm_xe_objects AS xo
JOIN sys.dm_xe_packages AS p
   ON xo.package_guid = p.guid
--WHERE xo.object_type = 'target'
--AND (xo.capabilities & 1 = 0
--OR xo.capabilities IS NULL)
--ORDER BY p.name, xo.name

--The following example creates an Extended Events session named IOActivity that captures the following information:
--Event data for completed file reads, including the associated Transact-SQL text for file reads where the file ID is equal to 1.
--Event data for completed file writes.
--Event data for when data is written from the log cache to the physical log file.
--The session sends the output to a file target.

CREATE EVENT SESSION IOActivity
ON SERVER
ADD EVENT sqlserver.file_read_completed
   (
   ACTION (sqlserver.sql_text)
   WHERE file_id = 1),
ADD EVENT sqlserver.file_write_completed,
ADD EVENT sqlserver.databases_log_flush
ADD TARGET package0.asynchronous_file_target 
   (SET filename = 'c:\temp\xelog.xel', metadatafile = 'c:\temp\xelog.xem')

SQL Server Extended Events via T-SQL. Create the Event Session

-- Create the Event Session --
------------------------------
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRunningQuery')
DROP EVENT SESSION LongRunningQuery ON SERVER
GO
-- Create Event
CREATE EVENT SESSION LongRunningQuery
ON SERVER
-- Add event to capture event
ADD EVENT sqlserver.rpc_completed
(
-- Add action - event property ; can't add query_hash in R2
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
-- Predicate - time 1000 milisecond
WHERE (
duration > 1000 --by leaving off the event name, you can easily change to capture diff events
AND sqlserver.client_hostname <> 'A' --cant use NOT LIKE prior to 2012
)
--by leaving off the event name, you can easily change to capture diff events
),
ADD EVENT sqlserver.sql_statement_completed
-- or do sqlserver.rpc_completed, though getting the actual SP name seems overly difficult
(
-- Add action - event property ; can't add query_hash in R2
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
-- Predicate - time 1000 milisecond
WHERE (
duration > 1000
AND sqlserver.client_hostname <> 'A'
)
),
--adding Module_End. Gives us the various SPs called.
ADD EVENT sqlserver.module_end
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
WHERE (
duration > 1000000
--note that 1 second duration is 1million, and we still need to match it up via the causality
AND sqlserver.client_hostname <> 'A'
)
)
-- Add target for capturing the data - XML File
-- You don't need this (pull the ring buffer into temp table),
-- but allows us to capture more events (without allocating more memory to the buffer)
--!!! Remember the files will be left there when done!!!
ADD TARGET package0.asynchronous_file_target(
SET filename='C:\New folder\LongRunningQuery.xet', metadatafile='C:\New folder\LongRunningQuery.xem'),
-- Add target for capturing the data - Ring Buffer. Can query while live, or just see how chatty it is
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 SECONDS, TRACK_CAUSALITY = ON)
GO
 
-- Enable Event, aka Turn It On
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=START
GO