Today I received an email from someone who attended my Extended Event session on Boston last fall. He was asking what table stores information about what Extended Events exist on the server. The answer is sys.dm_xe_objects data management view.
In this DMV, obviously there are a number of columns. This post will focus mostly on the object_type column. This column has a number of potential values, including Event, Target and Action. When setting up a SQL Server Extended Event session these are a few of the key items that need to be filled out.
Let’s just take a few moments to identify what these are.
Target – This is where the data captured by the Extended Event session will be stored. Many times you will find the target to be the event_file target, although from time to time you will find the data going to other targets. The data will be stored in an XML format. See the image below.
Actions – These are also referred to as Global Fields. These are the fields that many of the events have in common. You can decide what data you would like to collect. These differ from the Event Fields in the sense that are shared with all the events. Event Fields are data points that are specific to that event. Sometimes you will find a particular data point in both the Actions and Event Fields.
Events – This is the focus of this post. Events are things that happen on the server and you would like to capture the what, when and other information about the event. As each release of SQL Server comes out, there are more and more events included. The image below is an example of some of the events you might want to use. In this case they are related to Deadlocks.
Now back to the purpose of this post, where is this information found in the system tables. As stated above, the information can be found in the sys.dm_xe_objects DMV.
If you run this query you will see all the events for that instance. When I run this on my SQL Server 2017 server I get 1,505 events. Although, your results might be slightly different depending on version.
SELECT Name
, Description
FROM sys.dm_xe_objects
WHERE object_type = ‘event’
Here what the data set looks like.
There are really two columns that are important for this post, the Name and Description columns.
Thank you for stopping by and a big thank you to the attendee that emailed me the question.