Enhancing Transactional Replication Performance

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

• 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 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 not 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 or using custom data partitions with transformable subscriptions (for more information, see Using Transformable Subscriptions to Create Custom Data Partitions).

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

When Microsoft® SQL Server™ 2000 applies transactions at a Subscriber, by default it overrides 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. These stored procedures can be further customized, which is generally better than adding Subscriber-specific logic in triggers (for actions such as maintaining aggregate tables).

Avoid Horizontal Filtering

The criteria set for a horizontal filter are 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 using custom data partitions with transformable subscriptions (for more information, see Using Transformable Subscriptions to Create Custom Data Partitions).