Extended Event related sytem catalog views and DMVs

  • Article

We add extended event (short for XEVENT) to SQL Server 2008. Along with this feature, we provide a set of system catalog views and DMVs to allow user to monitor extended event objects and sessions. For the usage of XEvent, please check the online help.

When I do the testing for XEvent, I use those catalog views and DMVs to verify the status of my event sessions or chose available events for my event sessions. It would be helpful for you if you have a high level overview for those views and DMVs.

Those views and DMVs can be divided into 3 categories:

  1. XEvent packages and objects
  2. sessions
  3. online only sessions

Category 1: XEvent packages and objects

Now we take a look at the first category. There are only DMVs in this category.

· sys.dm_xe_packages will show you the packages loaded in the system.

  • sys.dm_xe_objects will show you the objects available in the system. object_type will tell you the type of the object, and do a join with sys.dm_xe_packages on package_guid will give you the package the object is in. if capabilities_desc contains "private", it can be only used by system internally.

· sys.dm_xe_object_columns will show youthe columns for events and targets.

· sys.dm_xe_map_values : If you see a column has a “strange” type, it might be a map, check it up in this DMV, if it’s a map type, it will show you a meaningful string for each value. For example, value 3 for lock_mode is S and lock_resource_type 5 means OBJECT. So if you captured an lock_acquire event with lock_mode = 3 and lock_resource_type=5, you will know it’s an S lock on an object.

Category 2: Views for Sessions

Following system catalog views will tell you the information about all sessions – no matter the session is online or offline. When a session is just created or stopped, it is offline, once you start it, it’s considered online.

· sys.server_event_sessions will show you all the sessions (both online and offline).

· sys.server_event_session_targets will show you all the targets in the sessions.

· sys.server_event_session_events will show you all the events you enabled in the sessions.

· sys.server_event_session_actions will show you all the actions you attached to the enabled events.

· sys.server_event_session_fields will show you the values that was provided for the customizable fields for the events and targets in the sessions.

Category 3: DMVs for online sessions only

Following DMVs only contain information for online session (the session that you started).

· sys.dm_xe_sessions will show you all the online sessions

· sys.dm_xe_session_targets will show you information about targets enabled in those sessions.

· sys.dm_xe_session_events will show you information about events enabled in those sessions.

· sys.dm_xe_session_event_actions will show you information about actions appended to events in the sessions.

· sys.dm_xe_session_object_columns will show you the values you provided for customizable columns for the events and targets in the sessions.