SQLServer 中dm_os_ring_buffers(ring_buffer_connectivity)读取XML

select distinct ring_buffer_type from sys.dm_os_ring_buffers/*RING_BUFFER_OOM RING_BUFFER_RESOURCE_MONITOR - 监控资源的使用情况，可以提供分析物理内存压力的信息。RING_BUFFER_SCHEDULER_MONITOR  - 监控逻辑调度器的状态，以及健康的记录类型。RING_BUFFER_MEMORY_BROKER  - 监控用于平衡缓存之间的内存分配的内部内存代理的行为。RING_BUFFER_SECURITY_ERROR  - 安全相关的错误信息。例如：登录失败可能会有更详细的关于操作系统错误代码。RING_BUFFER_SCHEDULER - 实际的调度器活动，像上下文切换。你可以从入口重新构建执行顺序。RING_BUFFER_EXCEPTION - 服务器发出的任何异常。SQL内部抛出错误，因此你也可以看到SQL错误。RING_BUFFER_CONNECTIVITY -关键连接信息，对于跟踪连接失败非常有用。RecordType:1. Error – 连接错误2. LoginTimers – 连接超时3. ConnectionClose – 杀掉进程*/

--RING_BUFFER_CONNECTIVITY;WITH RingBufferConnectivity as(   SELECT        records.record.value('(/Record/@id)[1]', 'int') AS [RecordID],        records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS [RecordType],        records.record.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid],        records.record.value('(/Record/ConnectivityTraceRecord/SniConnectionId)[1]', 'varchar(max)') AS [SniConnectionId],        records.record.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS [SniConsumerError],        records.record.value('(/Record/ConnectivityTraceRecord/SniProvider)[1]', 'int') AS [SniProvider],        records.record.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State],        records.record.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS [RemoteHost],        records.record.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS [RemotePort],        records.record.value('(/Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(max)') AS [LocalHost],        records.record.value('(/Record/ConnectivityTraceRecord/LocalPort)[1]', 'varchar(max)') AS [LocalPort],        records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS [RecordTime],        records.record.value('(/Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferError)[1]', 'int') AS [TdsInputBufferError],        records.record.value('(/Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsOutputBufferError)[1]', 'int') AS [TdsOutputBufferError],        records.record.value('(/Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferBytes)[1]', 'int') AS [TdsInputBufferBytes],        records.record.value('(/Record/ConnectivityTraceRecord/LoginTimers/TotalLoginTimeInMilliseconds)[1]', 'int') AS [TotalLoginTime],        records.record.value('(/Record/ConnectivityTraceRecord/LoginTimers/LoginTaskEnqueuedInMilliseconds)[1]', 'int') AS [LoginTaskEnqueued],        records.record.value('(/Record/ConnectivityTraceRecord/LoginTimers/NetworkWritesInMilliseconds)[1]', 'int') AS [NetworkWrites],        records.record.value('(/Record/ConnectivityTraceRecord/LoginTimers/NetworkReadsInMilliseconds)[1]', 'int') AS [NetworkReads],         records.record.value('(/Record/ConnectivityTraceRecord/LoginTimers/SslProcessingInMilliseconds)[1]', 'int') AS [SslProcessing],        records.record.value('(/Record/ConnectivityTraceRecord/LoginTimers/SspiProcessingInMilliseconds)[1]', 'int') AS [SspiProcessing]    FROM    (   SELECT CAST(record as xml) AS record_data        FROM sys.dm_os_ring_buffers        WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY'    ) TabA    CROSS APPLY record_data.nodes('//Record') AS records (record))SELECT RBC.*, m.textFROM RingBufferConnectivity RBCLEFT JOIN sys.messages M ON RBC.[SniConsumerError] = M.message_id AND M.language_id = 2052--WHERE RBC.RecordType='Error'WHERE RBC.[RecordTime] >=  CONVERT(DATETIME,'2014-12-25 10:24:00')AND RBC.[RecordTime] <= CONVERT(DATETIME,'2014-12-25 10:30:00')ORDER BY RBC.RecordTime DESC

更多参考官方博客：利用Ring Buffer在SQL Server 2008中进行连接故障排除