{"name":"Detecting Connection Timeouts","tsql":"SELECT Count(*) AS ObjectsAltered\r\nFROM sys.dm_xe_session_targets AS xet\r\nINNER JOIN sys.dm_xe_sessions AS xes\r\nON xes.address = xet.event_session_address\r\nCROSS APPLY\r\n(SELECT Cast(xet.target_data AS XML)) AS target_data_xml(xml)\r\nCROSS APPLY target_data_xml.xml.nodes('\/\/RingBufferTarget\/event') AS xed(event_data)\r\nWHERE xes.name = 'ConnectionTimeout'\r\nAND xet.target_name = 'ring_buffer'\r\nAND DateDiff\r\n(MINUTE,\r\nConvert(DATETIME2,\r\nSwitchOffset(\r\nConvert(DATETIMEOFFSET,xed.event_data.value('(@timestamp)[1]', 'datetime2')),\r\nDateName(TzOffset,SysDateTimeOffset()))\r\n),\r\nGetDate()) < 30; --timeouts recorded in OS ring buffer within the last thirty minutes","instances":true,"databases":{"mode":1},"frequency":1800,"alertenabled":false,"metricenabled":true,"description":"Connection timeouts can be difficult to troubleshoot. It's sometimes not even apparent that a timeout has occurred. However, the connectivity_ring_buffer_recorded Extended Event can show that this has happened. This is a debug event within Extended Events, so some caution is necessary in its use. Microsoft can change the debug events without warning.","frequency_comment":"seconds (collected every 30 minutes)","rateofchange":false,"aboveorbelow":"above","_thresholds_high":{"selected":false},"_thresholds_medium":{"selected":false},"_thresholds_low":{"selected":false},"collections":1,"targetentitytype":0}