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

Deadlocks: Determining the victim with little info from Profiler... Expand / Collapse
Author
Message
Posted Thursday, July 26, 2012 5:59 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 11, 2014 4:23 PM
Points: 471, Visits: 1,308
Hello all -

So, I have been hunting down and resolving deadlock issues over the past week, but have come across one that is not giving me much info in the way of the PROC or Ad-Hoc query involved. See image below



As you can see - the 'Statement:' is blank, and even the line reference is pretty vague. I've also tried reading the info from the SQL Log by using Trace Flags -T1222 and -T3605, but no luck there either. The information gives me little to no detail as to what object is the actual victim. I know that there are 2 PROCs involved, but which is the actual deadlock victim is not readily apparent, and in this particular case - the best thing I can do is look at the index given (because it is in nearly every deadlock, but my remedy for that is to simply rebuild it, and not much more - no ad-hoc query listed to add a new index by, so nothing else that I know to do).

Any insight would be much appreciated.
Post #1336179
Posted Monday, July 30, 2012 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
Please save off the deadlock graph and attach it to this thread.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1337232
Posted Monday, July 30, 2012 11:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 11, 2014 4:23 PM
Points: 471, Visits: 1,308
Deadlock Graph...


  Post Attachments 
deadlock_sample.jpg (11 views, 680.16 KB)
Post #1337435
Posted Monday, July 30, 2012 1:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
No, not the picture again. The XML. That picture is rendered off an XML document which contains important troubleshooting information.

Right click the row in Profiler and click Extract Event Data..., save the file and attach it to this thread.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1337498
Posted Monday, July 30, 2012 5:19 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 11, 2014 4:23 PM
Points: 471, Visits: 1,308
Sorry about that - here ya go...

  Post Attachments 
Deadlock1.zip (11 views, 1.81 KB)
Post #1337620
Posted Monday, July 30, 2012 10:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
Open the xdl in Notepad to see the two statements involved.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1337654
Posted Tuesday, July 31, 2012 11:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 11, 2014 4:23 PM
Points: 471, Visits: 1,308
OK...I had seen this ad-hoc query in the mix of things in past deadlocks, but couldn't tell that it was the victim until now.

Thank you so very much for pointing this out opc.three! Now - I need to figure out if there is some way for me to get this to stop happening, because there is nothing I can do to improve this query as it is today (ran it through DTA with no improvements to be made).
Post #1338061
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse