SQLServer 中dm_os_ring_buffers(ring_buffer_connectivity)读取

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中进行连接故障排除

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!