Today I get to talk about data types within Extended Events. This also means, I hope to be able to bring a little more clarity to the topic.
To a small degree, I introduced the topic when I wrote about event payloads. And I also did a very quick gloss over on the topic back when I wrote about data types – here.
In the older article I introduced the concept of data types and more specifically the custom data type. I will not cover the custom data type until the next article. Another, more common, way to talk about the custom data type is “map.”
In the more recent article on payloads, I introduced the notion that the event payload is broken up into three data classifications. Recall the classifications are: readonly, data, and customizable. I will only really be covering the one classification – data. The customizable classification will only have one data type – boolean.
Within the data classification of the payload, that is where we start to see the various data types that can be delivered with an event payload. Within this classification there will be two sub-classes of data types: standard and (as previously mentioned) the custom or maps data type. I will focus just on the standard data types for the time being and reserve the other for the next article.
Data Types
With the way the payload is defined, splitting out the standard data types from the custom data types (maps) requires a bit more than a straight forward query. Due to the EAV nature of the metadata, it requires that one also query the map_values DMV.
SELECT DISTINCT oc.type_name , CASEWHEN ISNULL(mv.name, '') = '' THEN 'Standard' ELSE 'Custom' END AS DataClass FROM sys.dm_xe_object_columns oc LEFT OUTER JOIN sys.dm_xe_map_values mv ON mv.name = oc.type_name WHERE oc.column_type <> 'readonly' AND mv.name IS NULL ORDER BY DataClass DESC , oc.type_name;
Looking at this query, one can see that I am joining the map_vaues DMV and excluding any data type that would exist in the maps. This will yield the following:
Within these results, I have highlighted a few interesting data types. These are the types that may be a little more uncommon within a database. For instance, the ptr data type is not a your typical data type in a database but rather more closely to an overload of a data type. This pointer is a special data type in Extended Events that is used for when the data is larger than 8 bytes. If it is smaller than 8 bytes, then the normal data type is used. Then there is filetime which is more of a property than a data type, but for now we will just leave it classified together with the standard data types.
As you can see, these data types should be very familiar. Nothing scary here – just data types that a data professional would already be accustomed to using.
Now that deals with the data types within the payload. However, this is not yet complete. There is also an object (component) within Extended Events called a type. If I query the components DMV, I will get a slightly different result set than what is seen via the payloads.
SELECT DISTINCT xo.name AS TypeName FROM sys.dm_xe_objects xo WHERE xo.object_type = 'type' AND (xo.capabilities & 1 = 0 OR xo.capabilities IS NULL);
This will yield about 28 data types. In addition to the ones already seen, one will see the additional types of cpu_cycle, wchar, and null among other additional data types. For the most part, I will rely on the data types that are within the event payloads.
If you are curious to see more details about these data types, it is easily achieved by simply adding the description to the query.
SELECT DISTINCT xo.name AS TypeName, xo.description FROM sys.dm_xe_objects xo WHERE xo.object_type = 'type' AND (xo.capabilities & 1 = 0 OR xo.capabilities IS NULL);
Stay tuned for more on Extended Events!
