Home Dashboard Directory Help
Search

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


Status: 

Active


2
0
Sign in
to vote
Type: Bug
ID: 478792
Opened: 7/30/2009 8:26:55 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
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
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 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.
Sign in to post a workaround.