Enhancing Snapshot and Transactional Replication Performance

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

• Configuring the Distributor on a dedicated server.
  
  
• Increasing memory on the Distributor.
  
  
• Subscribing to all articles in a publication.
  
  
• Using stored procedure replication when a large number of rows are affected.
  
  
• Minimizing the retention period for transactions and history.

Configure the Distributor on a Dedicated Server

You can reduce processing overhead on the publishing server by configuring a computer dedicated to the distribution process. This may result in performance gains for both the Publisher and the Distributor.

Increase Memory on the Distributor

In addition to the benefits of maintaining a dedicated Distributor, you can realize additional performance gains by increasing the amount of memory on the Distributor. This is especially true if the Distributor is supporting replication to a large number of Subscribers. For example, if the computer is configured with 64 megabytes (MB) of memory, consider increasing the memory to 128 MB or more. You can use the sp_configure stored procedure to assign additional memory to Microsoft® SQL Server™ 2000. 

Subscribe to All Articles in a Publication

By default, a subscription includes all the articles in a publication. By not having to exclude any articles from a publication, the Distribution Agent can use an optimal query during synchronization.

Use Stored Procedure Replication When a Large Number of Rows are Affected

If a single set update/delete at the Publisher affects a very large number of rows, the change to each row affected by the update is logged individually in the transaction log of the database. The log reader will propagate these as individual updates (within a single transaction) and when the Distribution Agent applies the changes it can take much longer than the original update at the Publisher.

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 on a sending an update/delete for every row affected by the update/delete, the Distribution Agent will execute the same procedure at the subscriber with the same parameter values. This is faster by a large magnitude compared to sending the update/delete as individual row changes. For more information see Publishing Stored Procedure Execution.

Use Custom Stored Procedures to Update Subscribers

By default when a Subscriber is set up for transactional replication, the process of applying a snapshot to a Subscriber, in addition to creating the table(s) and populating them, will also create a set of stored procedures at the Subscriber (for INSERT, UPDATE and DELETE).

Subsequently when changes are made to a published table, the log reader will construct a stored procedure call instead of SQL statements representing the change. The distribution agent then executes this while applying changes to a Subscriber. This is much more efficient than SQL statements over which it provides significant performance gains.

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).

 For more information, see Using Custom Stored Procedures in Articles.

Minimize the Retention Period for Transactions and History

You can reduce the amount of disk space used on the Distributor by minimizing the amount of time that replicated transactions and history are stored in the distribution database after they have been delivered to Subscribers. 

Reduce Unnecessary Reinitialization or Expiration of Subscriptions

If a Subscriber does not synchronize for a long time  there is a possibility the subscription may get dropped or it may be automatically marked deactivated and require reinitialization. Whether it expires and is dropped or gets marked deactivated and requires initialization depends upon whether it exceeds the Subscription Expiration property of the publication or the Maximum Transaction Retention property of the distribution database as well as whether or not it is an Anonymous subscriber.

If you do not want your subscriptions to expire, you should set the publication retention to "0". If you do not want your subscriptions to be deactivated you should set the Maximum Distribution Retention period to a higher value than the default of 72 hours taking into consideration the effect it may have on the size of the distribution database. For more information, see Subscription Deactivation and Expiration.

Use a Quality Disk Subsystem

Because snapshot replication copies a complete copy of data in the publication, it writes data for the entire publication to the snapshot folder. The faster the disk subsystem can read and write data to the disk(s), the faster the snapshot is completed.