How to debug timeout/deadlock problems on a table in SQL 2000?

Question

How to debug timeout/deadlock problems on a table in SQL 2000?

Pete T-366679

SSCrazy

Points: 2065
More actions
October 16, 2008 at 10:22 am

#94676

We are running SQL 2000 on a Win2k3 Server, and think our database might be starting to show some scalability problems.
We have a table that drives a main part of our application. It is inserted and updated a lot. We have seen deadlock errors in the past, and they seem to happen more often in recent months than they had before. Lately, we seem to be experiencing more and more timeout problems involving this table, both on updates and inserts.
We have application error handling that reports us of timeout errors, but I don't ever see anything in the SQL logs. Even the deadlocks don't show up in the SQL logs. Is there a way to log things like this in SQL Server, or any suggestions on tracking down what might be the problem?
Thanks for any ideas!

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

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

use lock hints and also see if the timeout setting is too low. We ran into this problem where my DB timeout setting was infinite but the application timeout in the ini file was 300 seconds and to our dismay the query takes 600 seconds to do its part from a critical table in the DB, we increased the ini timeout and then never had a problem. Check all the processes that access this table and track down the ones that get deadlocked frequently and try to fix the code...Good Luck!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

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

Don't use locking hints, at least not as the first thing you try

Frequent blocking and deadlocks are usually caused by bad code, bad indexes or both. See if you can find what procedures are frequently involved in the blocking and see if you can optimise them.

As for deadlocks, switch traceflag 1204 on (DBCC TRACEON(1204,-1) or -T1204 in SQL's startup parameters). This will result in the deadlock graph been written to the error log. With the deadlock graph, you should be able to pinpoint the cause of the deadlock and fix it. Again, fix the code, tune the indexes.

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

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 3

Thanks! I'll turn the deadlock flag on....I'm pretty sure it's definitely a code and/or index problem (it's a legacy VB6 app I've inherited), but trying to pinpoint where things are happening has been tough. Thanks again for all the help!

U J SSCrazy Points: 2423 More actions · Answer 4

You can also add trace flag 1205 along with the 1204 trace flag which will more details about the deadlock..

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Here is the Bible for deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 6

We enable deadlock detection at all our instances using:

startup parameters -T1204 and -T3605

so it gets recorded in the sqlserver errorlog file.

/* to start deadlock detection and recording without stop/start of the instance */

dbcc traceon(1204, 3605, -1)

/*

-- sql-log-info

-- 2006-06-28 14:16:28.00 spid4 KEY: 20:2121058592:3 (5b027ea21b78) CleanCnt:1 Mode: X Flags: 0x0

--

-- key: 20:2121058592:3

-- database 20, object id 2121058592, indid 3

-- select db_name(20) -- = DOrderManagement

Declare @DeadlockKey varchar(128)

Set @DeadlockKey = '20:2121058592:3'

Declare @DbIdNr int

Declare @TbIdNr int

Declare @IxIndIdNr int

Select @DbIdNr = substring(@DeadlockKey,1,convert(int,charindex(':',@DeadlockKey,1)-1))

, @TbIdNr = substring(@DeadlockKey,charindex(':',@DeadlockKey,1)+1, charindex(':',@DeadlockKey,charindex(':',@DeadlockKey,1)+1) - charindex(':',@DeadlockKey,1) - 1 )

, @IxIndIdNr = substring(@DeadlockKey, charindex(':',@DeadlockKey,charindex(':',@DeadlockKey,1)+1) + 1, 25 )

If db_id() = @DbIdNr

begin

select db_name(@DbIdNr) as Deadlock_Database_Name

, object_name(@TbIdNr) as Deadlock_Object_Name

, [name] as Deadlock_Index_Name

, indid

from sysindexes

where id = @TbIdNr

and indid = @IxIndIdNr

order by indid

end else

begin

Select 'USE ' + db_name(@DbIdNr) as Deadlock_Database_Name

end

*/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

nice little snipped there! 😉