动态管理视图sys.dm_os_ring_buffers使得实时定位问题更加容易。环形缓冲包含大量的在服务器上发生的事件。当前，我正碰到锁请求超时问题。根据SQL Server Profiler跟踪捕获，发现服务器收到大量如下信息：
Lock request time out period exceeded.
我们找到了语句并修改，来阻止所请求超时的发生。现在服务器正被监控，我不想运行SQL Server Profiler去跟踪这个消息的产生。所以，我想用环形缓冲动态管理视图去监控是否服务器上有进一步的锁请求超时发生。这使得监控实例更容易。
下面的脚本给出了一个存储在环形缓冲区中的异常的时间范围，输出了大量的发生的异常。
对于SQL Server 2005：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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
|
对于SQL Server 2008：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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
|
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1584028 ，如需转载请自行联系原作者
