Enhancing Transactional Replication Performance
After considering the general performance tips described in Enhancing General Replication Performance, consider these additional areas specific to transactional replication.
Database Design
Minimize transaction size in your application design.
By default, transactional replication propagates changes according to transaction boundaries. If transactions are smaller, it is less likely that the Distribution Agent will have to resend a transaction due to network issues. If the agent is required to resend a transaction, the amount of data sent is smaller.
Distributor Configuration
Configure the Distributor on a dedicated server.
You can reduce processing overhead on the Publisher by configuring a remote Distributor. For more information, see Configuring Distribution.
Size the distribution database appropriately.
Test replication with a typical load for your system to determine how much space is required to store commands. Ensure the database is large enough to store commands without having to auto-grow frequently. For more information about changing the size of a database, see ALTER DATABASE (Transact-SQL).
Publication Design
Replicate stored procedure execution when making batch updates to published tables.
If you have batch updates that occasionally affect a large number of rows at the Subscriber, you should consider updating the published table using a stored procedure and publish the execution of the stored procedure. Instead of sending an update or delete for every row affected, the Distribution Agent executes the same procedure at the Subscriber with the same parameter values. For more information, see Publishing Stored Procedure Execution in Transactional Replication.
Spread articles across multiple publications.
If you cannot use the -SubscriptionStreams parameter (described later in this topic), consider creating multiple publications. Spreading articles across these publications allows replication to apply changes in parallel to Subscribers.
Subscription Considerations
Use independent agents rather than shared agents if you have multiple publications on the same Publisher (this is the default for the New Publication Wizard).
Run agents continuously instead of on very frequent schedules.
Setting the agents to run continuously rather than creating frequent schedules (such as every minute) improves replication performance, because the agent does not have to start and stop. When you set the Distribution Agent to run continuously, changes are propagated with low latency to the other servers that are connected in the topology. For more information, see:
SQL Server Management Studio: How to: Specify Synchronization Schedules (SQL Server Management Studio)
Replication Transact-SQL programming: How to: Specify Synchronization Schedules (Replication Transact-SQL Programming)
Distribution Agent and Log Reader Agent Parameters
Use the –MaxCmdsInTran parameter for the Log Reader Agent.
The –MaxCmdsInTran parameter specifies the maximum number of statements grouped into a transaction as the Log Reader writes commands to the distribution database. Using this parameter allows the Log Reader Agent and Distribution Agent to divide large transactions (consisting of many commands) at the Publisher into several smaller transactions when applying commands at the Subscriber. Specifying this parameter can reduce contention at the Distributor and reduce latency between the Publisher and Subscriber. Because the original transaction is applied in smaller units, the Subscriber can access rows of a large logical Publisher transaction prior to the end of the original transaction, breaking strict transactional atomicity. The default is 0, which preserves the transaction boundaries of the Publisher. This parameter does not apply to Oracle Publishers.
Use the –SubscriptionStreams parameter for the Distribution Agent.
The –SubscriptionStreams parameter can greatly improve aggregate replication throughput. It allows multiple connections to a Subscriber to apply batches of changes in parallel, while maintaining many of the transactional characteristics present when using a single thread. If one of the connections fails to execute or commit, all connections will abort the current batch, and the agent will use a single stream to retry the failed batches. Before this retry phase completes, there can be temporary transactional inconsistencies at the Subscriber. After the failed batches are successfully committed, the Subscriber is brought back to a state of transactional consistency.
A value for this agent parameter can be specified using the @subscriptionstreams of sp_addsubscription (Transact-SQL).
Increase the value of the -ReadBatchSize parameter for the Log Reader Agent.
The Log Reader Agent and Distribution Agent support batch sizes for transaction read and commit operations. Batch sizes default to 500 transactions. The Log Reader Agent reads the specific number of transactions from the log, whether or not they are marked for replication. When a large number of transactions is written to a publication database, but only a small subset of those are marked for replication, you should use the -ReadBatchSize parameter to increase the read batch size of the Log Reader Agent. This parameter does not apply to Oracle Publishers.
Increase the value of the -CommitBatchSize parameter for the Distribution Agent.
Committing a set of transactions has a fixed overhead; by committing a larger number of transactions less frequently, the overhead is spread across a larger volume of data. However, the benefit of increasing this parameter drops off as the cost of applying changes is gated by other factors, such as the maximum I/O of the disk that contains the log. Additionally, there is a trade off to be considered: any failure that causes the Distribution Agent to start over must rollback and reapply a larger number of transactions. For unreliable networks, a lower value can result in less failures and a smaller number of transactions to rollback and reapply if a failure occurs.
Decrease the value of the -PollingInterval parameter for the Log Reader Agent.
The -PollingInterval parameter specifies how often the transaction log of a published database is queried for transactions to replicate. The default is 5 seconds. If you decrease this value, the log is polled more frequently, which can result in lower latency for the delivery of transactions from the publication database to the distribution database. However, you should balance the need for lower latency against the increased load on the server from polling more frequently.
Agent parameters can be specified in agent profiles and on the command line. For more information, see: