Actions In Extended Events

, 2015-09-15

xe_actionThe 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!

Actions

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:

action_sample

 

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads