The next component to discuss is Actions. I have discussed various aspects of events, schemas, categories and packages as some of the core concepts of Extended Events. Actions, is another of the core concepts.
In grammar, an action invokes memories of verbs and “to do” something. If you recall from the quick definition in the article on Extended Events Objects, an action is “a means to attach additional data to the payload of an event.”
I like to think of these actions as highly efficient internal functions. I am talking about the functions like DB_NAME(), DB_ID(), @@SERVERNAME. Think of how quickly these execute within SQL Server. Not only are they quick, but they, in essence, attach additional data to the payload (your query results). Make sense? Good!
Since actions are an object type within Extended Events, the access path to the metadata about them is through sys.dm_xe_objects. In order to explore the possible actions, one simply needs to execute a basic query akin to the following:
SELECT xp.name AS PackageName , xo.name AS ActionName , xo.description AS ObjDescription , xo.capabilities_desc FROM sys.dm_xe_packages AS xp INNER JOIN sys.dm_xe_objects AS xo ON xp.guid = xo.package_guid WHERE ( xp.capabilities IS NULL OR xp.capabilities & 1 = 0 ) AND ( xo.capabilities IS NULL OR xo.capabilities & 1 = 0 ) AND xo.object_type = 'action' ORDER BY ActionName, PackageName;
Notice the highlighted actions in the following image:
Do those look familiar? These are very consistent with the SQL Server functions previously mentioned. These actions do the same sort of thing as the functions in SQL Server do – they attach the database name (or id) to the results of the query (or the payload of the event).
Granted, not all actions are as simple as this. And not all actions are harmless. Look at the debug_break in the previous image. That one looks pretty dangerous based on the description and should be avoided unless there is a very good reason and maybe CSS is on the line at the time. MAYBE!
Within Extended Events, there are 50 actions in SQL Server 2014 (similar numbers in 2016). Of these, 48 are usable for user created event sessions while two are marked as private.
A word of advice with actions, as alluded to with the debug_break action, not all actions come at the same cost. Some will add more overhead to the server than others. And not all actions will work on all events. Furthermore, it is worth noting that there are two basic groupings of Actions. The first group is the type that collects data and attaches that data to the payload. And, to belabor the debug_break action, there are actions that perform some other type of task. I call these the effectors (mostly because there really isn’t a good name and they do effect an action that has greater reach than to collect and attach data to a payload).
SELECT xp.name AS PackageName , xo.name AS ActionName , xo.description AS ObjDescription , CASE WHEN xo.type_name = 'null' THEN 'Effector' ELSE 'Collector' END AS ActionType FROM sys.dm_xe_packages AS xp INNER JOIN sys.dm_xe_objects AS xo ON xp.guid = xo.package_guid WHERE xo.object_type = 'action' AND ( xp.capabilities IS NULL OR xp.capabilities & 1 = 0 ) AND ( xo.capabilities IS NULL OR xo.capabilities & 1 = 0 ) ORDER BY ActionName, PackageName;
The results of this query will show there are only three effectors. The debug_break (as previously mentioned), and then two that will create dump files.
Between the quantity of actions and the range of functionality they provide, these can be an invaluable tool as you become more and more involved with Extended Events.
While this has been an introduction to the core concept of actions, there is more that will be covered as we build on these concepts over time.