Capturing deadlocks in sql profiler

Công Nghệ

deadlock graph sql server 2008
how can you get a deadlock graph from sql server
sql server profiler deadlock graphs
sql server deadlock graph xml
how to read deadlock graph
deadlock graph analysis
deadlock graph analyze
analyze deadlock graph sql server

Text version of the video


All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

In this video we will discuss how to capture deadlock graph using SQL profiler.

To capture deadlock graph, all you need to do is add Deadlock graph event to the trace in SQL profiler.

Here are the steps :
1. Open SQL Profiler
2. Click File – New Trace. Provide the credentials and connect to the server
3. On the general tab, select “Blank” template from “Use the template” dropdownlist
4. On the “Events Selection” tab, expand “Locks” section and select “Deadlock graph” event
5. Finally click the Run button to start the trace
6. At this point execute the code that causes deadlock
7. The deadlock graph should be captured in the profiler

The deadlock graph data is captured in XML format. If you want to extract this XML data to a physical file for later analysis, you can do so by following the steps below.
1. In SQL profiler, click on “File – Export – Extract SQL Server Events – Extract Deadlock Events”
2. Provide a name for the file
3. The extension for the deadlock xml file is .xdl
4. Finally choose if you want to export all events in a single file or each event in a separate file

The deadlock information in the XML file is similar to what we have captured using the trace flag 1222.

Analyzing the deadlock graph
1. The oval on the graph, with the blue cross, represents the transaction that was chosen as the deadlock victim by SQL Server.
2. The oval on the graph represents the transaction that completed successfully.
3. When you move the mouse pointer over the oval, you can see the SQL code that was running that caused the deadlock.
4. The oval symbols represent the process nodes
a) Server Process Id : If you are using SQL Server Management Studio you can see the server process id on information bar at the bottom.
b) Deadlock Priority : If you have not set DEADLOCK PRIORITY explicitly using SET DEADLOCK PRIORITY statement, then both the processes should have the same default deadlock priority NORMAL (0).
c) Log Used : The transaction log space used. If a transaction has used a lot of log space then the cost to roll it back is also more. So the transaction that has used the least log space is killed and rolled back.
5. The rectangles represent the resource nodes.
a) HoBt ID : Heap Or Binary Tree ID. Using this ID query sys.partitions view to find the database objects involved in the deadlock.
SELECT object_name([object_id])
FROM sys.partitions
WHERE hobt_id = 72057594041663488
6. The arrows represent types of locks each process has on each resource node.


Xem thêm bài viết khác:

16 thoughts on “Capturing deadlocks in sql profiler

  1. can we make this trace flag in running condition in background always in prod enviornments ??
    will it impact on performance ?

  2. Hi Venkat sir
    Thanks for ur excellent tutorial. Its really helpful
    I may request you to capture a video for database replication and log shipping concepts.

  3. in the deadlocks I am looking into I have one box that says key lock and another that says object lock… what is the difference?

  4. Hi venkat sir,

    you are teaching very well sir. I have one question sir hope you answer my question.
    I could not find the SQL server profiler from tools sir, and also I tried by right click on object explorer to Trace process in SQL server profiler. But its not working,it shows JITdebugging is enable. Then i followed MSDN step to disable JIT even though its not working.I hope on you help me sir..

  5. Hi Excellent works sir and if u dont mine can u make it about Execution Plan (ie) Actual and Estimated Execution Plan

Leave a Reply

Your email address will not be published. Required fields are marked *