Improve Replication Performance

Question

We have a lot of tables depending on each other. Instead-of-delete triggers have been set in place to handle the proper deletion of necessary data. We cannot use cascading deletes since we have circular references.

Some tables from main application DB need to be replicated in another DB. Because there are some scenarios in which a lot of delete commands are queuing on subscribers (taking a lot of time to execute), we need a solution to improve the performance of the replication system.

What do you think that the best solution would be?

LaurentiuM

Answer 1

Must read:

http://blogs.msdn.com/john_daskalakis/archive/2009/01/16/9326860.aspx

HTH
Vinay

---

Vinay Thakur http://vinay-thakur.spaces.live.com/ http://twitter.com/ThakurVinay

Answer 2

Have you thought about using replicating stored procedures instead of replicating just the tables involved? With this method when you call a stored procedure that does lots of data modifications (in your case deletes) instead of replicating each delete statement, it just replicates the stored procedure in 1 command which then performs all the deletes on the subscriber.

There is an article about it at http://www.sql-server-performance.com/articles/dba/replicating_sp_execution_p1.aspx which although uses sql 2000 because it uses commands to set up all the replication it will work with sql 2005 or sql 2008

Anthony

Answer 3

Anthony,

In my case the deletes are nested. Will this replicated stored procedures work?
e.g.
The deletion of a record from table A will delete data from table B before deleting the record from table A.
The deletion of a record from table B will delete data from table C before deleting the record from table B.
The deletion of a record from table C will delete data from table D before deleting the record from table C.
This is currently handled using nested triggers.

I moved the trigger code in stored procedures published as 'proc exec':
SP_A deletes records from table B.
SP_B deletes records from table C.
SP_C deletes records from table D.
Now
The deletion of a record from table A will call the SP_A before deleting the record from table A.
The deletion of a record from table B will call the SP_B before deleting the record from table B.
The deletion of a record from table C will call the SP_C before deleting the record from table C.

On the main DB when I delete a record from table A, the triggers are in place and all SPs are called (deleting records from B, C and D). But on the replication side, only the records from tables A and B are deleted.

Any idea why the SP_B and SP_C are not called on replication side?

LaurentiuM

Answer 4

Is it necessary to have the triggers enabled on the subscriber?  The changes that happen on the publisher should automatically flow to the subscriber.