SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlock Issue


Deadlock Issue

Author
Message
lianvh 89542
lianvh 89542
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 723
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 ?
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6329 Visits: 2774
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.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96689 Visits: 33013
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Neeraj Dwivedi
Neeraj Dwivedi
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 1341
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.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31589 Visits: 8671
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search