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

Deadlock Issue Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 1:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:18 AM
Points: 47, Visits: 383
We are running SQL Server 2008R2 Ent and we are experiencing a high incident rate of Deadlocks . We started by adding some of the missing indexes . This practice did help to resolve some of the deadlocks. However we get a Clustered Index Update of 86% of the processing time during One particular Transaction . I had a look at the update statement and there is not much we can dot to optimize it as it is a simple update statement : " Update Table1 set column1 = Getdate() , column2 = 'Name1' where column 3 = 99999.
This transaction causes the deadlock . Any ideas to resolve this issue ?
Post #1449611
Posted Monday, May 6, 2013 1:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
Good if you are able to regenerate the issue. Setup the profiler with deadlock events under lock event groups along with deadlock graphs. Run the profiler and start generating the deadlock event. It'll give you a very clear picture of whats causing the deadlock.
Post #1449613
Posted Monday, May 6, 2013 5:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 15,661, Visits: 28,051
A single straight update statement all by itself can't cause a deadlock with another update/insert/delete statement. So, other statements within these query batches must be involved. Since you're on 2008, you can use the system_health extended event session to look at the deadlock graphs. That will tell you the other statement where the deadlock is occurring. What's likely is that you're doing two statements that involve both reading from the table and writing to it, but they are doing those reads and writes in different order. Understand all the statements involved and you'll be more likely to arrive at the solution to the deadlock.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1449678
Posted Monday, May 6, 2013 1:06 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:30 AM
Points: 811, Visits: 1,165
As Grant Fritchey said single update statement can not cause deadlock to itself. So run sp_who2 which will give you what processes are running at the time. Look for blyby column. Look which SP ID this update statement has, and see if there is any non zero value in bklby Column. If you see something there, see if that SP ID is blocked by other ans so on.

You can get the text of SP ID by runinning dbcc inputbuffer (spid).

You can run profiler trace as sqlnaive sugegsted and also run DMV's as Grant said. But SP_who2 is the first palce where I would start.

Post #1449848
Posted Tuesday, May 7, 2013 6:53 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 4,350, Visits: 6,162
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Note there are 2 additional parts to this blog series.

There is also several Profiler Events under the Locks category that can show detailed information about deadlocks.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1450110
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse