Using SQL Profiler to Resolve Deadlocks in SQL Server

Question

Post reply

Using SQL Profiler to Resolve Deadlocks in SQL Server

Jonathan Kehayias

One Orange Chip

Points: 26778
More actions
February 1, 2009 at 10:07 pm

#204897

Comments posted to this topic are about the item Using SQL Profiler to Resolve Deadlocks in SQL Server
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

Viewing 15 posts - 1 through 15 (of 47 total)

You must be logged in to reply to this topic. Login to reply

SQL Joker Ten Centuries Points: 1256 More actions · Answer 1

Excellent Article. Thank you for the techniques that you shared to track and resolve dead locks.

With very best regards,

Sreeju

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Nice one. Sharp & direct.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Mike Good SSCertifiable Points: 7415 More actions · Answer 3

Ditto the other comments. This is a superlative article. Thank you.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Nice article. Although you just reduced the # articles in the series on profiler I am working on by 1.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

Sorry bout that Jack. I am sure that you could add to the basics that are covered by this article though. I tried to follow KISS for this introduction and then I write a few other articles that cover deadlocks from other angles as well. More to come.....

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Mine was gonna be pretty much the same. It's my own fault for not buckling down and getting it done. There is still plenty to cover in Profiler and SQL Trace.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

I think any discussion on deadlock troubleshooting should reference this blog series: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

There is actually more than just that link:

http://blogs.msdn.com/bartd/archive/tags/SQL+Deadlocks/default.aspx

Bart Duncan has a bunch of them and the above reference is on another article I put in, but I guess I missed adding it to this one.

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 9

Took me a minute to overcome the 'owner' and 'request' jargon in the deadlock graph.

To me they're all locks and blocks.

So an 'owner' arrow could by paraphrased as 'blocked by', and the request arrow is just the lock request?

:rolleyes:

I'm spoiled with some nice 3rd party tools, but the profiler deadlock graph looks like something I can use.

Thanks for the great article.

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Adam Haines SSC-Insane Points: 23217 More actions · Answer 10

Great article Jonathan! The article is easy to read, informative, and concise.

Great Job.

My blog: http://jahaines.blogspot.com

cutedeveloper Valued Member Points: 71 More actions · Answer 11

Guys,

I have a problem to find "Deadlock Graph" event at SQL Profiler. I see Deadlock and DeadLock chain but

Deadlock Graph is missing...

I have checked SQL 2000 and SQL2005 profiler, both are the same story...

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 12

You must be connecting to a SQL 2000 server which doesn't have that event. In SQL Server 2000, you have to enable Trace Flag 1204 to get the deadlock graph through the ErrorLog.

dbcc traceon(1204, -1)

cutedeveloper Valued Member Points: 71 More actions · Answer 13

i am sorry. I just did it but still cannot see graph at the profiler...

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 14

Read my response again. Profiler isn't going to give you a graph from SQL Server 2000. That is only applicable to SQL Server 2005/2008.