Using the ReadBatchThreshold parameter in Logreader agent leads to slow performance

Using the ReadBatchThreshold parameter in Logreader agent leads to slow performance by Greg Schreiner

Active

Type:	Bug
ID:	478792
Opened:	7/30/2009 8:26:55 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

It appears the overall throughput of a SQL Server Replication logreader agent is significantly slower if specifying a value for the ReadBatchThreshold parameter on the command line.

Details (expand)

English

SQL Server 2005 - Standard Edition

Replication

Windows Server 2003 (all x64 editions)

English

Create a table like as follows:

CREATE TABLE [dbo].[testing](
    [ID] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [Data] [char](5) NOT NULL,
)
INSERT INTO dbo.Testing VALUES (1001, '0.000')

Publish the table for transactional replication, add a subscription somewhere, and push the snapshot. Using the default options via Mananagement Studio is fine.

Perform updates on the table:

DECLARE @I INT
SET @I = 100000
while @I > 0 BEGIN
    UPDATE dbo.Testing SET Data = ROUND(RAND(), 3) WHERE ID = 1001
    SET @I = @I - 1
END

Note the actual time the logreader agent takes to read the commands. Then go into the logreader agent job properties and add " -ReadBatchThreshold 1000000" to the command line, and repeat the test.

With the command line parameter specified, while the reported delevery rate reported via Replication Monitor is similar, the actual elapsed time it takes to read all the commands is significantly slower. The effective rate is about 100-300 tranactions per second with, versus several thousand transactions/sec without on a multi-core machine.

With the parameter, a trace appears to show the logreader agent loads 1-3 500 transaction batches to the distributor and then waits for the next pooling interval despite transactions on the publisher waiting to be read.

I've also confirmed this behavior on SQL Developer edition builds 9.00.4211.00 and 10.0.2714.0.

Performance with and without the option is roughly similar given a series of very small transactions.

X64

0 attachments

Posted by Microsoft on 2/15/2011 at 1:42 PM

Hi Greg,

thanks for taking the time to share your feedback, this is really important to us.
We are indeed trying to address this issue in the next major release of SQL Server.

Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)
SQL Server Engine Team

Posted by Microsoft on 2/15/2011 at 1:42 PM

Posted by Greg Schreiner on 9/21/2009 at 3:47 PM

Thanks Qun Guo! Yes, I was thinking that when comparing the returned # of trans and commands to max_tran and the ReadBatchThreshold that if either or both limits were exceeded, it wouldn't trigger a pollinginterval wait. In this case, it appeared it still waited despite hitting the max_tran threshold. I also considered the pollinginterval workaround--it just didn't meet our needs during some high volume periods.

I was aiming to use ReadBatchThreshold to reduce the batch size to the distributor when encountering a series of larger (20,000 command) transactions. It worked well untill we found this performance issue.

I'll presume that the next version of SQL server you're referring to is the version after SQL 2008 R2.

Thanks!
Greg

Posted by Microsoft on 9/21/2009 at 12:13 PM

Hello,

Thanks for reporting this issue, this behavior was actually by design, we are comparing the returned #of transactions and commands against max_tran and the threshold u specified when smaller we intentionally hold off the scan for the period of pollinginterval so to achieve bigger batch with more commands, this optimization was done several versions ago and I tend to agree it is more confusing than useful, therefore i will change this in next version of SQL server so we will no longer wait in the case you described here. For current versions you can workaround this behavior to some degree by reducing pollinginterval from the default 5 second to 1.

Again, thanks for contact us regarding this issue.
Qun Guo
SQL Server dev team

Posted by Microsoft on 8/3/2009 at 10:30 AM

Thanks for reporting this. We will take a look at this and get back to you.