sys.dm_xe_map_values – Day 58 – One DMV a Day

Hello Geeks and welcome to the Day 58 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

So far we are covering extended events related DMVs. Yesterday we have finished sys.dm_xe_object_columns. Today I will be covering sys.dm_xe_map_values. This DMV provides the numeric map values to the values which we read.

To put it in more simpler terms what sys.dm_xe_map_values does I will explain it through the output form the DMV.

SELECT * 
FROM sys.dm_xe_map_values
WHERE name LIKE 'wait_types'

sys.dm_xe_map_values

From the above output you can see that each wait type has a different unique map_key. This value is not the same across versions. So if you are using same extended events scripts on different versions it may not work. So make sure to use the right value form this table.

This can be used to filter out the events I want to capture. If I have to troubleshoot the BACKUPBUFFER wait type I will first get the map_key from sys.dm_xe_map_values. Then I will use as below for creating my extended events session.

CREATE EVENT SESSION SSG_WaitSession_OneDMVaDay ON SERVER
ADD EVENT [sqlos].[wait_info]
(
    ACTION ([package0].[callstack])
    WHERE [wait_type] = 138 -- BACKUPBUFFER only
)
ADD TARGET [package0].[asynchronous_bucketizer]
(
    SET filtering_event_name = N'sqlos.wait_info',
    source_type = 1,
    source = N'package0.callstack'
)
WITH
(
    MAX_MEMORY = 50 MB,
    MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

So this session collects only events when the wait type is BACKUPBUFFER. Sys.dm_xe_map_values is needed as you cannot use the map_values for filters. Only map_key will keep it light. One more reason I think Microsoft kept it like this is to not allow pattern matching on map_values. Makes sense.

One more important thing to notice is that the map values are not always the same that you see in wait types for waits. All the mapping for wait names which are different are beautifully blogged by Jonathan Kehayias

Tomorrow I will be covering another extended events related DMV. So stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

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