Map Data Types in Extended Events

, 2015-09-18

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
		, CASE	WHEN 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).

mismatched_map

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.

sample_map

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads