Dead Locks
-
Hi,
I do have an enquiry. We are running on SQL 2000, but we faced a lot of deadlock issues. What are the features that is avaliable in SQL 2000 to reslove this problem or what will be a good suggestion to reslove this deadlock issues.
Thank you in advance.
Kelvin
-
Hi Kelvin,
I know your situation well. I was faced with this same task!
My first step, would be to run a trace on your database using profiler.
Typically, when tracing for deadlocks, I would do the following...
In the Events Tab monitor...
TSQL=>SQL:Batch Completed
Locks=>Lock:Deadlock
Locks=>Lock:Deadlock Chain
In the Filters tab, I would put in the DBID to ensure you only get statements for a specific DB, that is if your not doing cross database joins?
Also, to alert you of deadlocks, you could create an Alert to provide a notification of Error 1205. When you get the notification, you can then note the time and go into profiler and track back to the deadlock to investigate the queries.
Hope thats enough to get you moving in the right direction!
Clive Strong
-
Agree. Turning on the trace flag is a great first step. Typically deadlocks are driven by data access patterns as they relate to indexes. If you can determine which indexes are involved you can look at either changing how they are used or changing the index.
Andy
-
Here's the Microsoft KB article on understanding blocking in SQL Server 7.0/2000. It also has links to how to monitor blocking in both versions.
http://support.microsoft.com/default.aspx?scid=kb;en-us;q224453
The amount of information may be overkill for your needs, but it's a good article to have tucked away.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
A solution we have used to reduce dead locks is to use the join hint WITH (NOLOCK). We place this after each table name in a SELECT statement. For example:
SELECT *
FROM titles WITH(NOLOCK)
Be aware that this allows what is called dirty reads. So if modifications are happening to the table at the time the un committed modifacations can show in the result set. Since our data doesn't change much and when it does it is only one row at a time the WITH (NOLOCK) hint was a good solution for us.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply