SQL Server扩展事件（Extended Events）-- 使用扩展事件跟踪监控死锁脚本实现
|
1
2
3
4
5
6
7
8
|
-- Create a new event session (it is better to create a new session and not modify the system’s built-in session “system_health”):
CREATE
EVENT SESSION [Deadlock_Monitor]
ON
SERVER
ADD
EVENT sqlserver.xml_deadlock_report
ADD
TARGET package0.asynchronous_file_target
(
SET
filename= N
'C:\temp\deadlock.xel'
)
WITH
(MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=10 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=
OFF
,STARTUP_STATE=
ON
)
|
|
1
2
|
-- Enable the session to start capturing events:
ALTER
EVENT SESSION [Deadlock_Monitor]
ON
SERVER STATE = start;
|
|
1
2
|
-- To see how many deadlocks have been captured by the session since it started running, you can run this query:
select
COUNT
(*)
from
sys.fn_xe_file_target_read_file (
'c:\temp\deadlock*.xel'
,
'c:\temp\deadlock*.xem'
,
null
,
null
)
|
|
1
2
3
4
5
6
|
-- To get a list of the captured deadlocks and their graphs you can execute this query:
select
xml_data.value(
'(event[@name="xml_deadlock_report"]/@timestamp)[1]'
,
'datetime'
) Execution_Time,
xml_data.value(
'(event/data/value)[1]'
,
'varchar(max)'
) Query
from
(
select
object_name
as
event,
CONVERT
(xml, event_data)
as
xml_data
from
sys.fn_xe_file_target_read_file
(
'c:\temp\deadlock*.xel'
,
'c:\temp\deadlock*.xem'
,
null
,
null
)) v
order
by
Execution_Time
|
|
1
2
|
-- If you want the session to stop capturing events (until you enable the session again), you can use this query:
ALTER
EVENT SESSION [Deadlock_Monitor]
ON
SERVER STATE = stop;
|
|
1
2
|
-- If you want to completely remove (delete) the session from the server, you can use this query:
DROP
EVENT SESSION [Deadlock_Monitor]
ON
SERVER
|
|
1
2
3
4
5
6
7
|
-- If you want to configure other events too, you can query these tables to find out what you can trace and how:
--Show the possible targets:
SELECT
xp.[
name
], xo.*
FROM
sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE
xp.[guid] = xo.[package_guid]
AND
xo.[object_type] =
'target'
ORDER
BY
xp.[
name
];
|
|
1
2
3
4
5
6
|
--Show the possible actions
SELECT
xp.[
name
], xo.*
FROM
sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE
xp.[guid] = xo.[package_guid]
AND
xo.[object_type] =
'action'
ORDER
BY
xp.[
name
], xo.[
name
];
|
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1600520 ，如需转载请自行联系原作者
