In transactional replication, the transaction log of the database involved in replication is both written to and read from. Without replication, a transaction log is almost always written to, and rarely read from. Because a transaction log is both written to and read from when using transactional replication, this can cause I/O performance issues on databases that experience large numbers of transactions.
To help reduce this problem, locate the transaction log of databases involved in transactional replication on its own dedicated RAID 1 or RAID 10 disk array, and connected to its own SCSI or fiber channel. [6.0, 7.0, 2000, 2005] Updated 1-6-2006
*****
If you are using transactional replication, you may want to monitor the latency that it takes the Log Reader to move transactions from a database’s transaction log until it puts it in the distribution database, and to also monitor the latency it takes the Distributor Agent to move transactions from the distribution database to the Subscriber database. The total of these two figures is the amount of time it takes a transaction to get from the publication database to the subscriber database.
The counters for these two processes are the SQL Server Replication LogReader: Delivery Latency counter and the SQL Server Replication Dist.: Delivery Latency counter.
If you see a significant increase in the latency for either of these processes, this should be a signal to you to find out what new or different action has happened to cause the increased latency. [6.5, 7.0, 2000, 2005] Updated 1-6-2006
*****
Transactional replication offers an option called Immediate-Updating Subscribers. This feature provides for transactional consistency for all of the various Subscribers of a publisher. By making use of the Microsoft Distributed Transaction Coordinator (MSDTC), Immediate-Updating Subscribers allows subscribers to update a copy of the local data, and the Publishers data is also updated, simultaneously. This change is then replicated from the Publisher to the other Subscribers, so they all have consistent data.
While the option is effective, it is also a resource hog. Because of this, you should only use Immediate-Updating when it is absolutely necessary. As an alternative to Immediate-Updating, consider only replicating changes from a Subscriber to a Publisher at regular intervals, such as once an hour, or once a day. This will significantly reduce server overhead. [7.0, 2000, 2005] Updated 1-6-2006
*****
Although horizontal filtering can reduce the amount of data that is replicated from one database to another, using it also incurs high overhead when used with transactional replication. The log reader agent is used to perform horizontal filtering, and every row that is updated on the Publisher causes extra work for the log reader.
One way around this overhead is to employ DTS custom partitions (available in SQL Server 2000) to perform the horizontal partitioning for you. This reduces the log reader’s overhead, boosting performance. [2000] Updated 1-6-2006
*****
Transactional replication performance can be boosted by changing the ReadBatchSize parameter of the log reader agent in cases where a large number of transactions are being written to a published database, but only a small portion of them will be replicated to subscribers. The default value is 500. You will probably have to experiment with different values until you find the optimal one for your particular configuration.
To set this option, run this command:
logread -ReadBatchSize number_of_transactions
[7.0, 2000] Updated 1-6-2006
*****
Transactional replication performance can sometimes be boosted by changing the PollingInterval parameter of the Log Reader Agent. By default, the Log Reader Agent polls the published database’s transaction log every 10 seconds. If the transaction log of the published database is very busy, then performance may be boosted if the PollingInterval is increased.
The reason performance is boosted is because normally the I/O activity on transactions logs is limited to sequential writes. When the Log Reader Agent polls the transaction log, it has to read the log, which in effect causes random reads to occur on the device with the transaction log, which prevents writes from being purely sequential in nature. If the device has to perform both reads and writes, as is the case in this instance, then sequential writes, in effect, turn into random writes, which hurts performance.
By increasing the PollingInterval, reads occur less often, which reduce the interference with sequential writes, which helps to boost performance. For this performance bonus to work, the drive that has the transaction log must be devoted to transaction logs. If it is not, then that drive most likely will already be experiencing random writes, and increasing the PollingInterval won’t help performance.
If you decide to increase the PollingInterval, you will have to experiment with different values to come up with the optimum value for your particular situation. If you do this, be sure you have a good way to determine performance before and after your experimentation to ensure that you get good results. [7.0, 2000] Added 12-26-2001
The distributor has a setting called the CommitBatchSize, which is configured from the “Replication Agent Profile Details” dialog box. This parameter determines how many replication transactions are committed in the distribution database in a single batch. The default value is 100.
This setting can affect performance two different ways. First, the larger the CommitBatchSize parameter is, the fewer commits that have to be made to the distribution database, which results in reduced overhead. This is because this process is resource intensive, and the fewer times that a commit occurs, the less overhead that is incurred.
While increasing the CommitBatchSize sounds like a good idea, there is also a corresponding downside to increasing it. The problem occurs because larger batch sizes mean that the actual commit takes longer to occur, which in turn causes locks in the distribution database to be held longer than if the commits were to take less time. Locks, as you know, can reduce concurrency in a database, reducing performance.
So this issue is, what is more important, faster replication or less locks? The ideal size of the CommitBatchSize parameter depends on your specific situation. For example, if there is a large amount of activity in the distribution database coming from both the publisher and many subscribers, then reducing the batch size can be beneficial because of reduced locking (improved concurrency) in the distribution database. By increasing concurrency, transactions that have to wait on locks don’t have to wait as long, and overall performance if boosted. For example, if there are 100 subscribers, each subscriber needs to be updated from the Distribution database. This can create a lot of activity in the distribution database, which be slowed down if there are many locks occurring.
On the other hand, if most of the activity in the Distribution database is caused by the Log Reader Agent that is running often (or even continuously), and not other sources (such as subscribers), then increasing the size of the CommitBatchSize parameter makes more sense, as larger batch sizes mean that the Log Reader Agent doesn’t have to work as often, which in turn reduces overhead, boosting performance.
So what should you do? Unless you are aware of specific issues with transactional replication performance, you should leave the CommitBatchSize option alone. If you are experiencing performance problems, you may want to experiment with different CommitBatchSize settings until you can find one that helps boost performance. If you do this, be sure you have a good way to determine performance before and after your experimentation to ensure that you get good results. [7.0, 2000] Added 12-26-2001
