Capturing deadlocks in sql profiler

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

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

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.
SELECT object_name([object_id])
WHERE hobt_id = 72057594041663488
6. The arrows represent types of locks each process has on each resource node.

[ukd]

A beautiful clear and understandable English. Clearly discussed, huge thanks for this movie!!

[vishroxx]

"Thank you very much" for explaining complex topic with easy example and useful information.

[hemanthu]

Thanq so venkat garu, a crystal clear expalnation to get into. lot f thanks for sharing .

[MohammedFadhul]

Thanks a lot for such a great video. Really appreciated brother

[danezubro]

Excellent joob! Keep doing such videos :) !

[krzysztofs]

Wow! Thank U Venkat . next video session ended.

[Ravivaraavlogs]

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

[anilkumarpandey]

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.

[ramachandranm]

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..

[atul]

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

[stanleytoles]

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?

[praveenshetty]

Hi Venkat
Can you add tutorial on bottle necks in SSIS/SQL

[rohinithakre]

Hello Sir,
Do you have any link which can help us with SQL Or PL/SQL interview Q&A like a play list??

[mummz]

Venkat, I would like to meet you and have a beer with you one day <3