RE: CommitBatchSize vs. CommitBatchThreshold for transactional dis

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Paul, think your problem is that you are thinking of it like a spill bucket.
The spill bucket can only hold so much before dumping.

What happens here is like a bar maid at the pubs you frequent to all hours
and probably are at while you are reading this message.

Let's suppose the bar maid on duty tonight is the high IQ type. She can
memorize 50 orders (commitbatchThreshold), but her tray is only so large, so
she can carry 5 beers (commit batch size).

If you give her two orders, is she going to wait around for 3 more orders?
Of course not (unless she is chatting u up). She will go and grab the beers
no matter that her brain can hold 50 orders and her tray can hold 5 beers.

Same thing is going on here. You issue 2 transactions with a varied amount
of commands. Is the distribution agent going to wait 7 years for the next 3
transactions to trip a commit? No, its going to trip when it hits the polling
interval or some other internal mechanism.

It only when its things get busy that the bar maid start delivering orders
with a full bar tray, and then there is no time to chat for her to chat you
up anymore, until you mention your ailment and immediately clear the bar.

You can only observe this action when pimping, oops typo there, pumping
large numbers of transactions and commands to the distribution agent.

What is puzzling however is the split in test 3, however.

"Paul Ibison" wrote:

Actually you've put what I was trying to express much more clearly than me
"What bol does not make clear is how it handles a transaction and its
constituent commands". It seems to me that the commands referred to in
CommitBatchThreshold only refers to individual commands outside of a grouped
transaction, also that both definitions refer to a "Maximum" before the
COMMIT is issued, ie 2 changes need making to the BOL definitions.

This makes producing the rules in the original post "CommitBatchThreshold >
CommitBatchSize" etc quite tricky.

On the point of the poster's strange issue with 7 singleton updates becoming
2 transactions: one with 6 commands and one with one command, I have no ideas
- I've just tested on SQL Server 2000 with Log Explorer and it is totally
reproducible there also! Have requested clarification for this logic from our
friends at MS.

Cheers,

Paul Ibison
.

Relevant Pages

  • RE: CommitBatchSize vs. CommitBatchThreshold for transactional dis
    ... transactions, one of 5 rows and one of 6 rows. ... trigger a commit as its two transactions, but commitbatchthreshold would as ... " I issued the same 7 update statements as in test 1. ... ID) followed by a second batch with 1 update statement (different ...
    (microsoft.public.sqlserver.replication)
  • Re: Transactional Replication is reapplying commands
    ... from the log, written to the distribution database, and then just when the ... transactions in the log, write them to the distribution database, and mark ... cascading updates and deletes for replication enabled. ... > executing a batch of commands. ...
    (microsoft.public.sqlserver.replication)
  • Re: 1 transaction with 800000 commands were delivered
    ... to your developer about breaking these commands up into multiple transactions. ... Depending on what you are actually doing, this could cause replication to ...
    (microsoft.public.sqlserver.replication)
  • Re: Thread safety and performance
    ... > My opinion is that one session per thread is the way to go. ... If you design it with speed in mind, executing commands ... So you feel that prepared commands are more efficient than rowsets. ... figure out how to reuse prepared commands under transactions. ...
    (microsoft.public.data.oledb)
  • RE: CommitBatchSize vs. CommitBatchThreshold for transactional dis
    ... "What bol does not make clear is how it handles a transaction and its ... constituent commands". ... CommitBatchThreshold only refers to individual commands outside of a grouped ... ie 2 changes need making to the BOL definitions. ...
    (microsoft.public.sqlserver.replication)