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?
Thanks,
Zachi.
