In the previous article, I wrote about getting into some of the metadata to take a peek at session metadata relevant to deployed Extended Event sessions. Today, I will continue talking about metadata. One difference being that today I will focus on diving into the metadata of running sessions.
Running session metadata, while it can be still be viewed in the catalog views, is exposed via DMVs. Putting it a slightly different way, if I query DMVs that are related to extended events, I will only see metadata for running sessions.
DMV
Similar to the catalog views, there is a consistent naming pattern and a simple query to help us figure out what is out there related to Extended Events.
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name FROM sys.all_objects o WHERE o.is_ms_shipped = 1 AND o.type = 'V' AND o.name LIKE '%dm_xe%';
That little query will show us a list of DMVs like the following – descriptions added.
| Name | Description |
|---|---|
| sys.dm_xe_objects | List of objects exposed by an event package. |
| sys.dm_xe_object_columns | schema information for the objects. |
| sys.dm_xe_packages | Lists all the packages registered with the Extended Events engine. |
| sys.dm_xe_sessions | Lists the Running Extended Events sessions. |
| sys.dm_xe_session_targets | Information about session targets. |
| sys.dm_xe_session_events | Information about events tied to a running session. |
| sys.dm_xe_session_event_actions | Information about the “actions” tied to an event in a running session. |
| sys.dm_xe_map_values | Provides a mapping of internal numeric keys to human-readable text. |
| sys.dm_xe_session_object_columns | Shows the configuration values for objects that are bound to a session. |
Since much of this is information about running sessions, it becomes very useful in trying to query the metadata for those sessions. However, if the session is not running and you don’t realize certain DMVs (e.g. sys.dm_xe_session* named DMVs) are only applicable to running sessions – you could end up being rather frustrated.
So, in a similar vein to the query presented in the previous article; if I want to query the DMVs to find where I might be storing session data, I would run something like the following.
SELECT xs.name AS SessionName,xst.target_data FROM sys.dm_xe_sessions xs INNER JOIN sys.dm_xe_session_targets xst ON xs.address = xst.event_session_address WHERE xst.target_name = 'event_file' ;
This query will show us the file location for each of the deployed sessions that have data saved to a file on the operating system (as opposed to memory). But look at the results real quick. The target_data is more than just the file path. Sure you can manually parse that information from the results. Or you could go back to just using the query provided in the previous article. Or, get daring and try a little XML parsing – with this query.
SELECT td.SessionName, td.target_data
, FileEvent.FileTarget.value('@name','varchar(1000)') AS FileTargetName
FROM (
SELECT xs.name AS SessionName,CONVERT(XML,xst.target_data) AS target_data
FROM sys.dm_xe_sessions xs
INNER JOIN sys.dm_xe_session_targets xst
ON xs.address = xst.event_session_address
WHERE xst.target_name = 'event_file'
) td
CROSS APPLY td.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
;And here we have yet another one of those frustrating things when dealing with Extended Events. Not only do the DMVs just show the information as it pertains to running sessions, but you also have to deal with XML now. Don’t think this is the last of dealing with XML in XEvents – not by a long shot!
This is just one quick example of the type of data that can be viewed from the running session metadata. Imagine wanting to figure out how long the session has been running. Or maybe you want to know if there is something that might be blocking an event from firing. The data in these DMVs can help you access that kind of data – quickly!
Stay tuned for more XE content soon to come!
