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'
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
