How to Improve SQL Server Replication Performance
June 07, 2013
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.
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
Geeks who read this article also read…
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- How to Restore an Analysis Services Database Using SQL Server Management Studio
- Automate Backup of Analysis Services Database Using SQL Server Agent Job
- How to Attach Analysis Services Database in SQL Server
- How to Detach Analysis Services Database in SQL Server
- Performance Dashboard Reports in SQL Server 2012
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- How to Attach Database Without a Transaction Log File in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…