Deadlocks rate

Question

Deadlocks rate

Niyala

SSCrazy

Points: 2778
More actions
August 11, 2008 at 8:17 am

#130632

Hi all,
I have the following error message which I am unable to understand how the rates are estimated.
DBSPI-3271.1: Deadlocks rate for object _Total (110.00/sec) too high (>=3.00/sec)
I would very grateful if anyone can give me a clue about what this rate is.
Many thanks indeed

Viewing 9 posts - 1 through 8 (of 8 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Looks to me like you're getting 110 deadlocks per second. That's quite a few.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Niyala SSCrazy Points: 2778 More actions · Answer 2

Thanks for your response to my trouble.

I still do not understand which ranges are negligible, bad, or worse. Is there a generl rule of thumb to classify the dedlocks? I went through all pages of the book "Administrator's companion" by Whalen et al. (2007), but I could not find a hint on this in the section dealing with deadlocks.

Thanks indeed

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

Deadlocks are generated by queries. If there are many SELECT queries, you may try to use NOLOCK clause (or change the isolation level) to reduce the deadlock rate. But you need to do some tests because the side effect of NOLOCK clause is dirty data.

Niyala SSCrazy Points: 2778 More actions · Answer 4

Niyala

SSCrazy

Points: 2778

August 11, 2008 at 8:54 am

#856134

Thanks. Good hint to get me started!

noeld SSC Guru Points: 96590 More actions · Answer 5

You should implament an SQL Trace to determine which are your most frequent deadlocks and SOLVE them.

* Noel

Wesley Brown SSChampion Points: 12045 More actions · Answer 6

Wesley Brown

SSChampion

Points: 12045

August 11, 2008 at 12:01 pm

#856281

http://support.microsoft.com/kb/832524

Niyala SSCrazy Points: 2778 More actions · Answer 7

Hi, all,

Thanks to all who gave me useful hints on how to work out this problem. I will try all your suggestions soon.

G'day

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Switch traceflag 1204 or 1222 on (DBCC TRACEON (1222,-1)). That will write the deadlock graphs into the error log. With that you can see what is deadlocking and on what resource. Alternative is to run profiler/SQL Trace and capture the deadlock graph event. Same info, a different way

Once you know what two (or more) processes are causing the deadlocks, then you can look at fixing them. Often its just a case of optimising the code in question.

If you have questions on the deadlock graph or the fixing of code, post here. There are a few people here who love to help out with that kind of thing.

Don't go adding nolock everywhere. It may not help, since deadlocks shouldn't occur with straight selects, but usually involve one of more data modification statements as well.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass