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

wefe2

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:        empdeadlock.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:        empdeadlock*.xel', 'c:        empdeadlock*.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:        empdeadlock*.xel', 'c:        empdeadlock*.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];