SQL Server 2000 Deadlock

By zachi.fernaldes ·
Our production DB is SQL Server 2005 and there is a replication process to SQL Server 2000 DB, which is the base for the analysis processes.

Right after creating that replication all the problems have started.

Few SP which we used to run are so slow (1.5 hrs) up to impossible to run,
instead of few seconds' execution (before the replication was done).

The funniest thing is that running the content of that SP with its
required parameters will give us the results very quickly.

What shell I do in order to keep executing the SP in the way it used to
be before making that replication?

In addition sometimes I get the below deadlock error:
Server: Msg 1205, Level 13, State 22, Procedure SP_Name, Line 20
Transaction (Process ID 106) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I?ve try insert in the SP the NOLOCK hint, and SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, but there is no improvement.

PLEASE, what else can I do?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Some basics are required

by surajsomanna In reply to SQL Server 2000 Deadlock


Can u please tell me some thing about the architecture of the Windows Server Hardware, No. of instances and type of replication ?


Collapse -

Execution Plans could be out of date

by abroadway In reply to SQL Server 2000 Deadlock

If the contents of your sproc query is fast when run as a basic query (but the sproc is slow), then the EXECUTION PLAN for the Sproc is out of date:

(or "WITH SAMPLE 50 PERCENT" - depending on table size and the number of clustered PK's will effect the outcome of the Stats)

EXEC sp_recompile NAME_of_TABLE

Next time the Sproc runs it will recompile ready to run fast again.

This speed improvement will effect transactions times and minimise your DEADLOCK potential.

Also, when replicating data, ensure that you have set the replicated tables to use Indexes where their would otherwise have been PK. Primary Keys wont be replicated and so the Indexes instead will give your queries performance gains on replicated data.

Adam :)

Related Discussions

Related Forums