Actions In Extended Events

Comments: 1 Comment
Published on: September 15, 2015

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:

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).

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.

1 Comment - Leave a comment
  1. […] Actions In Extended Events September 15, 2015 […]

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
September 2015
M T W T F S S
« Aug   Oct »
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp

  • @live_sql: #sqlhelp Is there a way to allow users attach DB's using the GUI and them not being SA. I crested custom server roles but still no GUI?
  • @joachimpr: Are your reports at work always taking forever to show you data? This could be the reason why! #SQLServer #sqlhelp
  • @NickyvV: Did anyone ever find a solution for memory consumtion of PP in Excel 2010 that @Kjonge made for 2013 and SSAS? #sqlhelp #ssashelp
  • @Ranjeeth: Can I have DBSer01, DBServ02 having replicas in DBServ03(2 AGs, diff clusters one target). #sqlhelp #alwayson trying to save testing time.
  • @jdanton: @Sir_NiN_Sir both free, but dev=enterprise, feature wise, express, much more limited. For learning, I’d go dev, or just use Docker. #sqlhelp
  • @Sir_NiN_Sir: How's different MSSQL Developer edition and Express edition? both edition for free licensed or not? If I need to practice t-sql.#sqlhelp
  • @jdanton: @Spinner_ Nope. Added a bunch of features to it, as it of SP1 2016. #sqlhelp
  • @Spinner_: #sqlhelp are Microsoft's deprecating sql server express?
  • @SQLHA: @ke0mms Would need to know about your issue before I could even think about giving an answer. #sqlhelp
  • @SQLHA: @ke0mms What do you mean by availability server? That's not a "thing" :) #sqlhelp

Welcome , today is Wednesday, July 26, 2017