SQL Server replication distributes large transactions into small transactions

In SQL Server replication, when you perform 1 large transactions in the publication database, such as 100,000 or more data at a time. When the operational data is completed at the publication database, the Log Reader Agent scans the transaction log for a one-time delivery to the distribution database. If the last transaction is not delivered, and multiple transactions are executed consecutively, the Log Reader Agent passes multiple transactions in the scan log to the distribution database, with the default maximum scan of 500 transactions. If you execute multiple million or tens of millions of data, it will clog up for a long time.

The Log Reader Agent can be configured to divide large transactions into small transactions for delivery to the distribution database, and the distribution queue is distributed to the subscription database according to small transactions, so that the data is synchronized quickly!

Before changing the agent parameters, I performed 1 insertions of 300,000 data into the publishing table. After the insert is complete, the monitoring publishes to the distribution record as follows:

As you can see, the commands for these 1 transactions have to be delivered one time before they are distributed, and the distribution consumes time, and this waits too long to affect the real-time nature of the transaction.

If there are other transactions, the default of 500 (reference parameter:-readbatchsize), will also be passed together, taking a longer time.

Now change the parameters, scan to about 1000 of the command on-the-fly distribution, you need to set the following parameters:

-maxcmdsintran Number_of_commands

Note: This parameter can only be added to the Log Reader Agent, and there is no setting for this parameter in the agent configuration file.

Restart the Log Reader Agent after adding.

Insert the 300,000 data again! ~ To monitor View

As you can see, the command is distributed around 1000, when the subscription database is viewed, and the data is synchronized, eliminating the time for more scan commands.

This is a good parameter, but it can also cause data consistency.

Such as:

A batch of data is updated on the publication, but the subscription query is different.

The distribution transaction encounters a conflict or deadlock that also results in inconsistencies in this part of the data.

Reference: Replication Log Reader Agent

SQL Server replication distributes large transactions into small transactions