Performance Tuning SQL Server Replication – 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.
Does SQL Server replication affect performance?
Yes, absolutely!
Good news… there are a lot of knobs you can adjust.
Performance tune SQL Server Replication – Checklist
Change these replication settings to make SQL replication faster, and reduce replication impact on Production SQL Server:
- 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.
- Use “PULL” subscriptions instead of the “PUSH”.
- Replicate only articles that must be in replication and only those columns which you really need to replicate.
- Use separate publications for big tables.
- Set a “distribution” db to “Simple” recovery mode.
- Enable/create “distribution” DB clean-up job (default: Distribution clean up: distribution).
- 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 resynch.
- If the Distribution Clean-up was not set up – better to clean up the archive tables manually in chunks to avoid locking/blocking issues.
- 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.
- Consider disabling “Copy nonclustered indexes”.
- If you have large transactions in the replication, consider changing -MaxCmdsInTran on Log Reader Agent job.
- If enabled – disable immediate sync and keep @min_distretention set to 4 to avoid locking/blocking on the cleanup procedure.
- Create multiple publications or use –SubscriptionStreams parameter for the Distribution Agent – this allows to have the parallel replication.
- Run subscription agents continuously instead of on very frequent schedules and independent agents rather than shared.
- You can split articles (or tables).
- Use the “Read Committed Snapshot Isolation” setting at the subscribers.
- Check the Microsoft SQL replication tuning article for more ideas.
I am probably forgetting a few.
Just remember, that SQL replication has lots of customizations available.
SQL Replication Agents
- SQL Server Replication uses agents to manage and monitor replication.
- Snapshot Agent
- Log Reader Agent
- Distribution Agent
- Each publication has its own agent(s)
- Push and Pull Agents
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 AlwaysOn availability group. But you can replicate tables inside AlwaysOn DBS.
- Be default, nothing in replication is encrypted! But you can enable SSL communication.
Conclusion: Start with 15 SQL replication parameters above to improve SQL Server replication speed.
Have we missed any? What improvements did you see when you changed those parameters?
