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];
本文出自 “SQL Server Deep Dives” 博客,请务必保留此出处http://ultrasql.blog.51cto.com/9591438/1600520
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。