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:

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.

SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name 
FROM sys.all_objects o
WHERE o.is_ms_shipped = 1
AND o.type = 'u'
AND o.name LIKE '%xe%';

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.

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

SELECT type
, name
, dispatcher_count
, dispatcher_ideal_count
, queue_length
FROM sys.dm_os_dispatcher_pools

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.