PRB: 6.5 SP4 May Affect Replication Performance

• Microsoft SQL Server, version 6.5 Service Pack 4
• Microsoft SQL Server version 6.0

SYMPTOMS

After applying Service Pack 4 for SQL Server 6.5, the logreader task may take longer to process transactions marked for replication out of the published database, if there are multiple replicated transactions bound by a long-running transaction. Specifically, the sp_repldone stored procedure may take longer to determine the next non-distributed transaction when long-running transactions are encountered in the transaction log of the published database.

WORKAROUND

If you suspect that Service Pack 4 for SQL Server 6.5 has caused replication performance problems, there are a few options that you can tune to alleviate the problem. If you do not use Windows NT Performance Monitor to actually monitor replication objects, you can use the logreader option "-p0" to turn off Performance Monitor counter updates.

As for increasing the sp_repldone performance, the recommended configuration is to avoid concurrent batch processing with transaction processing. To improve overall logreader performance, change the transactionbatchsize parameter (the "-b" option of the logreader task) to have relatively high batching and consequently, reduced number of calls to sp_repldone. The default value for the "-b" option is 100; depending on the number of transactions, you can use a high batch value like 1000.

MORE INFORMATION

Sp_repldone is the stored procedure that marks a transaction as "replicated" and scans the transaction log for any further transactions marked for replication. This procedure begins with the oldest distributed transaction and scans the transaction log for "commit" records that are marked for replication. In the process, sp_repldone must ensure that the oldest non-distributed transaction information is maintained properly, to ensure transactional integrity and ordering, during replication.

The logic used to determine the oldest non-distributed transaction was modified to avoid incorrect truncation of the log. This was done to avoid situations where logreader could fail due to partial truncation of the oldest non-distributed transaction from the log. Consequently, the sp_repldone procedure must scan the transaction log until the end of the oldest open transaction at the time of this "commit" or the end of the log, if there are long-running transactions that started before multiple smaller transactions marked for replication.

This change to the sp_repldone logic is required to maintain transactional integrity with replication. The performance implication of this change does not apply to scenarios with implicit transactions or short, OLTP-type transactions. It only applies to specific, limited cases where there are long-running transactions (batch processing) that run concurrently with the short, OLTP-type replicated transactions. Another situation where this effect is pronounced is where the logreader batch size is small, thereby causing more calls to the sp_repldone procedure.

Additional query words: repl perf down sp sp4 regression degradation slow
down perfmon tran trans

Keywords : SSrvRep
Version : WINNT:6.5
Platform : winnt
Issue type : kbprb