Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I 'm testing implementing transactional replication . The replication works fine if I insert/update handful of rows. The problem occurs when I have a sql job running which deletes/updates & inserts 1000's 15000 plus rows in each database(more than 100) on the server. And this job runs every n minutes. It eventually causes locking issues and slows down replication.

The main objective of doing replication is to able to scale horizontally and also improve performance. Following is a quick overview of test environment

2 database server Each server has 100 unique databases & 5 shared database(which are replicated) Any suggestion how to get around the locking issues?

Thanks

share|improve this question
    
Have you tried wrapping your DML into a stored procedure and then just replicating the stored procedure call(s) to the subscribers? –  Thomas Stringer Aug 19 '13 at 15:18
    
I 'm using a stored procedure. I think its the sheer number of transactions that occur is what causing the problem –  jsp Aug 19 '13 at 15:19
    
But are you replicating the stored procedure call? Or are you replicating just the data changes? Those are two very different things. msdn.microsoft.com/en-us/library/ms152754.aspx –  Thomas Stringer Aug 19 '13 at 15:20
    
@jsp You can do 2 things with Replication Statement Delivery Options - a) Do not replicate DELETE statements or if you want to replicate delete statemets then choose b) call <stored procedure>. Also, replicating 100 databases is a lot depending on how much data you are replicating (whole database or some tables) + the network bandwidth. –  Kin Aug 19 '13 at 15:33

1 Answer 1

There are two steps to consider when tuning replication:

  1. Reduce the number of commands to replicate
  2. Reduce the time each command takes

The first one can be achieved by grouping multiple statements in a stored procedure and then replicate each procedure execution as a single command instead of multiple separate commands. @ThomasStringer recommended http://msdn.microsoft.com/en-us/library/ms152754.aspx as a good starting point to learn about this feature.

The second can often be achieved by tuning the Distributor. The Distributor is doing most of the work in transactional replication. But it is also the most neglected part in replication. Often then Publisher is tasked to do the work of the Distributor too. If the Publisher has a lot to do already to keep up with the application workload, It cannot handle the additional work required to execute the distribution tasks. In high performance environments it is therefore a good idea to make the Distributor its own machine with its own disk. This will reduce CPU load, Disk load and network load on the Publisher.

If that is not possible make sure the distribution database is on a different spindle than the application database. You can also try to setup the subscription(s) as pull subscriptions instead of push subscriptions.


The above are general recommendations. As always, first make sure you understand what the root cause is. You can use sys.do_os_wait_stats and sys.dm_db_virtual_file_stats to see wether your disks are to slow, the processor is overloaded you have memory pressure or the network is to slow. Then look at the recommendations above to see what can help in your case.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.