SQL Server Extended Events (Extended events)--using extended event tracking to monitor deadlock script implementations

SQL Server Extended Event (Extended Events )-- monitoring deadlock script implementation using extended event tracking

--Create a new event session (it is better to Create a new session and not modify the system ' s built-in session "System_h Ealth "): CREATE event SESSION [Deadlock_monitor] on Serveradd event Sqlserver.xml_deadlock_reportadd 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;

--How many deadlocks has been captured by the session since it started running, can run this query:select COU NT (*) 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) ') Queryfrom (select Object_name as Event, CONVERT (XML, Event_data) as Xml_datafrom 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 EVE NT 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 [Deadl Ock_monitor] on SERVER

--If you want to configure other events too, you can query these tables to find out what can trace and how:--show the Possible Targets:select XP. [Name], Xo.*from sys.dm_xe_objects xo, sys.dm_xe_packages xpwhere XP. [GUID] = xo. [Package_guid] and XO. [Object_type] = ' target ' ORDER by XP. [Name];

--show the possible Actionsselect XP. [Name], Xo.*from sys.dm_xe_objects xo, sys.dm_xe_packages xpwhere XP. [GUID] = xo. [Package_guid] and XO. [Object_type] = ' action ' ORDER by XP. [Name], XO. [Name];

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1600520

SQL Server Extended Events (Extended events)--using extended event tracking to monitor deadlock script implementations