THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

An XEvent a Day (4 of 31) – Querying the Session Definition and Active Session DMV’s

Yesterdays post, Managing Event Sessions, showed how to manage Event Sessions in Extended Events 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 Catalog Views and how to find information about the Active Event Sessions that exist using the Active Session DMV’s.

Session Definition Catalog Views

(Note: The following section has been changed based on feedback provided by Mike Wachal, who works on the Extended Events team at Microsoft.  In the original posting, this section refered to the Session Definition DMV's, which Mike pointed out is not accurate.  Per Mike, "Session Definition DMV’s that you describe are not DMVs, they are part of the system catalog. The system catalog is persisted storage while DMVs are generated at runtime by querying the state of the server."  To keep the information in this series as technically accurate as possible I have updated all references to these being DMV's to instead be Catalog Views, views, or system views.  Many thanks to Mike for pointing out this discrepancy.)

The Session Definition Catalog Views 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 system views 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 view 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 view, 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 s

sys.server_event_session_events

The sys.server_event_session_events view provides information about the specific Events that are defined in the Event Sessions maintained by the Extended Events Engine.  This view 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 view 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
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 view 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 view to the sys.server_event_session_events view.

-- 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
JOIN sys.server_event_session_events AS sese
   
ON ses.event_session_id = sese.event_session_id
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 view 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 view to the sys.server_event_sessions view.

-- Get target information
SELECT
  
ses.name AS session_name,
  
sest.name AS target_name
FROM sys.server_event_sessions AS ses
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 view 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 view to the sys.server_event_session_targets view.

-- 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
JOIN sys.server_event_session_targets AS sest
        
ON ses.event_session_id = sest.event_session_id
JOIN sys.server_event_session_fields AS sesf
    
ON sest.event_session_id = sesf.event_session_id
   
AND sest.target_id = sesf.OBJECT_ID

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
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
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
JOIN sys.dm_xe_session_events AS e
    
ON s.address = e.event_session_address
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
JOIN sys.dm_xe_session_targets AS t
    
ON s.address = t.event_session_address
JOIN sys.dm_xe_session_events AS e
    
ON s.address = e.event_session_address
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.

Published Saturday, December 04, 2010 10:00 AM by Jonathan Kehayias

Comments

No Comments
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement