Sys.dm_os_ring_buffers內(nèi)幕
Sys.dm_os_ring_buffers內(nèi)幕
創(chuàng)新互聯(lián)從2013年開(kāi)始，先為焉耆等服務(wù)建站，焉耆等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為焉耆企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。
翻譯自：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())
分享標(biāo)題：Sys.dm_os_ring_buffers內(nèi)幕
網(wǎng)站鏈接：http://muchs.cn/article36/jpidsg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供動(dòng)態(tài)網(wǎng)站、App設(shè)計(jì)、網(wǎng)站導(dǎo)航、小程序開(kāi)發(fā)、網(wǎng)站改版、網(wǎng)站營(yíng)銷
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 影響網(wǎng)站優(yōu)化排名的幾個(gè)重要因素 2022-09-24
- 為什么說(shuō)企業(yè)網(wǎng)站，必須要做好網(wǎng)站排名優(yōu)化工作呢？ 2022-12-15
- 外貿(mào)網(wǎng)站該怎樣留住訪客 2023-09-22
- URI和URL的區(qū)別 2021-03-13
- 電商平臺(tái)定制年費(fèi)和模板年費(fèi)的區(qū)別 2021-05-25
- 域名過(guò)期后搶注的相關(guān)知識(shí) 2023-12-22
- 網(wǎng)站制作在商城類網(wǎng)站建設(shè)的時(shí)候需要注意哪些地方的幾個(gè)要點(diǎn)二手網(wǎng)站誰(shuí)制作 2023-11-21
- 成都網(wǎng)站建設(shè)如何增強(qiáng)網(wǎng)站內(nèi)容的吸引力 2023-03-05
- 網(wǎng)站容是寫(xiě)給誰(shuí)看的？說(shuō)說(shuō)寫(xiě)網(wǎng)頁(yè)內(nèi)容需要注意些什么？ 2022-03-03
- 網(wǎng)頁(yè)設(shè)計(jì):手機(jī)端網(wǎng)站要注意的要點(diǎn) 2023-09-27
- 北京網(wǎng)站優(yōu)化，如何判斷網(wǎng)站title關(guān)鍵詞是否過(guò)度重復(fù)？ 2021-04-14
- 小企業(yè)選服務(wù)器租用的好處在哪？ 2023-06-26