October 22, 2008 at 10:34 am
Just trying to make sure I have an exact understanding of the difference between these two agent profile values and how they affect one another...
CommitBatchSize controls the max number of transactions in a batch; default is 100
CommitBatchThreshold controls the max number of commands in a batch; default is 1000
If I have an update at the publisher which affects 950 rows and another which affects 200 rows, I'm going to see two batches issued to the subscriber because more than 1000 individual update commands would be issued if the result of both update statements were included in the same batch (since replication works against a single row at a time at the subscriber). Batch 1 will have 1 transaction with 950 commands and batch 2 will have 1 transaction with 200 commands.
On the other hand, if I had that same update which affected 950 rows followed by 40 insert statements, both the update and the inserts would all be included in the same batch sent to the subscriber because there are less than 100 transactions and the total number of rows affected is less than 1,000. So batch 1 will have 41 transactions with 990 commands in this case.
Is all of this correct so far, or am I way off base here?
Lastly, suppose I have a single update statement at the publisher which affects 1,500 rows. I'm expecting to see two batches issued with 1 transaction each - batch 1 with 1 transaction and 1,000 commands and batch 2 with 1 transaction and 500 commands, right?
Thanks,
November 20, 2008 at 5:17 pm
Never received a response to this...so I figured it out for myself. I wrote a blog post about it for anyone who is interested in understanding what these two values do for replication:
http://kendalvandyke.blogspot.com/2008/11/how-commitbatchsize-and.html
November 21, 2008 at 9:29 am
thx
i'm on a project right now to improve our replication. we have jobs that generate up to 70 million commands in a few hours. publisher is ok. we're having trouble replicating this in a timely manner
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply