Extended Events Series (4 of 31) – Querying the Session Definition and Active Session DMV’s

Yesterdays post, Managing Event Sessions, showed how to manage Event Sessions inside the Extended Events framework in SQL Server. In today's post, we’ll take a look at how to find information about the defined Event Sessions that already exist inside a SQL Server using the Session Definition DMV’s and how to find information about the Active Event Sessions that exist using the Active Session DMV’s.

Extended Events Session Definition DMV’s

The Session Definition DMV’s provide information about the Event Sessions that have been defined in the Extended Events Engine and may or may not be actively running against the SQL Server Instance.  Five DMV’s provide information about the Event Sessions that exist inside of the Extended Events Engine; sys.server_event_sessions, sys.server_event_session_events, sys.server_event_session_actions, sys.server_event_session_targets, and sys.server_event_session_fields.

sys.server_event_sessions

The sys.server_event_sessions DMV provides information about the Event Sessions that exist inside of the Extended Events Engine.  The Session level options for the Event Session can be retrieved from this DMV, to determine how the Event Session is configured.

-- Session level information for current Event Sessions
SELECT 
   s.name,
   s.max_memory,
   s.event_retention_mode_desc,
   s.max_dispatch_latency,
   s.max_event_size,
   s.memory_partition_mode_desc,
   s.track_causality,
   s.startup_state
FROM sys.server_event_sessions AS s;

sys.server_event_session_events

The sys.server_event_session_events DMV provides information about the specific Events that are defined in the Event Sessions maintained by the Extended Events Engine.  This DMV also returns the defined Predicates for the Events that are included for collection in Event Sessions on the server.  The event_session_id column can be used to join this DMV to sys.server_event_sessions as shown below.

-- Get events in a session
SELECT 
   ses.name AS session_name,
   sese.package AS event_package,
   sese.name AS event_name,
   sese.predicate AS event_predicate
FROM sys.server_event_sessions AS ses
INNER JOIN sys.server_event_session_events AS sese
    ON ses.event_session_id = sese.event_session_id;

sys.server_event_session_actions

The sys.server_event_session_actions DMV contains one row for each of the Actions that have been added to an Event in an Event Session.  If the same Action was added to multiple Events, there would be a separate row per Event and Action pair in the Event Session.  The event_session_id and event_id columns are used to join this DMV to the sys.server_event_session_events DMV.

-- Get actions 
SELECT 
   ses.name AS session_name,
   sese.package AS event_package,
   sese.name AS event_name,
   sese.predicate AS event_predicate,
   sesa.package AS action_package,
   sesa.name AS action_name
FROM sys.server_event_sessions AS ses
INNER JOIN sys.server_event_session_events AS sese
    ON ses.event_session_id = sese.event_session_id
INNER JOIN sys.server_event_session_actions AS sesa
     ON ses.event_session_id = sesa.event_session_id
    AND sese.event_id = sesa.event_id;

sys.server_event_session_targets

The sys.server_event_session_targets DMV contains one row for each of the configured Targets that are defined for an Event Session.  The event_session_id column is used to join this DMV to the sys.server_event_sessions DMV.

-- Get target information
SELECT 
   ses.name AS session_name,
   sest.name AS target_name
FROM sys.server_event_sessions AS ses
INNER JOIN sys.server_event_session_targets AS sest
   ON ses.event_session_id = sest.event_session_id;

sys.server_event_session_fields

The sys.server_event_session_fields DMV contains one row for each of the configured options for each Target defined for an Event Session.  The event_session_id and target_id columns are used to join this DMV to the sys.server_event_session_targets DMV.

-- Get target option information
SELECT 
   ses.name AS session_name,
   sest.name AS target_name,
   sesf.name AS option_name,
   sesf.value AS option_value
FROM sys.server_event_sessions AS ses
INNER JOIN sys.server_event_session_targets AS sest
   ON ses.event_session_id = sest.event_session_id
INNER JOIN sys.server_event_session_fields AS sesf
   ON sest.event_session_id = sesf.event_session_id
   AND sest.target_id = sesf.object_id;

Extended Events Active Session DMV’s

The Active Session DMV’s provide information about the Event Sessions that are currently in a started state on a SQL Server Instance.  Five DMV’s make up the group of Active Session DMV’s; sys.dm_xe_sessions, sys.dm_xe_session_events, sys.dm_xe_session_event_actions, sys.dm_xe_session_targets, and sys.dm_xe_session_object_columns.

sys.dm_xe_sessions

The sys.dm_xe_sessions DMV contains one row for each active Event Session (STATE=START) in the SQL Server Instance, and provides information about the configuration of the Session buffers.  Information about the size, and number of buffers is returned for the regular sized and large sized buffers associated with the Event Session.  An Event Session will have large sized buffers when the MAX_EVENT_SIZE configured is larger than the regular buffer size.  In general, most Events will be buffered to the regular buffers.  Information about event loss associated with the buffers being full and buffers that are full and pending dispatch is also contained in this DMV.

