How to Improve SQL Server Replication Performance


June 07, 2013

Introduction

In this article we will discuss some of the optimization techniques which a database administrator can follow to improve the performance of SQL Server Replication.

Problem

Currently we have SQL Server 2008 acting as a publisher and the distributor is also configured to use the same instance. We have configured Push Transactional replication which is configured to deliver a huge database. Currently Distribution agent is configured to run once in every 6 hours and it keeps running for almost 2 hours before it completes the data delivery.

Some of the ways in which DBA can optimize SQL Server Replication are mentioned below: -

1. Log Reader and Distribution Agents should be set to run continuously instead of running them on frequent schedules.

2. Set the distribution database size to be sufficient enough to handle the transaction volume thereby avoiding frequent growth. For more information on this read the following article How to Change SQL Server Database Auto Growth Settings

3. It is always better to configuring the Distributor Server to be a dedicated server rather than running Publisher and Distribution on the server.

4. Verify are you running a 64 bit or 32 bit version of SQL Server. If you are running 32 bit then consider move to 64 bit server to realize better performance.

5. Consider increasing memory on the Distributor Server.

6. It is also a good idea to minimizing the retention period for transactions and history as this will lowering database space.

7. Consider increasing the read batch size for the Log Reader Agent

8. Minimizing the log history and retention period for the data in the distribution database.

9. Avoiding horizontal filtering of data as it takes longer time.

10. Increase the BCP Batch Size parameter and reduce the commit batch value to improve performance


Share this Article


Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."