Commitbatchsize and commitbatchthreshold attributes in SQL Server transactional Replication

Source: Internet
Author: User
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:

    1. the distrabution agent uses all the single commands as a unit. The commitbatchthreshold and commitbatchsize are based on the number of single commands.
    2. if a statement updates N records, this statement is considered to be in a transaction. However, the number of commands is N.
    3. If the command is in a transaction, commitbatchsize is not considered, only commitbatchthreshold is considered.
    4. a single transaction is not split into multiple ones, even if the number of commands in a single transaction exceeds commitbatchthreshold.
    5. 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.
    6. If the command is not in a transaction, merge a certain number of commands into a transaction according to the commitbatchsize setting.
    7. 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.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.