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.text
FROM RingBufferConnectivity RBC
LEFT 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中进行连接故障排除