The System Has a Performance Issue
Replication performance can be measured along the following five dimensions:
Latency: the amount of time it takes for a data change to be propagated between nodes in a replication topology.
Throughput: the amount of replication activity (measured in commands delivered over a period of time) a system can sustain over time.
Concurrency: the number of replication processes that can operate on a system simultaneously.
Duration of synchronization: how long it takes a given synchronization to complete.
Resource consumption: hardware and network resources used as a result of replication processing.
Latency and throughput are most relevant to transactional replication, because systems built on transactional replication generally require low latency and high throughput. Concurrency and duration of synchronization are most relevant to merge replication, because systems built on merge replication often have a large number of Subscribers, and a Publisher can have a significant number of concurrent synchronizations with these Subscribers.
After replication is configured, we recommend developing a performance baseline, which allows you to determine how replication behaves with a workload that is typical for your applications and topology. Use Replication Monitor and System Monitor to determine typical numbers for the five dimensions of replication performance listed above. After you have established baseline numbers, set thresholds and alerts in Replication Monitor. For more information, see Monitoring Performance with Replication Monitor, Setting Thresholds and Warnings in Replication Monitor, and Using Alerts for Replication Agent Events. For more information about tools that can be used to troubleshoot replication, see Troubleshooting Tools (Replication).
Explanation and User Action
Replication performance is affected by the following factors:
Server and network hardware
Database design
Distributor configuration
Publication design and options
Filter design and use
Subscription options
Snapshot options
Agent parameters
Maintenance
If you encounter a performance problem, we recommend reading through the suggestions in the following sections and to apply changes in areas that affect the issues you are encountering. For example:
If you are using merge replication and you notice in Replication Monitor that a single filtered article is accounting for a large percentage of the synchronization time: verify that you are using the appropriate filtering options and that columns in the filter are indexed.
If you are using transactional replication and experience high latency when performing batch operations on published tables: consider replicating the execution of a stored procedure to perform the batch operation at the Subscriber.
All Types of Replication
The following areas should be considered for all types of replication. For more information, see Enhancing General Replication Performance.
Server and network
Set the minimum and maximum amount of memory allocated to Microsoft SQL Server Database Engine.
Ensure proper allocation of database data files and log files. Use a separate disk drive for the transaction log for all databases involved in replication.
Consider adding memory to servers used in replication, particularly the Distributor.
Use multiprocessor computers.
Use a fast network. If the network is slow, specify appropriate network settings and agent parameters. For more information, see A Slow Network Is Causing Problems.
Database design
Follow best practices for database design.
Consider setting the READ_COMMITTED_SNAPSHOT database option.
Be cautious with application logic in triggers.
Limit the use of Large Object (LOB) data types.
Publication design and options
Publish only the data required.
Minimize conflicts through publication design and application behavior.
Use row filters judiciously.
Reduce the verbose levels of replication agents except during initial testing, monitoring, or debugging.
Subscription options
Use pull subscriptions when there are a large number of Subscribers.
Consider reinitialization of the subscription if Subscribers are too far behind.
Snapshot options
Run the Snapshot Agent only when necessary and at off-peak times.
Use a single snapshot folder for a publication.
Place the snapshot folder on a drive local to the Distributor that is not used to store database or log files.
When you create the subscription database at the Subscriber, consider specifying a recovery model of simple or bulk-logged.
Use the alternate snapshot folder and compressed snapshots on removable media for low-bandwidth networks.
Use the –MaxBCPThreads parameter of the Snapshot Agent, Merge Agent, and Distribution Agent. Use the –UseInprocLoader parameter of the Distribution Agent and the Merge Agent.
Transactional Replication
The following areas should be considered for transactional replication. For more information, see Enhancing Transactional Replication Performance.
Database design
- Minimize transaction size in your application design.
Distributor configuration
Configure the Distributor on a dedicated server.
Set an appropriate size for the distribution database.
Publication design and options
Replicate stored procedure execution when making batch updates to published tables.
Spread articles across multiple publications.
Subscription options
Use independent agents rather than shared agents if you have multiple publications on the same Publisher (this is the default behavior).
Run agents continuously instead of on very frequent schedules.
Agent parameters
Use the –MaxCmdsInTran parameter for the Log Reader Agent.
Use the –SubcriptionStreams parameter for the Distribution Agent.
Increase the value of the -ReadBatchSize parameter for the Log Reader Agent.
Increase the value of the -CommitBatchSize parameter for the Distribution Agent.
Decrease the value of the -PollingInterval parameter for the Log Reader Agent.
Merge Replication
The following areas should be considered for merge replication. For more information, see Enhancing Merge Replication Performance.
Database design
Index columns used in row filters and join filters.
Consider over-normalizing tables that include Large Object (LOB) data types.
Publication design
Use a publication compatibility level of 90RTM (SQL Server 2005) or a later version.
Use appropriate publication retention settings.
Use download-only articles on those tables that are only changed at the Publisher.
Filter design and use
Limit the complexity of row filter clauses.
Use precomputed partitions with parameterized filters (this feature is used by default).
Use nonoverlapping partitions if data is filtered but not shared among users.
Do not create complex join filter hierarchies.
Set the join_unique_key option to 1 if logic allows.
Precomputed partition considerations
When batches contain lots of data changes, design the application carefully. Changes to data in the parent table in a join filter should be made before corresponding changes in the child tables.
When batches contain lots of data changes, reduce the number of changes in a batch and run the Merge Agent between batches. If this cannot be done, increase the value of generation_leveling_threshold for the publication.
Subscription considerations
- Stagger subscription synchronization schedules.
Agent parameters
- If a subscription is synchronized over a fast connection and changes are sent from the Publisher and Subscriber, use the –ParallelUploadDownload parameter for the Merge Agent.
Snapshot options
Create a ROWGUIDCOL column on large tables prior to generating the initial snapshot.
Pre-generate snapshots and/or allow Subscribers to request snapshot generation and application the first time they synchronize.
Maintenance
Occasionally re-index merge replication system tables.
Monitor synchronization performance using the Synchronization History tab in Replication Monitor.