Deadlock Issue

  • 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 ?

  • 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.

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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.

  • 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 on googles mail service

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply