In a previous article, I introduced the core concept of Actions. In that article, I explained that an Action is a means to attach additional data from the stream to the event payload.
Recently, I have been covering various aspects of how to build an event session as well as how to investigate metadata related to any session that might be deployed to the server. The articles discussing these aspects can be found in the recap (Table of Contents) of the series.
In this segment, I will continue to build on the core concepts previously discussed. Knowing how to access the deployed session metadata and the event metadata attached to that session, I will now begin the dive into how to find the same relevant data for any actions that may be deployed.
Recall that an Action is tied directly to an Event. This means that each Event in the session can have one or more, or no actions attached to it. This contributes to the configurability of Extended Event Sessions.
Session Action Metadata
Resorting back to a common theme, I will re-introduce the example script used over the course of the past several articles.
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, that same script, with just the pertinent pieces to see the Actions.
/* just the actions */CREATE EVENT SESSION [demosession] ON SERVER ADD EVENT sqlserver.auto_stats ( ... ACTION ( package0.event_sequence--Actions Screen , sqlos.cpu_id , sqlserver.database_id , sqlserver.database_name ) ... ) ... GO
Looking at this example with just the necessary pieces for the Actions, it should stand out that I did not exclude the addition of the Event to the session. This is to help reinforce that an action has to be tied to an Event in order to be deployed to a session. In this example, I have assigned four actions to the auto_stats event.
If I have the script to build this session, that is great. If I have a GUI to access the Session data, great. If I have neither of those at my disposal, how do I get to the metadata for these deployed Actions? In response to that need, there is a catalog view that exposes this metadata – sys.server_event_session_actions. And to get at the data, a query such as the following will do the trick at the base level:
SELECT ses.name AS SessionName,sesa.name AS ActionName, sesa.event_id, sesa.package, sesa.module FROM sys.server_event_session_actions sesa INNER JOIN sys.server_event_sessions ses ON sesa.event_session_id = ses.event_session_id WHERE ses.name = 'demosession';
I have joined this view to the sys.server_event_sessions catalog view in order to filter this down to the specific deployed session I want to investigate. In the results, I can see which actions have been deployed as well as an id that correlates to the Event for which the Action has been deployed. In addition, like was seen with the sys.server_event_session_events view, the package that exposes the specific action is listed with the corresponding module guid.
This is a good basic query to begin the discovery process into deployed Action metadata. As I showed with the deployed Event metadata, there is more to discover. Let’s step it up a bit to find more about these deployed Actions.
SELECT ses.name AS SessionName,sesa.name AS ActionName, sese.name AS EventName, sesa.package
, xo.type_name, xo.type_size, xo.description
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('',REVERSE(olm.name))-1)) AS DLLName
FROM sys.server_event_session_actions sesa
INNER JOIN sys.server_event_sessions ses
ON sesa.event_session_id = ses.event_session_id
INNER JOIN sys.server_event_session_events sese
ON sesa.event_session_id = sese.event_session_id
AND sesa.event_id = sese.event_id
INNER JOIN sys.dm_xe_objects xo
ON xo.name = sesa.name
AND xo.object_type = 'action'
INNER JOIN sys.dm_xe_packages xp
ON xp.module_guid = sesa.module
AND xp.name = sesa.package
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address
WHERE ses.name = 'demosession';The results for this example query would be as follows:
While some of this is more informational at first glance, it could all be somewhat useful at one time or another. In this query, I chose to return results for the data type of each of the actions in addition to the dll and package that happen to be the source of the action. In addition, I can also see what the deployed Action is supposed to do (e.g. the description of the Action).
In the same fashion as the deployed Events, queries such as I just demonstrated can be used to create tools for the DBA toolbox.
Stay tuned for more in this series and the world of Extended Events. If you have missed any of the tutorials and articles, you can catch up here.
