Map Data Types in Extended Events

In the last segment, I wrote about the data types for the payload of an event in Extended Events. In that article, I also mentioned there is another component that is like a custom data type. That component is a map.

Maps are like a way of overloading the data type to expose more meaningful values that relate to the payload data in question. The standard data types can also be called scalar types with a single value (instead of a table lookup).

Maps

Looking at the internals for the maps is very similar to the scalar data types. All that will be necessary is a quick swap on a couple of values in the queries we use the last time.

SELECT DISTINCT xo.name AS TypeName
FROM sys.dm_xe_objects xo
WHERE xo.object_type = 'map'
AND (xo.capabilities & 1 = 0
OR xo.capabilities IS NULL);

Since I am just querying the components library in this query, I only need to look at the “map” object_type. This will produce a result-set of 254 maps. That is far too many to list here at the moment. But let’s take that and compare it to the maps that are attached to actual event payloads.

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 NOT NULL
ORDER BY DataClass DESC
, oc.type_name;

When this query is executed, it can be seen that there are roughly 212 maps exposed to the event payloads (or targets). This leaves us with 42 that for one reason or another are not assigned to a payload and may be unnecessary in the components DMV.

Looking into what maps don’t map to payloads with the following query, we should start to see where the differences may lie:

SELECT DISTINCT xo.name AS TypeName
FROM sys.dm_xe_objects xo
WHERE xo.object_type = 'map'
AND (xo.capabilities & 1 = 0
OR xo.capabilities IS NULL)
AND xo.name NOT IN (
SELECT DISTINCT oc.type_name
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 NOT NULL
);

If you run that query, it will result in 43 maps that exist in the components DMV that do not exist in the payload DMV. Conversely, running this next query will help to explain why the math has not yet lined up for us:

SELECT DISTINCT oc.type_name, oc.object_name
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 NOT NULL
AND mv.name NOT IN (
SELECT DISTINCT xo.name AS TypeName
FROM sys.dm_xe_objects xo
WHERE xo.object_type = 'map'
AND (xo.capabilities & 1 = 0
OR xo.capabilities IS NULL)
);

I wanted to share this to show where the difference was. And looking at it, it is now apparent that there is a map in the event payload that does not seem to be in the objects. For me, this underscores the importance to use the payload DMV to see these maps in lieu of the components DMV (e.g. use sys.dm_xe_object_columns in lieu of sys.dm_xe_objects).

This shows that the missing map is used by several of the events in the system.

With these maps, what can we do to explore the data related to the map. Notice that the previous query references the sys.dm_xe_map_values DMV? Well, this is where the map data will exist. Let’s browse a bit.

DECLARE @EventName VARCHAR(64) = 'database_mirroring_state_change'
, @ReadFlag VARCHAR(64) = 'ALL';
 --readonly' --ALL if all columntypes are desired
 
SELECT oc.object_name AS EventName
, oc.name AS ColName
, mv.name AS MapName
, map_key
, map_value
FROM sys.dm_xe_map_values mv
INNER JOIN sys.dm_xe_object_columns oc
ON mv.name = oc.type_name
AND mv.object_package_guid = oc.object_package_guid
WHERE oc.object_name = @EventName
AND oc.column_type <> @ReadFlag
ORDER BY ColName
, MapName
, mv.map_key;

In this case, I have a need to try and figure out what the various values might be for the different maps tied to a specific event. I have chosen “database_mirroring_state_change” to see what data values will be available for the various maps.

In this sample, we can see there is a column of data in the payload of the database_mirroring_state_change event. The map_value represents the friendly text of the event payload that we would want to lookup in order to determine what is happening (in this case) with the mirroring session.

Keep these few tidbits in mind as we progress through the series. I will revisit these maps in the near future as I continue to use these concepts to build on within the realm of Extended Events.