SQL Server Extended Events (Extended events)--monitoring deadlocks with extended event tracking

SQL Server Extended events (Extended events)--monitoring deadlocks with extended event tracking

We deploy an extended event tracking session through the SQL Server 2012 graphical interface. You can then generate a SQL script that runs a similar trace under the 2008 or R2 version.

Step 1:

Connect to the instance through Object Explorer, expand Management, Extended Events, Sessions.

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M02/58/45/wKiom1StB2ODS_kpAAEzRObs5ds508.jpg "border=" 0 "height=" 436 "/>

Step 2:

Right-click on "Sessions" to create a new Session wizard.

Step 3:

Enter the session name "Deadlock_monitor" and click Next.

650) this.width=650; "title=" clip_image002 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M00/58/45/wKiom1StB2SjGBcNAAHYuGnV50g743.jpg "border=" 0 "height=" 675 "/>

Step 4:

Choose not to use a template (like SQL Server Profiler template, preset some default options to start together, but not a template that meets our needs), click Next.

650) this.width=650; "title=" clip_image003 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M01/58/45/wKiom1StB2WgLXsvAAJQrSFxZ3Y641.jpg "border=" 0 "height=" 673 "/>

Step 5:

Select the event that you want to capture, and in the "Event library" input deadlock, you can see the following:

650) this.width=650; "title=" clip_image004 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M02/58/45/wKiom1StB2eS9938AALCKM3N3bA669.jpg "border=" 0 "height=" 673 "/>

Step 6:

Select "Xml_deadlock_report" to add to the list of events selected on the right. And then click Next.

650) this.width=650; "title=" clip_image005 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image005 "src=" http://s3.51cto.com/wyfs02/M00/58/45/wKiom1StB2mTFribAAMXjVvZKkk254.jpg "border=" 0 "height=" 677 "/>

Step 7:

Select the columns you want to capture, here we choose the next step.

650) this.width=650; "title=" clip_image006 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M01/58/45/wKiom1StB2uzxemeAASaVLHHblE662.jpg "border=" 0 "height=" 678 "/>

Step 8:

Define the filter condition, here we ignore this setting, click Next.

650) this.width=650; "title=" clip_image007 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image007 "src=" http://s3.51cto.com/wyfs02/M02/58/45/wKiom1StB2zz9WKzAAIoTuI1S8Y588.jpg "border=" 0 "height=" 675 "/>

Step 9:

Choose Save data to file, set file path and maximum value, etc. Click Next.

650) this.width=650; "title=" clip_image008 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image008 "src=" http://s3.51cto.com/wyfs02/M00/58/45/wKiom1StB26BQYM-AAL-NeFvxbI253.jpg "border=" 0 "height=" 674 "/>

Step Ten:

Check all the configurations and click Finish to install and enable the session.

650) this.width=650; "title=" clip_image009 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image009 "src=" http://s3.51cto.com/wyfs02/M01/58/45/wKiom1StB27x3ykzAAIbpsg_yKE607.jpg "border=" 0 "height=" 676 "/>

steps:

Now we can start the capture and view the activity data.

650) this.width=650; "title=" clip_image010 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image010 "src=" http://s3.51cto.com/wyfs02/M02/58/45/wKiom1StB2_CvtWHAAG1-27PmYw794.jpg "border=" 0 "height=" 677 "/>

Step:

Right-click on the build script on the "Deadlock_monitor" session just created.

CREATE event SESSION [Deadlock_monitor] on Serveradd event Sqlserver.xml_deadlock_reportadd TARGET package0.event_file ( SET filename=n ' D:\MSSQL\DATA\MSSQL11. Mssqlserver\mssql\log\deadlock_monitor.xel ', max_file_size=, max_rollover_files= (Ten)) with (MAX_MEMORY=4096 KB , event_retention_mode=allow_single_event_loss,max_dispatch_latency=30 seconds,max_event_size=0 KB,MEMORY_ Partition_mode=none,track_causality=off,startup_state=off) GO

Step:

Right-click on the session "Deadlock_monitor" to select Start session.

Step:

Execute the following statements in two query Windows, respectively.

--window1use adventureworks2012begin tranupdate person.address SET AddressLine1 = ' New Address ' WHERE addressid = 20WAITFO R DELAY ' 0:0:10 ' SELECT * from person.address WHERE addressid = +--window 2USE adventureworks2012begin tranupdate person. Address SET AddressLine1 = ' New address ' WHERE addressid = 25WAITFOR DELAY ' 0:0:10 ' SELECT * from Person.Address WHERE Addr EssID = 20

steps:

Right-click on the Package0.event_file on "Deadlock_monitor" to select "View Target Data ...". Select the corresponding timestamp deadlock entry, in the details of the Xml_report value is displayed in the deadlock XML file, you can double-click Open. Click Deadlock to see a graphical display of deadlocks.

650) this.width=650; "title=" clip_image011 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image011 "src=" http://s3.51cto.com/wyfs02/M00/58/42/wKioL1StCDGQVkryAAKh3dmEMQM451.jpg "border=" 0 "height=" 446 "/>

650) this.width=650; "title=" clip_image012 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image012 "src=" http://s3.51cto.com/wyfs02/M00/58/45/wKiom1StB3KBQEQIAAU2aQobEiU181.jpg "border=" 0 "height=" 533 "/>

650) this.width=650; "title=" clip_image013 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image013 "src=" http://s3.51cto.com/wyfs02/M01/58/45/wKiom1StB3XxVnxvAAG3C4KmzAc905.jpg "border=" 0 "height=" 315 "/>

further advanced

Deadlock details There are several steps you can use to configure extended events to monitor deadlocks.

I want to discuss the other two events to capture more detailed information about the deadlock analysis.

1. Lock:deadlock event class

This event class can be used to verify the deadlock victim. This event indicates when a request requires a lock, but is canceled as a deadlock victim.

2. Lock:deadlock Chain event class

This event class is used to monitor the deadlock state. The event is triggered when there is a deadlock. By monitoring this event at the instance level, we are able to identify those objects in the deadlock, whether we have deadlock-causing performance problems in the application.

Step 1:

Right-select "Properties" on the previous "Deadlock_monitor" session. Select the Events page to add the Lock_deadlock and Lock_deadlock_chain event classes to the right-selected event list.

650) this.width=650; "title=" clip_image014 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image014 "src=" http://s3.51cto.com/wyfs02/M02/58/42/wKioL1StCDfArYKuAAPXpdu8Iro970.jpg "border=" 0 "height=" 673 "/>

Step 2:

Example of a deadlock before running.

Step 3:

Right-click on the Package0.event_file on "Deadlock_monitor" to select "View Target Data ...". Select the deadlock entry for the corresponding timestamp.

650) this.width=650; "title=" clip_image015 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image015 "src=" http://s3.51cto.com/wyfs02/M00/58/42/wKioL1StCDiT3e_BAAIxbsFIYNQ779.jpg "border=" 0 "height=" 496 "/>

650) this.width=650; "title=" clip_image016 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image016 "src=" http://s3.51cto.com/wyfs02/M01/58/42/wKioL1StCDjT1PhCAAIyjFeTPts077.jpg "border=" 0 "height=" 498 "/>

If user feedback says they found the deadlock information in the application's error log, it was late at night. We'll know how to monitor and retrieve the deadlock data.

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

SQL Server Extended Events (Extended events)--monitoring deadlocks with extended event tracking