Sql-server – Transactional Replication Update process

To paraphrase the quoted block, with immediate update the only guarantee is that the commit is immediately updated to the publisher. The changes will be sent to the other subscribers in due course.

Without immediate updating, your changes are only committed to the local subscriber, and queued for distribution back to the publisher, and following that, the other subscribers.

In both cases there is a period of time when a stale record is still at the other subscribers, and one of those subscribers may update the stale record and cause a conflict.

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.