When publishing the EXECUTION of a Stored Procedure wrap in Transaction

When Replication the EXECUTION of a stored procedure to a subscriber select the “serialized transaction” and and wrap the SP execution within a transactions.

This ensures the changes are replicated as a single stored procedure execution and not as individual row-level updates.

See: Publishing Stored Procedure Execution in Transactional Replication

“The serializable option is recommended because it replicates the procedure execution only if the procedure is executed within the context of a serializable transaction. If the stored procedure is executed from outside a serializable transaction, changes to data in published tables are replicated as a series of DML statements. This behavior contributes to making data at the Subscriber consistent with data at the Publisher. This is especially useful for batch operations, such as large cleanup operations.”

For example, let’s say we’re running a script to call stored procedure [sp_fixaccount] passing an [accountnum] and the stored procedure is expected to update  26,601 rows for each account.  Our objective is to execute the CALLING of the stored procedure on both the Publisher and the Subscriber, not the 26,601 updates.

To accomplish this wrap the [sp_fixaccount] in a transaction.

BEGIN loop logic
    BEGIN TRAN
        EXEC sp_fixaccount [accountnum]
    COMMIT TRAN
END loop logic

Using Replication Monitor you’ll see a single TRANSACTION (4:12:17 PM) being replicated instead of the previous 26,601 individual row updates (4:09:56 PM).  This will significantly reduce the Transactional Replication overhead.