Session Target Metadata

With the ability to find the metadata for deployed session, events and actions firmly under the belt, the natural progression would lead one to find the metadata for the target(s) attached to a session.

Exploring the metadata for the core components of Extended Events Sessions can be an extremely fruitful undertaking. In the case of exploring the Event metadata for deployed sessions, one can also quickly discover the predicate for that event.

In the case of the final core component, Targets, the exploration into the metadata can be a little awkward. At least at first.

Once again, I will refer immediately back to the example code I have used throughout these dives into the metadata internals.

CREATE EVENT SESSION [demosession] ON SERVER
ADD EVENT sqlserver.auto_stats ( 
SET collect_database_name = ( 1 )--Event Fields Screen
ACTION (
  package0.event_sequence--Actions Screen
, sqlos.cpu_id
, sqlserver.database_id
, sqlserver.database_name 
)
WHERE ( [database_name] = N'AdventureWorks2014' )--Predicates Screen
)
ADD TARGET package0.event_file 
( SET filename = N'demosession'--Data Storage Screen
, max_file_size = ( 50 )--Data Storage Screen
, max_rollover_files = ( 6 )--Data Storage Screen
),
ADD TARGET package0.ring_buffer
(SET max_events_limit=(666)--Data Storage Screen
,max_memory=(65536)--Data Storage Screen
,occurrence_number=(3)--Data Storage Screen
)
WITH ( EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS--Advanced Screen
, MAX_DISPATCH_LATENCY = 5 SECONDS--Advanced Screen
, MAX_EVENT_SIZE = 2048 KB--Advanced Screen
, MEMORY_PARTITION_MODE = PER_NODE--Advanced Screen
, TRACK_CAUSALITY = ON--Opening Screen
, STARTUP_STATE = ON--Opening Screen
);
GO

And now, with a little less noise and just a focus on the targets of the session.

/* just the targets */CREATE EVENT SESSION [demosession] ON SERVER
...
ADD TARGET package0.event_file 
( SET filename = N'demosession'--Data Storage Screen
, max_file_size = ( 50 )--Data Storage Screen
, max_rollover_files = ( 6 )--Data Storage Screen
),
ADD TARGET package0.ring_buffer
(SET max_events_limit=(666)--Data Storage Screen
,max_memory=(65536)--Data Storage Screen
,occurrence_number=(3)--Data Storage Screen
)
...
GO

In this condensed version of the code, all that is present (of interest) is the code necessary to create two targets for this session as it gets deployed. Quite similar to how one can explore the metadata of the session, events, and actions, one merely needs to query the catalog view for the targets – sys.server_event_session_targets. Or so it would seem. Let’s start with the base query once again.

SELECT ses.name AS SessionName,sest.name AS Targettype, sest.target_id, sest.package, sest.module
FROM sys.server_event_session_targets sest
INNER JOIN sys.server_event_sessions ses
ON sest.event_session_id = ses.event_session_id
WHERE ses.name = 'demosession';

For the “demosession” session that has been deployed, I will receive the following results:

Take note of the name column from sys.server_event_session_targets. In my query, I have renamed it to Targettype. If this is compared back to the original script, it becomes apparent that the target type or name of the target type is what is used in this particular view. These results represent essentially the entirety of the catalog view (minus the event_session_id column which I used to join back to sys.server_event_sessions in order to filter by session name).

Unlike the metadata for the session, events and actions, the target metadata view is less useful. That is, by itself. Sure I can find out the types of targets attached to the session. But this view directly does not give me the pertinent information such as the file name when a file target is used. With that said, the information is available and I will be covering that in the next article.

As for further use out of this particular view, there is still some metadata to be gained. Let’s dive just a bit more.

SELECT ses.name AS SessionName,sest.name AS Targettype, sest.target_id, sest.package
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('\',REVERSE(olm.name))-1)) AS DLLName
FROM sys.server_event_session_targets sest
INNER JOIN sys.server_event_sessions ses
ON sest.event_session_id = ses.event_session_id
INNER JOIN sys.dm_xe_objects xo
ON xo.name = sest.name
AND xo.object_type = 'target'
INNER JOIN sys.dm_xe_packages xp
ON xp.module_guid = sest.module
AND xp.name = sest.package
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address
WHERE ses.name = 'demosession';

For now, this is still pretty basic information. As I said just a moment ago, the more fruitful information is not readily available direct from this view. The exploration of reaching that information will be revealed in the next article.

It is important to discuss this particular view to try and help prevent a bit of frustration. I would personally think that the view should expose the pertinent metadata for the session. This is one case where extra steps need to be taken and an exploration into a different view will be required – a view that does not necessarily equate to Target.

In this short tutorial, I covered a quick means to view the target types that are deployed for a specific session. This is just one of a long series of articles on Extended Events. Explore the other articles here.

SET Operations and Metadata October 2, 2015
Session Action Metadata September 30, 2015
Session Event Metadata September 29, 2015
Deployed Session Settings September 28, 2015
Backup Deployed Sessions October 5, 2015

Session Target Metadata