Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Using SQL Profiler to Resolve Deadlocks in SQL Server Expand / Collapse
Author
Message
Posted Tuesday, February 3, 2009 8:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:58 AM
Points: 2,280, Visits: 3,069
Great article Jonathan! The article is easy to read, informative, and concise.

Great Job.




My blog: http://jahaines.blogspot.com
Post #648873
Posted Friday, February 20, 2009 10:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:15 AM
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...
Post #661530
Posted Friday, February 20, 2009 11:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #661545
Posted Friday, February 20, 2009 11:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:15 AM
Points: 7, Visits: 140
i am sorry. I just did it but still cannot see graph at the profiler...
Post #661552
Posted Friday, February 20, 2009 11:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #661556
Posted Friday, February 20, 2009 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:15 AM
Points: 7, Visits: 140
OK. Got it. Thank you.
(there was a lapse in your previous post)
Post #661566
Posted Monday, May 4, 2009 12:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 7:44 PM
Points: 1, Visits: 99
Good Article,simple and easy to read.

The link of upstairs reply is also usefully !!
Post #709207
Posted Thursday, October 22, 2009 2:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 26, 2009 7:03 AM
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.
Post #807451
Posted Thursday, October 22, 2009 2:36 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #807454
Posted Thursday, October 22, 2009 2:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 26, 2009 7:03 AM
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?
Post #807461
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse