Sys.dm_os_ring_buffers內(nèi)幕-創(chuàng)新互聯(lián)

Sys.dm_os_ring_buffers內(nèi)幕

創(chuàng)新互聯(lián)2013年至今，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目成都做網(wǎng)站、成都網(wǎng)站制作網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢想脫穎而出為使命，1280元梁山做網(wǎng)站,已為上家服務(wù),為梁山各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:028-86922220

翻譯自：https://mssqlwiki.com/2013/03/29/inside-sys-dm_os_ring_buffers/

Sys.dm_os_ring_buffers DMV可以用于連接錯(cuò)誤故障排除，跟蹤異常，監(jiān)控系統(tǒng)健康，內(nèi)存壓力，Non-yielding/Deadlocked schedulers和更多。

你可以使用以下腳本在故障排除期間從sys.dm_os_ring_buffers查詢數(shù)據(jù)。

USE master go SET NOCOUNT ON SET QUOTED_IDENTIFIER ON GO PRINT 'Start Time: ' + CONVERT(varchar(30), GETDATE(), 121) GO PRINT '' PRINT '==== SELECT GETDATE()' SELECT GETDATE() PRINT '' PRINT '' PRINT '==== SELECT @@version' SELECT @@VERSION GO PRINT '' PRINT '==== SQL Server name' SELECT @@SERVERNAME GO PRINT '' PRINT '' PRINT '==== RING_BUFFER_CONNECTIVITY -LOGIN TIMERS' SELECT a.* FROM (SELECT x.value('(//Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(30)') AS [RecordType], x.value('(//Record/ConnectivityTraceRecord/RecordSource)[1]', 'varchar(30)') AS [RecordSource], x.value('(//Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid], x.value('(//Record/ConnectivityTraceRecord/OSError)[1]', 'int') AS [OSError], x.value('(//Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS[SniConsumerError], x.value('(//Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State], x.value('(//Record/ConnectivityTraceRecord/RecordTime)[1]', 'nvarchar(30)') AS [RecordTime], x.value('(//Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferError)[1]', 'int') AS[TdsInputBufferError], x.value('(//Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsOutputBufferError)[1]', 'int') AS[TdsOutputBufferError], x.value('(//Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferBytes)[1]', 'int') AS[TdsInputBufferBytes], x.value('(//Record/ConnectivityTraceRecord/LoginTimers/TotalLoginTimeInMilliseconds)[1]', 'int') AS[TotalLoginTimeInMilliseconds], x.value('(//Record/ConnectivityTraceRecord/LoginTimers/LoginTaskEnqueuedInMilliseconds)[1]', 'int') AS[LoginTaskEnqueuedInMilliseconds], x.value('(//Record/ConnectivityTraceRecord/LoginTimers/NetworkWritesInMilliseconds)[1]', 'int') AS[NetworkWritesInMilliseconds], x.value('(//Record/ConnectivityTraceRecord/LoginTimers/NetworkReadsInMilliseconds)[1]', 'int') AS[NetworkReadsInMilliseconds], x.value('(//Record/ConnectivityTraceRecord/LoginTimers/SslProcessingInMilliseconds)[1]', 'int') AS[SslProcessingInMilliseconds], x.value('(//Record/ConnectivityTraceRecord/LoginTimers/SspiProcessingInMilliseconds)[1]', 'int') AS[SspiProcessingInMilliseconds], x.value('(//Record/ConnectivityTraceRecord/LoginTimers/LoginTriggerAndResourceGovernorProcessingInMilliseconds)[1]', 'int') AS[LoginTriggerAndResourceGovernorProcessingInMilliseconds] FROM (SELECT CAST(record as xml) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY')AS R(x)) a where a.RecordType = 'LoginTimers' order by a.recordtime PRINT '' PRINT '' PRINT '==== RING_BUFFER_CONNECTIVITY -TDS Data' SELECT a.* FROM (SELECT x.value('(//Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(30)') AS [RecordType], x.value('(//Record/ConnectivityTraceRecord/RecordSource)[1]', 'varchar(30)') AS [RecordSource], x.value('(//Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid], x.value('(//Record/ConnectivityTraceRecord/OSError)[1]', 'int') AS [OSError], x.value('(//Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS[SniConsumerError], x.value('(//Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State], x.value('(//Record/ConnectivityTraceRecord/RecordTime)[1]', 'nvarchar(30)') AS [RecordTime], x.value('(//Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferError)[1]', 'int') AS[TdsInputBufferError], x.value('(//Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsOutputBufferError)[1]', 'int') AS[TdsOutputBufferError], x.value('(//Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferBytes)[1]', 'int') AS[TdsInputBufferBytes], x.value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/PhysicalConnectionIsKilled)[1]', 'int') AS[PhysicalConnectionIsKilled], x.value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError)[1]', 'int') AS[DisconnectDueToReadError], x.value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NetworkErrorFoundInInputStream)[1]', 'int') AS[NetworkErrorFoundInInputStream], x.value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/ErrorFoundBeforeLogin)[1]', 'int') AS [ErrorFoundBeforeLogin], x.value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]', 'int') AS[SessionIsKilled], x.value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect)[1]', 'int') AS[NormalDisconnect] FROM (SELECT CAST(record as xml) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY')AS R(x)) a where a.RecordType = 'Error' order by a.recordtime PRINT '' PRINT '' PRINT '====RING_BUFFER_SECURITY_EORROR' SELECT CONVERT(varchar(30), GETDATE(), 121) as [RunTime], dateadd(ms, rbf.[timestamp] - tme.ms_ticks, GETDATE()) as[Notification_Time], cast(record as xml).value('(//SPID)[1]', 'bigint') as SPID, cast(record as xml).value('(//ErrorCode)[1]', 'varchar(255)') as Error_Code, cast(record as xml).value('(//CallingAPIName)[1]', 'varchar(255)') as [CallingAPIName], cast(record as xml).value('(//APIName)[1]', 'varchar(255)') as [APIName], cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id], cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type], cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],tme.ms_ticks as [Current Time] from sys.dm_os_ring_buffersrbf cross join sys.dm_os_sys_info tme where rbf.ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR' ORDER BY rbf.timestamp ASC PRINT '' PRINT '' PRINT '==== RING_BUFFER_EXCEPTION' SELECT CONVERT(varchar(30), GETDATE(), 121) as [RunTime], dateadd(ms,(rbf.[timestamp] - tme.ms_ticks), GETDATE()) as Time_Stamp, cast(record as xml).value('(//Exception//Error)[1]', 'varchar(255)') as [Error], cast(record as xml).value('(//Exception/Severity)[1]', 'varchar(255)') as [Severity], cast(record as xml).value('(//Exception/State)[1]', 'varchar(255)') as [State], msg.description, cast(record as xml).value('(//Exception/UserDefined)[1]', 'int') AS[isUserDefinedError], cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id], cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type], cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time], tme.ms_ticks as [Current Time] from sys.dm_os_ring_buffersrbf cross join sys.dm_os_sys_info tme cross join sys.sysmessages msg where rbf.ring_buffer_type = 'RING_BUFFER_EXCEPTION' and msg.error = cast(record as xml).value('(//Exception//Error)[1]', 'varchar(500)') and msg.msglangid = 1033 ORDER BY rbf.timestamp ASC PRINT '' PRINT '' PRINT '====RING_BUFFER_RESOURCE_MONITOR to capture external and internal memory pressure' SELECT CONVERT(varchar(30), GETDATE(), 121) as [RunTime], dateadd(ms,(rbf.[timestamp] - tme.ms_ticks), GETDATE()) as[Notification_Time], cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS[Notification_type], cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS[MemoryUtilization %], cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id], cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS[Process_Indicator], cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS[System_Indicator], cast(record as xml).value('(//Record/ResourceMonitor/Effect/@type)[1]', 'varchar(30)') AS [type], cast(record as xml).value('(//Record/ResourceMonitor/Effect/@state)[1]', 'varchar(30)') AS [state], cast(record as xml).value('(//Record/ResourceMonitor/Effect/@reversed)[1]', 'int') AS [reserved], cast(record as xml).value('(//Record/ResourceMonitor/Effect)[1]', 'bigint') AS [Effect], cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@type)[1]', 'varchar(30)') AS [type], cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@state)[1]', 'varchar(30)') AS [state], cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@reversed)[1]', 'int') AS [reserved], cast(record as xml).value('(//Record/ResourceMonitor/Effect)[2]', 'bigint') AS [Effect], cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@type)[1]', 'varchar(30)') AS [type], cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@state)[1]', 'varchar(30)') AS [state], cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@reversed)[1]', 'int') AS [reserved], cast(record as xml).value('(//Record/ResourceMonitor/Effect)[3]', 'bigint') AS [Effect], cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS[SQL_ReservedMemory_KB], cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS[SQL_CommittedMemory_KB], cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory], cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS[SinglePagesMemory], cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS[MultiplePagesMemory], cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS[TotalPhysicalMemory_KB], cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS[AvailablePhysicalMemory_KB], cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS[TotalPageFile_KB], cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS[AvailablePageFile_KB], cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS[TotalVirtualAddressSpace_KB], cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS[AvailableVirtualAddressSpace_KB], cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id], cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type], cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time], tme.ms_ticks as [Current Time] FROM sys.dm_os_ring_buffersrbf cross join sys.dm_os_sys_info tme where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'--and cast(record asxml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') ='RESOURCE_MEMPHYSICAL_LOW' ORDER BY rbf.timestamp ASC PRINT '' PRINT '' PRINT '==== RING_BUFFER_SCHEDULER_MONITORto Monitor system health' SELECT CONVERT(varchar(30), GETDATE(), 121) as runtime, DATEADD(ms, a.[Record Time] - sys.ms_ticks, GETDATE()) AS Notification_time, a.* , sys.ms_ticks AS [CurrentTime] FROM (SELECT x.value('(//Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS[ProcessUtilization], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle %], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]', 'bigint') AS [UserModeTime], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]', 'bigint') AS [KernelModeTime], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/PageFaults)[1]', 'bigint') AS [PageFaults], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta)[1]', 'bigint')/1024 AS[WorkingSetDelta], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'bigint') AS[MemoryUtilization (%workingset)], x.value('(//Record/@time)[1]', 'bigint') AS [RecordTime] FROM (SELECT CAST(record as xml) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR') AS R(x)) a CROSS JOIN sys.dm_os_sys_info sys ORDER BY DATEADD(ms, a.[Record Time] - sys.ms_ticks, GETDATE())

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

當(dāng)前名稱：Sys.dm_os_ring_buffers內(nèi)幕-創(chuàng)新互聯(lián)
文章轉(zhuǎn)載：http://muchs.cn/article0/cdsgoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制開發(fā)、營銷型網(wǎng)站建設(shè)、企業(yè)網(wǎng)站制作、建站公司、軟件開發(fā)、網(wǎng)站維護(hù)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容