Executing batch UPDATE commands on Publisher and its affect on Transactional Replication

José Moreira Neto | Microsoft SQL Server Escalation Services

Problem 1: batch updates

1)      A single update command can generate a transaction with thousands of commands. For example if you execute an UPDATE statement that updates 10K rows on the publisher, up to 20K commands can be send over to the subscriber. On the publisher this can look like a single statement, but needs to be replicated as multiple commands to the subscriber.

For example, assume you create the following stored procedure in the publication database:

CREATE PROC give_raise AS

UPDATE EMPLOYEES SET salary = salary * 1.10

This procedure gives each of the 10,000 employees in your company a 10 percent pay increase. When you execute this stored procedure at the Publisher, it updates the salary for each employee.

BEGIN TRAN

UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = ’emp 1′

UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = ’emp 2′

COMMIT TRAN

Note that transaction integrity needs to be maintained, so a single update can generate a very large transaction on the subscriber. All commands in the transaction (which can add up to hundreds of thousands) must complete for the transaction to commit.

Problem 2: Updates to Primary Keys

1)      Every row updated on the publisher can also generate up to 2 commands (DELETE/UPDATE) on the subscriber

For example, Table T1 has column C1 (pk) , C2 and 1 row with C1= 1, C2 = ‘value 1’. Subscriber1 performs a synchronous update that sets C1 = 100. The transaction is accepted. Because the primary key or unique index is updated, it is processed as a deferred update. The distribution database has the following transaction:

Delete T1 where C1 = 1

Insert T1 (C1, C2) values (100, ‘value 1’)

Solutions:

1) Publish stored procedure EXECUTION, not the results: With the replication of stored procedure execution, replication sends only the command to execute the stored procedure at the Subscriber, rather than writing all the updates to the distribution database and then sending them over the network to the Subscriber.

See: Publishing Stored Procedure Execution in Transactional Replication

2) A single UPDATE statement can generate a large transaction in the subscriber. You can break this large transaction into batches of smaller transactions at the Log Reader Agent with parameter MaxCmdsInTran.  However, these new batches will be replicated as individual transactions and could result in partial subscriber updates should the Distribution Agent stop while applying the new individual batches.

See: Enhancing Transactional Replication Performance