Skip to content

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