SQL Server Performance Tuning

17 Performance Tuning Techniques For SQL Server Replication

Mark Varnas
3 comments

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.

  1. Use “PULL” subscriptions instead of the “PUSH”.
  1. Replicate only articles that must be in replication and only those columns which you really need to replicate.
  1. Use separate publications for big tables.
  1. Set a “distribution” DB to Simple recovery mode.
  1. Enable/create “distribution” DB clean-up job (default: Distribution clean up: distribution).
  1. 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.

  1. If the Distribution Clean-up was not set up – better to clean up the archive tables manually in chunks to avoid locking/blocking issues.
  1. 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.
  1. Consider disabling “Copy nonclustered indexes”.
  1. If you have large transactions in the replication, consider changing -MaxCmdsInTran on Log Reader Agent job.
  1. If enabled – disable immediate sync and keep @min_distretention set to 4 to avoid locking/blocking on the cleanup procedure.
  1. Create multiple publications or use –SubscriptionStreams parameter for the Distribution Agent – this allows to have the parallel replication.
  1. Run subscription agents continuously instead of on very frequent schedules and independent agents rather than shared.
  1. You can split articles (or tables).
  1. Use the “Read Committed Snapshot Isolation” setting at the subscribers.
  1. 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?

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

3 thoughts on “17 Performance Tuning Techniques For SQL Server Replication”

  1. Great article with a lot of good tips. Just a few things. I’d also recommend creating more than one distribution database if you have large publications with a lot of transactions. Also, you can definitely put distribution databases into an AlwaysOn availability group.

Comments are closed.

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials