<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Praveen Voleti
»
A Deadlock Occurence and Resolution
33 posts, Page 1 of 4
1
2
3
4
»
»»
A Deadlock Occurence and Resolution
Rate Topic
Display Mode
Topic Options
Author
Message
praveen voleti
praveen voleti
Posted Tuesday, February 03, 2009 1:12 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, June 19, 2012 6:06 AM
Points: 33,
Visits: 177
Comments posted to this topic are about the item
A Deadlock Occurence and Resolution
Post #648621
MarkusB
MarkusB
Posted Tuesday, February 03, 2009 5:03 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 4,222,
Visits: 3,884
Very good article.
Markus Bohse
Post #648723
Jeremy Huppatz
Jeremy Huppatz
Posted Tuesday, February 03, 2009 6:12 AM
Old Hand
Group: General Forum Members
Last Login: Monday, June 07, 2010 11:42 PM
Points: 314,
Visits: 6
Just a quick one - don't forget that SQL Server isn't the only source of deadlocks. I recently helped out on an issue where a single-threaded application DLL on one thread was blocked by a SQL Server lock, but at the same time, access to the application DLL from the locking process was also blocked. No deadlock victim, just timed-out queries and a lot of pain to the system's users.
Classic deadlock... but only one of the locks was within SQL Server. 1204 is great, but troubleshooting SQL Server deadlocks is simple compared with tracking down the problem I was helping to diagnose.
Post #648764
Gerhard Schmeusser
Gerhard Schmeusser
Posted Tuesday, February 03, 2009 7:31 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, September 07, 2012 12:30 PM
Points: 37,
Visits: 136
Nice how-to article. But I wonder why Microsoft does not provide better support for deadlock situations. Hunting SQL server deadlocks is still like in the stone age days of programming ( in the above article even with non-documented features). Assumed SQL Server "knows" the reason, why not simply tell it?
Post #648842
bondistrict
bondistrict
Posted Tuesday, February 03, 2009 8:32 AM
Ten Centuries
Group: General Forum Members
Last Login: Friday, July 15, 2011 1:21 PM
Points: 1,181,
Visits: 24
;) Nice article, thanks for sharing!
Post #648899
sureshot
sureshot
Posted Tuesday, February 03, 2009 8:39 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, August 07, 2012 9:00 PM
Points: 5,
Visits: 143
Great article on investigating the deadlock. Just a note that if you're running SQL 2005/2008, the trace flag 1222 (http://msdn.microsoft.com/en-us/library/ms178104.aspx) provides a lot more information so you don't have to do quite a much undocumented digging (though I found it very interesting).
Instead of the trace flag, I'd personally recommend setting up WMI to log the deadlock info: http://technet.microsoft.com/en-us/library/ms186385(SQL.90).aspx.
Post #648907
Jonathan Kehayias
Jonathan Kehayias
Posted Tuesday, February 03, 2009 11:21 AM
SSCommitted
Group: General Forum Members
Last Login: Monday, June 03, 2013 7:03 PM
Points: 1,696,
Visits: 1,746
sureshot (2/3/2009)
Great article on investigating the deadlock. Just a note that if you're running SQL 2005/2008, the trace flag 1222 (http://msdn.microsoft.com/en-us/library/ms178104.aspx) provides a lot more information so you don't have to do quite a much undocumented digging (though I found it very interesting).
Instead of the trace flag, I'd personally recommend setting up WMI to log the deadlock info: http://technet.microsoft.com/en-us/library/ms186385(SQL.90).aspx.
The WMI alerts can actually be a bit more resource intensive and problemattic to setup. There are a number of more moving parts involved with the WMI alerts where as the Trace Flags or a SQL Trace has minimal configuration required. In the end, if you use Trace Flag 1222 in SQL Server 2005/2008, the deadlock graph is the same from WMI, SQL Trace, Profiler, and the Trace Flags.
SQL 2008 offers Extended Events, watch for an upcoming article that I have already submitted which capture deadlock information natively while your server is running. Watch out though, the deadlock information changed to a new format in SQL 2008 that is only output by Extended Events and only Extended Events can capture and display a multi-victim deadlock, which is why there was a change made to the deadlock graph formatting in 2008 from Extended Events.
As I mentioned yesterday, deadlocks aren't necessarily the problem. If your app is coded correctly it can handled the error raised and rerun the statement and it will most likely succeed on the second attempt.
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 #649145
Dallas Martin
Dallas Martin
Posted Tuesday, February 03, 2009 11:53 AM
Grasshopper
Group: General Forum Members
Last Login: Friday, January 28, 2011 12:21 PM
Points: 14,
Visits: 33
SQL 101 - Always make sure your tables have indexes to avoid full table scans.
Looks as though the DBA and/or developers need a little coaching.
Post #649167
sureshot
sureshot
Posted Tuesday, February 03, 2009 12:00 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, August 07, 2012 9:00 PM
Points: 5,
Visits: 143
Very interesting news about SQL 2008 and Extended Events. I'm looking forward to when we can upgrade our production servers to that.
For now the WMI deadlock system has provided a nice and easy to read history of deadlocks for us and we've hooked in an e-mail notification as well. CPU usage for WMI is roughly 2-3% on our box and we have capacity to spare so it's been worth it.
Post #649174
DataDog
DataDog
Posted Tuesday, February 03, 2009 12:10 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 7:07 AM
Points: 146,
Visits: 421
SQL 2005 has xml deadlock report in profiler
which will track deadlock down to tables and stored procedure statements
I think your solution does not solve the deadlock - just makes it less likely
If the db+app had been designed correctly the omission of the index would have reduced performance but not created deadlocks
Post #649185
« Prev Topic
|
Next Topic »
33 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
