Today will be the first of a few introductory level posts about Extended Events.
When dealing with Extended Events, there are a few ways to take a look some of the metadata. Not all metadata is created equal when dealing with Extended Events. Some of the metadata is pertinent to a running XEvent session and some is pertinent to a deployed session.
If you have a session that has been configured/deployed to an instance of SQL Server, you will want to look at the Catalog Views – especially if that session is not running. Today, I will take a quick look at the catalog views. Yes – quick!
Catalog Views
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name FROM sys.all_objects o WHERE o.is_ms_shipped = 1 AND o.type = 'V' AND o.name LIKE '%server_event_ses%';
Straight forward, catalog views for Extended Events have a very common naming pattern – server_event_sessions%. The rest of the predicate is just for eye candy.
Among the list returned by that query we will see a list of results such as this:
Use the following catalog views to obtain the metadata that is created when you create an event session.
| Name | Description |
|---|---|
| sys.server_event_sessions | Lists all editable, deployed event sessions. |
| sys.server_event_session_actions | List of actions on each event of an event session. |
| sys.server_event_session_events | List of each event in an event session. |
| sys.server_event_session_fields | List of customizable columns that were set on events and targets. |
| sys.server_event_session_targets | List of event targets for an event session. |
Beyond just a list of the Catalog views, I find it useful to combine some of these to garner the information that would be useful in querying data from the session or even to possibly rebuild the event session if needed.
One may want to figure out where the data for an event session is being stored. The first inclination may be to look at the server_event_session_targets catalog view. Doing that could cause a little frustration since you will only see some binary data and the type of target attached to the session.
But, playing with the views a little more and becoming a little more familiar with the data presented by the views, you may notice that the server_event_session_fields suddenly becomes more attractive. Why? Well, because it contains the filepaths that would be necessary to query session data – if the session is deployed to a file target. Suddenly, hope is not lost.
To get that data, one would need to write a query like this:
SELECT ses.name AS SessionName, esf.value AS SessionFileLocation FROM sys.server_event_session_fields esf INNER JOIN sys.server_event_sessions ses ON ses.event_session_id = esf.event_session_id WHERE esf.name = 'filename';
This demonstrates one of the more frustrating (not by far the most frustrating part though) things about dealing with extended events. That is the entity attribute value model employed to store metadata. This is nothing new within SQL Server (e.g. agent jobs, schedules, or even sysobjvalues – internally). Sadly that doesn’t make dealing with the metadata terribly easy – but knowing can make it more manageable.
So, my recommendation here is to play around a bit and start to get to know the catalog views as they pertain to extended event metadata.
Stay tuned for more posts like this. As was mentioned, this is the first in what will be a long series on Extended Events.
