SQL Server扩展事件（Extended Events）-- 使用扩展事件跟踪监控死锁脚本实现

SQL Server扩展事件（Extended Events）-- 使用扩展事件跟踪监控死锁脚本实现

-- 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)

 

-- Enable the session to start capturing events:
ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE = start;

 

-- 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)

 

-- 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

 

-- 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;

 

-- If you want to completely remove (delete) the session from the server, you can use this query:
DROP EVENT SESSION [Deadlock_Monitor] ON SERVER

 

-- 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];

 

--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];