XEvent Session Supporting Cast

So far, in this series, I have discussed the means to view the metadata associated with Extended Events. I also demonstrated some quick and simple means to retrieve some metadata via the catalog views and DMVs.

Beyond the metadata in the the catalog views and DMVs, there are a few more objects that come in handy when dealing with Extended Events. I call these the supporting cast. This cast would come in the form of yet another DMV and a couple of tables. Granted this is not the end of the tools that can be used when dealing with Extended Events, the remaining tools would be classified differently and are going to be saved for another time.

Supporting Cast

Tables

First up in the supporting cast role would be the group of tables. These are not your ordinary tables. These tables could be your best friend as you work to convert yourself away from server side traces and profiler traces. These tables contain conversion (and therefor static) data to help translate from server trace events to extended event events (yes it is extended event events – sounds redundant but that is what it is).

The tables you will come to love are:

Name Description
trace_xe_event_map One row per Extended Events event that is mapped to a SQL Trace event class.
trace_xe_action_map One row per Extended Events action that is mapped to a SQL Trace column ID.

And, as you might have imagined, the quick and easy way to view what tables might be related to extended events is fairly straight forward as well.

If I query the trace_xe_event_map table, I will see the mapping between 138 trace event ids to the extended event events along with the package through which that event is exposed for extended events. Don’t worry about packages just yet, I will discuss that at a later time. If I do the same thing for the action_map table, I will see a similar result.

As I mentioned previously, these tables will be essential in converting any of those old profiler or server side traces into the XEvent platform.

DMV

Of all of the XEvent DMVs, all but one are used for the metadata of running sessions. The remaining DMV deals more with troubleshooting and session performance than it does with metadata. The name of the DMV is sys.dm_os_dispatcher_pools.

Why is this DMV thrown down to the supporting cast role? Well, despite (current) documentation, this DMV is not exclusively used by Extended Events. You will see dispatcher pools and threads for various different background tasks like XEvents, In-Memory OLTP (Hekaton), and filestream – to name a few.

dispatcherpool

The preceding results could be viewed using a query similar to the following:

If it seems there may be a problem with the server and you just don’t see events dumping into the target as quickly as you think they should, you may want to check this DMV. In the DMV, one will find things like queue_length and dispatcher_waiting_count. A high waiting_count would indicate no events are firing. While a high queue_length would indicate there are many events backing up while waiting to be processed.

Stay tuned for more quick tidbits as I continue this series.

1 Comment - Leave a comment
  1. […] XEvent Session Supporting Cast September 4, 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