Enhancing Transactional Replication Performance

You can enhance the performance of transactional replication in your application and on your network by:

• Increasing the Distribution Agent commit batch size.
• Increasing the Log Reader Agent read batch size.
• Minimizing the log history and retention period.
• Optimizing your database design to include replication considerations.
• Using custom stored procedures for inserts, updates, and deletes at Subscribers.
• Avoiding horizontal filtering.

Increase the Distribution Agent Commit Batch Size

The Distribution Agent locks the rows in replicated tables as it updates the Subscriber, and users are blocked from using the rows until the locks are released. The -CommitBatchSize parameter sets the number of transactions to apply before committing. Committing transactions is resource-intensive, and the fewer the commits, the faster replication occurs. Set the -CommitBatchSize parameter so that users at the Subscriber are blocked only for an acceptable amount of time. For more information, see Replication Distribution Agent Utility.

Increase the Log Reader Agent Read Batch Size

The Log Reader Agent and Distribution Agents support batch sizes for transaction read and commit operations. Batch sizes default to 500 transactions. When a large number of transactions are written to a publishing 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. The Log Reader Agent reads the specific number of transactions from the log, whether or nor they are marked for replication. For more information, see Replication Log Reader Agent Utility.

Minimize the Log History and Retention Period

You can reduce the amount of disk space used on the Distributor by minimizing the amount of time for log history and transaction retention. For more information, see Transactional Replication.

Optimize Your Database Design to Include Replication Considerations

Horizontal partitions can inhibit replication performance. Consider database design options that reduce the need to filter rows when defining articles in a publication. Alternatively, consider using custom stored procedures that can delete unnecessary rows at the Subscriber.

Use Custom Stored Procedures for Inserts, Updates, and Deletes at Subscribers

When Microsoft® SQL Server™ applies transactions at a Subscriber, it can override the INSERT, UPDATE, and DELETE statements from the transaction log with custom stored procedures. For example, instead of applying the INSERT statement read from the transaction log, the Distribution Agent can run a stored procedure at the Subscriber to perform the same action. For more information, see Using Custom Stored Procedures in Articles.

Avoid Horizontal Filtering

The criteria set for a horizontal filter is evaluated one time for each row marked for replication in the publication database log. This determines whether the row should be moved to the distribution database. For applications that require maximum data throughput, horizontal filtering of articles may not be the best choice for minimizing the rows delivered to each Subscriber. Instead, developing natural partitions of the table may be a better choice.