SQL Server 环形缓冲区（Ring Buffer） — RING_BUFFER_EXCEPTION 跟踪异常 动态管理视图sys.dm_os_ring_buffers使得实时定位问题更加容易。环形缓冲包含大量的在服务器上发生的事件。当前，我正碰到锁请求超时问题。根据SQL Server Profiler跟踪捕获，发现

SQL Server 环形缓冲区（Ring Buffer） — RING_BUFFER_EXCEPTION 跟踪异常

动态管理视图sys.dm_os_ring_buffers使得实时定位问题更加容易。环形缓冲包含大量的在服务器上发生的事件。当前，我正碰到锁请求超时问题。根据SQL Server Profiler跟踪捕获，发现服务器收到大量如下信息：

Lock request time out period exceeded.

我们找到了语句并修改，来阻止所请求超时的发生。现在服务器正被监控，我不想运行SQL Server Profiler去跟踪这个消息的产生。所以，我想用环形缓冲动态管理视图去监控是否服务器上有进一步的锁请求超时发生。这使得监控实例更容易。

下面的脚本给出了一个存储在环形缓冲区中的异常的时间范围，输出了大量的发生的异常。

对于SQL Server 2005：

DECLARE @ts_now BIGINT,@dt_max BIGINT, @dt_min BIGINTSELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_infoselect @dt_max = MAX(timestamp), @dt_min = MIN(timestamp) from sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'select DATEADD(ms, -1 * (@ts_now - @dt_max), GETDATE()) AS MaxTime,DATEADD(ms, -1 * (@ts_now - @dt_min), GETDATE()) AS MinTimeSELECT record_id,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,y.Error,UserDefined,b.description as NormalizedTextFROM (SELECTrecord.value('(./Record/@id)[1]', 'int') AS record_id,record.value('(./Record/Exception/Error)[1]', 'int') AS Error,record.value('(./Record/Exception/UserDefined)[1]', 'int') AS UserDefined,TIMESTAMPFROM (SELECT TIMESTAMP, CONVERT(XML, record) AS recordFROM sys.dm_os_ring_buffersWHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'AND record LIKE '% %') AS x) AS yINNER JOIN sys.sysmessages bon y.Error = b.errorWHERE b.msglangid = 1033 and y.Error = 1222 -- Change the message number to the message number that you want to monitorORDER BY record_id DESC

对于SQL Server 2008：

DECLARE @ts_now BIGINT,@dt_ma<span style="color:transparent">本文来源gaodai#ma#com搞*!代#%^码网%</span>x BIGINT, @dt_min BIGINTSELECT @ts_now = cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_infoselect @dt_max = MAX(timestamp), @dt_min = MIN(timestamp) from sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'select DATEADD(ms, -1 * (@ts_now - @dt_max), GETDATE()) AS MaxTime,DATEADD(ms, -1 * (@ts_now - @dt_min), GETDATE()) AS MinTimeSELECT record_id,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,Error,UserDefined,text as NormalizedTextFROM (SELECTrecord.value('(./Record/@id)[1]', 'int') AS record_id,record.value('(./Record/Exception/Error)[1]', 'int') AS Error,record.value('(./Record/Exception/UserDefined)[1]', 'int') AS UserDefined,TIMESTAMPFROM (SELECT TIMESTAMP, CONVERT(XML, record) AS recordFROM sys.dm_os_ring_buffersWHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'AND record LIKE '% %') AS x) AS yINNER JOIN sys.messages bon y.Error = b.message_idWHERE b.language_id = 1033 and y.Error = 1222 -- Change the message number to the message number that you want to monitorORDER BY record_id DESC

650) this.width=650;” title=”clip_image001″ style=”border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;” alt=”clip_image001″ src=”https://img.gaodaima.com/d/file/2019/04/15/6dd15633d1a888fa3539ec52f32942ab.jpg” height=”243″ border=”0″ />

650) this.width=650;” title=”clip_image002″ style=”border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;” alt=”clip_image002″ src=”https://img.gaodaima.com/d/file/2019/04/15/52b0487c3c8deb6ea3ce8087f583cc32.jpg” height=”70″ border=”0″ />

喜欢 (0)赏

[搞代码]

分享 (0)