Recently, I encountered a problem about the setting of commitbatchsize and commitbatchthreshold in SQL Server transactional replication. The explanation in the BOL is vague:
- Commitbatchsize "is the number of transactions to be issued to the subscriber before a commit statement is issued. The default is 100 ."
- Commitbatchthreshold "is the number of replication commands to be issued to the subscriber before a commit statement is issued. The default is 1000 ."
A foreigner made a detailed test result to summarize the functions of these two attributes. For details, see:
Http://kendalvandyke.blogspot.com/2008/11/how-commitbatchsize-and.html
Based on my own analysis, these two attributes serve the following purposes:
- the distrabution agent uses all the single commands as a unit. The commitbatchthreshold and commitbatchsize are based on the number of single commands.
- if a statement updates N records, this statement is considered to be in a transaction. However, the number of commands is N.
- If the command is in a transaction, commitbatchsize is not considered, only commitbatchthreshold is considered.
- a single transaction is not split into multiple ones, even if the number of commands in a single transaction exceeds commitbatchthreshold.
- if multiple transactions are submitted, the number of commands of the first transaction does not exceed commitbatchthreshold, and the second transaction is merged as the first one. If the number of merged commands exceeds commitbatchthreshold, the merge is not continued, but the first two transactions are still submitted.
- If the command is not in a transaction, merge a certain number of commands into a transaction according to the commitbatchsize setting.
- If the value of commitbatchsize is greater than commitbatchthreshold, the transaction of a commitbatchsize will be further split into two groups, for example, commitbatchsize = 5, commitbatchthreshold = 3, and the total number is 10, 4, 1. For more information about why 4 exists, see 5th, because the number of commands in the first package exceeded commitbatchthreshold in backward merge. Therefore, a commitbatchsize transaction is divided into two groups: 4 and 1.