Extended Events – Event Anatomy

So far in these brief discussions about Extended Events, I have covered several core concepts including a basic introduction into what an Event is within Extended Events. For a recap on those articles, you can read this.

As a follow-up to the introduction to what an Event is, it makes sense to discuss the components of an event. What makes up an event? What is the payload of the event? What is a payload?

Payload

The payload, is the stuff that is to be delivered somewhere. The payload can be rather extensive and broad like one may see in the armament configuration of a B-2 (as we see in the image to the right).

Or the payload can be more streamline and compact as one might see in a rocket used to deliver a satellite.

As seen in this image depicting a typical delivery mechanism for satellites to be delivered beyond the Earth’s atmosphere, the payload can be very specific and “targeted”.

With these payloads, we see that some common payloads range from one point of interest to hundreds of points of interest.

When working with computers (and not a physical delivery mechanism), a payload can work in much the same way. The item to be delivered can be focused and small in quantity or it can be broad and large in quantity.

A common use of payload in the computer world is with a virus or malware. Virii have a payload that is delivered to the new host to attempt to infect it. This is not too far different from the payload of Extended Events – excepting that XEvent payloads do not infect new hosts.

The payload of an XE Event is the data that was captured at the time the event (point of interest) was fired. The data of an Event can be broken into three classes or groups. More specifically, the columns that define the data can be broken into two different types with two divisions in the second type.

The first is the readonly data of the event. These columns are much like the metadata of the event containing data such as the channel and keyword. This data is used for ETW integration.

The second type of columns is the data that is captured when an event fires in the engine. Within this group, there are customizable data points as well as state data points. The data that is customizable is to the point of enabling the capture of that data point or disabling the capture of that specific data point.

These different types do not apply to all events. The customizable data may be present in some events. Don’t be surprised if you come across an event that does not contain a customizable data point.

We can look at some of this via the following example. Note that while this example touches the sys.dm_xe_object_columns DMV, I will not go into great detail about this just yet.

DECLARE @EventName VARCHAR(64) = 'sp_statement_completed'
,@ReadFlag VARCHAR(64) = 'ALL' --readonly' --ALL if all columntypes are desired
 
SELECT oc.OBJECT_NAME AS EventName
,oc.name AS column_name, oc.type_name
,oc.column_type AS column_type
,oc.column_value AS column_value
,oc.description AS column_description
,ca.map_value AS SearchKeyword
FROM sys.dm_xe_object_columns oc
CROSS APPLY (SELECT TOP 1 mv.map_value
FROM sys.dm_xe_object_columns occ
INNER JOIN sys.dm_xe_map_values mv
ON occ.type_name = mv.name
AND occ.column_value = mv.map_key
WHERE occ.name = 'KEYWORD'
AND occ.object_name = oc.object_name) ca
WHERE oc.object_name = @EventName
AND oc.column_type <> @ReadFlag
;

This will return the following results in SQL Server 2014:

In the attached image, I have color coded each of the different types of data. In the results, it is also shown that the customizable data points are boolean in nature. They are either enabled or disabled for the capture of that data point. And lastly, we can see the “metadata” or “event header” type of information in the readonly type of data. Of these data points within the payload, the most interesting will consistently be found in the “customizable” and “data” classifications.

The information presented on this payload will prove more helpful as we continue on this series. Stay tuned for the next article when we start taking a look at the Columns in greater detail.