-- Look at Active Session Information
SELECT 
   s.name, 
   s.pending_buffers,
   s.total_regular_buffers,
   s.regular_buffer_size,
   s.total_large_buffers,
   s.large_buffer_size,
   s.total_buffer_size,
   s.buffer_policy_flags,
   s.buffer_policy_desc,
   s.flags,
   s.flag_desc,
   s.dropped_event_count,
   s.dropped_buffer_count,
   s.blocked_event_fire_time,
   s.create_time,
   s.largest_event_dropped_size
FROM sys.dm_xe_sessions AS s;

sys.dm_xe_session_targets

The sys.dm_xe_session_targets DMV will contain one row for each Target that exists for an active Event Session.  Information about the Target such as the Target name (ring_buffer, pair_matching, etc.) and Target execution statistics are returned by this DMV.  For memory resident Targets, the target_data columns will return an XML document containing the information about the Events that have been dispatched to the Target and are still available.  For persisted Targets, the target_data column still contains an XML document, but only statistics about the Target will be contained in the document.  More specific information about the target_data column will be provided in the next week as we look at each Target individually.  The event_session_address column is used to join this DMV to the address column in the sys.dm_xe_sessions DMV.

-- Target information for a running session
SELECT 
   s.name AS session_name,
   t.target_name AS target_name,
   t.execution_count AS execution_count,
   t.execution_duration_ms AS execution_duration,
   CAST(t.target_data AS XML) AS target_data
FROM sys.dm_xe_sessions AS s
INNER JOIN sys.dm_xe_session_targets AS t
   ON s.address = t.event_session_address;

sys.dm_xe_session_events

The sys.dm_xe_session_events DMV contains one row for each Event that is defined in an Active Event Session.  The predicate definition for each event, if defined, is included in the output of this DMV.  However, the predicate is not the same as returned by sys.server_event_session_events if standard logical operators were used in the Event definition.  Instead the Predicates are converted to use Predicate Comparators in text form, and for complex Predicates, the length can exceed the allowable output.  When this occurs, “Predicate too large for display” will be returned by the DMV.  The event_session_address column is used to join this DMV to the address column in the sys.dm_xe_sessions DMV.

-- Event Information for a running session
SELECT s.name AS session_name,
       e.event_name AS event_name,
       e.event_predicate AS event_predicate
FROM sys.dm_xe_sessions AS s
INNER JOIN sys.dm_xe_session_events AS e
     ON s.address = e.event_session_address;

sys.dm_xe_session_event_actions

The sys.dm_xe_session_event_actions DMV contains one row for each Action that is defined on an Event in an Active Event Session.  If the same Action is defined on multiple Events in the Event Session, one row will be returned for each Event/Action pair.  The event_session_address and event_name columns are used to join this DMV to the address column in the sys.dm_xe_session_events DMV.

-- Event Information with Actions for a running session
SELECT s.name AS session_name,
       e.event_name AS event_name,
       e.event_predicate AS event_predicate,
       ea.action_name AS action_name
FROM sys.dm_xe_sessions AS s
INNER JOIN sys.dm_xe_session_events AS e
     ON s.address = e.event_session_address
INNER JOIN sys.dm_xe_session_event_actions AS ea
     ON e.event_session_address = ea.event_session_address
    AND e.event_name = ea.event_name;

sys.dm_xe_session_object_columns

The sys.dm_xe_session_object_columns DMV contains one row for each of the configured options for a Target that is defined in an Active Event Session, as well as one row for each of the customizable Data Elements for a Event that is defined in an Active Event Session.  The event_session_address and event_name columns are used to join this DMV to the address column in the sys.dm_xe_session_events DMV.  The event_session_address and target_name columns are used to join this DMV to the address column in the sys.dm_xe_session_targets DMV.

-- Configurable event and target column information
SELECT DISTINCT s.name AS session_name, 
       oc.OBJECT_NAME, 
       oc.object_type, 
       oc.column_name, 
       oc.column_value
FROM sys.dm_xe_sessions AS s
INNER JOIN sys.dm_xe_session_targets AS t
     ON s.address = t.event_session_address
INNER JOIN sys.dm_xe_session_events AS e
     ON s.address = e.event_session_address
INNER JOIN sys.dm_xe_session_object_columns AS oc
     ON s.address = oc.event_session_address
       AND ((oc.object_type = 'target' AND t.target_name = oc.object_name) 
       OR (oc.object_type = 'event' AND e.event_name = oc.object_name));

What’s next?

Now that we understand how to query the Extended Events Metadata, how to manage Event Sessions, and how to determine what Event Sessions have been created in a SQL Server, the next week of this series will focus on the specific targets of Extended Events and how to query the data contained in them.  The next post will look at the ring_buffer target and the data that it exposes.