首页 > 代码库 > SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪监控死锁脚本实现
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
SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪监控死锁脚本实现
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。