SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Extended Events – Events

db_eventAfter thrilling dives into the XEvent Core Concepts of packages, channels and objects, today we have the core concept of “Event.”

If you recall, an event is some point of interest that may or may not occur within an application. Obviously, if the event is triggered, then that point of interest has occurred.

We have also seen that events are categorized/classified via what are called channels and keywords. This classification can be a big help when trying to find groups of events that may be related.


An event carries with it the payload pertinent to the occurrence of that point of interest – when the event fires. This payload would be called “data” in a more friendly manner.

Before getting into the data too much, let’s take a look at the simplest of approaches to look at the available events within SQL Server.

SELECT xo.name AS EventName, xo.description
	FROM sys.dm_xe_objects xo
	WHERE xo.object_type = 'event'
	ORDER BY xo.name;

/* and the public events */
SELECT xo.name AS EventName, xo.description
	FROM sys.dm_xe_objects xo
	WHERE xo.object_type = 'event'
		AND (xo.capabilities_desc <> 'private'
		OR xo.capabilities_desc IS NULL)
	ORDER BY xo.name;

Very basic query(ies). Knowing these events, what if I now wanted to tie this back to something we have already seen? For example, what if I wanted to see which channel and keyword mapped to the event? I might try something like the following:

DECLARE @keyword VARCHAR(64) = 'errors' --'errors' --NULL for all keywords
		, @channel VARCHAR(32) = NULL; --'admin' --NULL for all channels

		,ch.description AS EventDesciption
		, ke.KeyWord AS Keyword
		, xp.name AS Package
		, ch.Channel AS Channel
	FROM sys.dm_xe_packages xp
			SELECT oc.object_package_guid, v.map_value AS Channel
					,xo.name AS EventName
				FROM sys.dm_xe_object_columns oc
					INNER JOIN sys.dm_xe_map_values v
						ON oc.type_name = v.name
						AND oc.column_value = CONVERT(NVARCHAR,v.map_key)
					INNER JOIN sys.dm_xe_objects xo
						ON oc.object_package_guid = xo.package_guid
						AND oc.object_name = xo.name
				WHERE oc.name = 'channel'
					AND xo.object_type = 'event'
					AND (xo.capabilities_desc <> 'private'
						OR xo.capabilities_desc IS NULL)
					) ch
			ON ch.object_package_guid = xp.guid
			SELECT xoc.object_package_guid, mv.map_value AS KeyWord
					,xo.name AS EventName
				FROM sys.dm_xe_map_values mv
					INNER JOIN sys.dm_xe_object_columns xoc
						ON xoc.type_name = mv.name
						AND xoc.column_value = CONVERT(NVARCHAR,mv.map_key)
						AND xoc.type_package_guid = mv.object_package_guid
					INNER JOIN sys.dm_xe_objects xo
						ON xo.package_guid = xoc.object_package_guid
						AND xo.name = xoc.object_name
				WHERE mv.name = 'keyword_map'
					AND xo.object_type = 'event'
					) ke
			ON ke.object_package_guid = xp.guid
			AND ch.EventName = ke.EventName
	WHERE ch.Channel = ISNULL(@channel,ch.Channel)
		AND ISNULL(ke.KeyWord,'') = COALESCE(@keyword,ke.KeyWord,'')
	ORDER BY  ke.KeyWord,xp.name;

Granted, in the previous article I had included the event name in the query. I just simply did not return the event name with the results. What I have added this time around though is the event description to the query. Then I am returning both the event name and description with the results.

Looking closer at the query, you will see that I have set it up in a fashion to allow targeted searching for keywords or channels or both. Or one can provide a NULL value to return all Events regardless of keyword or channel.

This gets us to a spot where we can find the events – basically. But that is one piece of the puzzle. When dealing with extended events, one also needs to understand the components of the payload within the event. In other words, there is a bit of dissecting of the event to figure out the differences in data available within an event.

Stay tuned for the next episode when we perform the event dissection and look into the anatomy of an event (e.g. the data/payload of the event).



Leave a comment on the original post [jasonbrimhall.info, opens in a new window]

Loading comments...