{"name":"MonitorTempDBContention","description":"Measures the total duration of latch waits on tempdb, over a period (one-minute time intervals, in this example). Collected over time, this will establish a baseline, showing the typical profile for tempdb latching for your workload, and allow you to spot abnormalities that indicate contention.","tsql":"DECLARE @Target_Data XML =\r\n          (\r\n          SELECT TOP 1 Cast(xet.target_data AS XML) AS targetdata\r\n            FROM sys.dm_xe_session_targets AS xet\r\n              INNER JOIN sys.dm_xe_sessions AS xes\r\n                ON xes.address = xet.event_session_address\r\n            WHERE xes.name = 'MonitorTempDBContention'\r\n              AND xet.target_name = 'ring_buffer'\r\n          );\r\nSELECT SUM(Duration)\r\nFROM \r\n  (\r\n  SELECT \r\n    Convert(datetime2,\r\n        SwitchOffset(CONVERT(datetimeoffset,xed.event_data.value('(@timestamp)[1]', 'datetime2')),\r\n        DateName(TzOffset, SYSDATETIMEOFFSET()))) AS Thedatetime,\r\n    xed.event_data.value('(data[@name=\"duration\"]\/value)[1]', 'int') AS [Duration]\r\n  FROM @Target_Data.nodes('\/\/RingBufferTarget\/event') AS xed (event_data)\r\n  )f(TheDateTime,Duration)\r\nWHERE Convert(CHAR(17),TheDateTime,113)\r\n       = Convert(CHAR(17),DateAdd(MINUTE,-2,GetDate()),113)","instances":false,"databases":{"mode":1},"frequency":60,"rateofchange":false,"metricenabled":true,"noalertremark":"The appropriate alert thresholds are entirely dependent on your system, and you should only enable them after establishing a baseline for your working instance.","frequency_comment":"seconds (collected every minute)","aboveorbelow":"above","_thresholds_high":{"selected":false},"_thresholds_medium":{"selected":false},"_thresholds_low":{"selected":false},"collections":1,"alertenabled":true,"targetentitytype":0}