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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.