17 Performance Tuning Techniques For SQL Server Replication

SQL Server replication performance tuning … A step-by-step guide

More often than not SQL Server replication users don’t realize it has many customizations. A lot of them affect performance.

Performance tune SQL Server replication

Does SQL Server replication affect performance?
Yes, absolutely!
Good news… there are a lot of knobs you can adjust.

Checklist

Change these replication settings to make SQL replication faster, and reduce replication impact on Production SQL Server:

1. Use another server for the housing SQL Server replication distribution DB.

You can also move distribution DB to a replication subscriber. This will offload processing which happens in distribution DB away from the Production node.

2. Use “PULL” subscriptions instead of the “PUSH”.

3. Replicate only articles that must be in replication and only those columns which you really need to replicate.

4. Use separate publications for big tables.

5. Set a “distribution” DB to Simple recovery mode.

6. Enable/create “distribution” DB clean-up job (default: Distribution clean up: distribution).

7. If the Distribution Clean-up job exists, check the retention and reduce it down. Often it contains millions of rows going back way too far.

Realistically if SQL replication gets behind by a few days, it’s over. You have to do a full resync.

8. If the Distribution Clean-up was not set up – better to clean up the archive tables manually in chunks to avoid locking/blocking issues.

9. Check distribution database auto-growth settings. You want to make sure it grows by say 512MB and not 1% which is a default. The goal here is to avoid growth operations, as they slow you down. And avoid excessive VLF problems.

10. Consider disabling “Copy nonclustered indexes”.

11. If you have large transactions in the replication, consider changing -MaxCmdsInTran on Log Reader Agent job.

12. If enabled – disable immediate sync and keep @min_distretention set to 4 to avoid locking/blocking on the cleanup procedure.

13. Create multiple publications or use –SubscriptionStreams parameter for the Distribution Agent – this allows to have the parallel replication.

14. Run subscription agents continuously instead of on very frequent schedules and independent agents rather than shared.

15. You can split articles (or tables).

16. Use the “Read Committed Snapshot Isolation” setting at the subscribers.

17. Check the Microsoft SQL replication tuning article for more ideas.

I am probably forgetting a few…

Just remember, SQL replication has lots of customizations available.

Types of SQL replication agents

SQL Server replication uses agents to manage and monitor replication.

1. Snapshot agent
2. Log Reader agent
3. Distribution agent
4. Each publication has its own agent(s)
5. Push and pull agents

A few SQL replication notes

The word “replication” sounds so good, that it makes sense to think that replication is perfect for disaster recovery.

It is not.

• Do not use SQL replication for the real-time source of data.
• You can’t put the “distribution” database into an Always On availability group. But you can replicate tables inside AlwaysOn DBS.
• By default, nothing in replication is encrypted! But you can enable SSL communication.

Conclusion

Start with the 17 SQL replication parameters above to improve SQL Server replication speed.

Have we missed anything? What improvements did you see when you changed those parameters?