Rumah > pangkalan data > tutorial mysql > SQLServer环形缓冲区(RingBuffer)--RING_BUFFER_EXCEPTION跟踪

SQLServer环形缓冲区(RingBuffer)--RING_BUFFER_EXCEPTION跟踪

WBOY
Lepaskan: 2016-06-07 14:55:34
asal
1550 orang telah melayarinya

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 BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
select @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 MinTime
SELECT record_id,
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,y.Error,UserDefined,b.description as NormalizedText
FROM (
SELECT
record.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,TIMESTAMP
FROM (
SELECT TIMESTAMP, CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'
AND record LIKE '% %') AS x) AS y
INNER JOIN sys.sysmessages b
on y.Error = b.error
WHERE b.msglangid = 1033 and y.Error = 1222 -- Change the message number to the message number that you want to monitor
ORDER BY record_id DESC
Salin selepas log masuk

对于SQL Server 2008:

DECLARE @ts_now BIGINT,@dt_max BIGINT, @dt_min BIGINT
SELECT @ts_now = cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info
select @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 MinTime
SELECT record_id,
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,Error,UserDefined,text as NormalizedText
FROM (
SELECT
record.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,TIMESTAMP
FROM (
SELECT TIMESTAMP, CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'
AND record LIKE '% %') AS x) AS y
INNER JOIN sys.messages b
on y.Error = b.message_id
WHERE b.language_id = 1033 and y.Error = 1222 -- Change the message number to the message number that you want to monitor
ORDER BY record_id DESC
Salin selepas log masuk

 

clip_image001

 

clip_image002



Label berkaitan:
sumber:php.cn
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan