Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SQL Profiler to Resolve Deadlocks in SQL Server


Using SQL Profiler to Resolve Deadlocks in SQL Server

Author
Message
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2450 Visits: 3135
Great article Jonathan! The article is easy to read, informative, and concise.

Great Job.



My blog: http://jahaines.blogspot.com
cutedeveloper
cutedeveloper
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 140
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
Jonathan Kehayias
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2002 Visits: 1807
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)



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
cutedeveloper
cutedeveloper
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 140
i am sorry. I just did it but still cannot see graph at the profiler...
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2002 Visits: 1807
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.

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
cutedeveloper
cutedeveloper
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 140
OK. Got it. Thank you.
(there was a lapse in your previous post)
Gerrard8
Gerrard8
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 99
Good Article,simple and easy to read.

The link of upstairs reply is also usefully !!
sanokistoka
sanokistoka
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 15
The claim "that a deadlock in and of itself is not necessarily a problem" and suggestion that "A properly designed and coded application ... can intercept the 1205 error and resubmit the deadlock victim request back to SQL Server" is forgetting a few basic things. Imagine, for example, if all client applications to any server they talk to had to retry their requests all the time - like, say, your web browser to any web server? What was rather meant, I hope, is that a properly designed database would never get you into unexpected deadlocks.
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2002 Visits: 1807
sanokistoka (10/22/2009)
The claim "that a deadlock in and of itself is not necessarily a problem" and suggestion that "A properly designed and coded application ... can intercept the 1205 error and resubmit the deadlock victim request back to SQL Server" is forgetting a few basic things. Imagine, for example, if all client applications to any server they talk to had to retry their requests all the time - like, say, your web browser to any web server? What was rather meant, I hope, is that a properly designed database would never get you into unexpected deadlocks.


Nope, I said what I meant, and I meant what I said there.

A properly designed application would have handling in place to deal with a deadlock occurence. Even in the worst instance of deadlocking I have dealt with while consulting, and deadlocks were occuring roughly every 3-5 seconds, this was a very small portion of the overall workload that was actually being submitted. It's not that every request has to be retried, or that they have to retry all the time. The point being made is that the database transaction problem of a deadlock shouldn't break the application on the front end. That is bad application side design coupled with potentially problemattic database design as well.

These days not all deadlocks are preventable. Communication Buffer deadlocks can occur infrequently that you won't fix because the lock is not on a resource you can control or change. Intraquery parallelism deadlocks are also becoming more commonplace due to the reduction of cost for multi-core multi-processor hardware. Those aren't database design problems, they are configuration problems. A properly designed database on a improperly configured SQL Server can most definately deadlock unexpectedly. Which is why the application should be coded to handle that.

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
sanokistoka
sanokistoka
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 15
Have you tried Oracle, which doesn't exhibit, say SELECT/UPDATE deadlocks because of row versioning? Thankfully, there is now row versioning in MSS. The point being is that, yes an application must be coded to handled deadlocks it expects *where* it expects them, but MSS is causing an egregious number of unexpected deadlocks, and I wonder if that's due to page locking and/or lock escalation policies. What's your experience with Oracle and how does it compare with MSS when it comes to unexpected deadlocks?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search