1

I'm working with an extended event session in SQL Server and encountered a part of the T-SQL code that I don't fully understand. Specifically, I'm puzzled by the use of "Mode = 3 OR Mode = 4" in the following portion of the code:

...
ADD EVENT sqlserver.latch_suspend_end
(WHERE (
Duration > 0
AND (Mode = 3 OR Mode = 4)
AND has_waiters = 1
)
)
...

I've been trying to find documentation or any sort of information that explains what "Mode = 3" and "Mode = 4" represent in this context, but so far, I've had no luck.

Furthermore, I'm interested in finding a comprehensive source of documentation or reference that describes the properties and possible values for different events, not just for this specific example. Could anyone point me towards the relevant documentation or explain what these Mode values signify in this extended event session?

Thank you in advance for your help!

1 Answer 1

5

These values correspond to UP and EX latches.

The mode field is of datatype latch_mode.

The mapping of numeric values to meanings is contained in sys.dm_xe_map_values

map_key map_value
0 NL
1 KP
2 SH
3 UP
4 EX
5 DT
6 LASTLATCH

NB: I have no idea what the LASTLATCH entry is about and if this can ever actually be seen in practice. The main latch documentation only mentions the entries with values 1 to 5. sys.dm_os_wait_stats documentation does additionally mention LATCH_NL "for informational purposes only" but no mystery 7th mode. This may be some similar issue to the bogus lock_mode that Paul White looks into here.

Though it is also mentioned here in the context of an error message apparently seen in SQL Server 2000 8.00.194

SQL Server Assertion: File: <latch .cpp>, line=799
Failed Assertion = 'type > NL && type < LASTLATCH'.

To get these mappings yourself in the future you can tweak the example query C.5 sys.dm_xe_map_values and event fields to get the values shown in the table above.

SELECT --C.5
        dp.name         AS [Package],
        do.name         AS [Object],
        do.object_type  AS [Object-Type],
        'o--c'          AS [O--C],
        dc.name         AS [Column],
        dc.type_name    AS [Column-Type-Name],
        dc.column_type  AS [Column-Type],
        dc.column_value AS [Column-Value],
        'c--m'          AS [C--M],
        dm.map_value    AS [Map-Value],
        dm.map_key      AS [Map-Key]
FROM   sys.dm_xe_objects AS do
       JOIN sys.dm_xe_object_columns AS dc
         ON dc.object_name = do.name
       JOIN sys.dm_xe_map_values AS dm
         ON dm.name = dc.type_name
       JOIN sys.dm_xe_packages AS dp
         ON dp.guid = do.package_guid
WHERE  dp.name = 'sqlserver'
       AND do.object_type = 'event'
       AND do.name = 'latch_suspend_end'
       AND dc.name = 'mode'
ORDER  BY [Package],
          [Object],
          [Column],
          [Map-Value]; 
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.