Map Data Types in Extended Events

Comments: 1 Comment
Published on: September 18, 2015

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.

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.

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:

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:

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.

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.

1 Comment - Leave a comment
  1. […] Map Data Types in Extended Events September 18, 2015 […]

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
September 2015
M T W T F S S
« Aug   Oct »
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, December 12, 2017