find the current large batch run in transaction replication

Question

hi folks, i want to find out what is the current large batch thats been run in a transaction replication article. Sometimes i see there are lot of pending commands left to be run in our transaction replication, hence i want to find out what is causing this slowness and if i want to see any large DML operation has been run i came up with following query. Does it sound correct?

select rc.article_id, rc.xact_seqno, source_owner, source_object, rt.entry_time , count(*)
from MSrepl_commands rc join MSrepl_transactions rt
on rc.xact_seqno = rt.xact_seqno join msarticles ar
on ar.article_id =rc.article_id
where ar.article_id =288 --filter on particular article
group by rc.article_id, rc.xact_seqno, source_owner, source_object, rt.entry_time
order by entry_time desc;

i get output as, last column represent number of rows.

Answer 1

Hi @SQLServerBro,

> Does it sound correct?

You can using this query to find numbers of transactions in this article and numbers of commands for every transaction in this replication article. But according to your screenshot, it seems that there is no batch operation in this replication article.

If you have batch updates that occasionally affect a large number of rows at the Subscriber, you should consider updating the published table using a stored procedure and publish the execution of the stored procedure. When the procedure is executed at the Publisher, replication executes the corresponding procedure at the Subscriber. This can provide significantly better performance for cases where large batch operations are performed, because only the procedure execution is replicated, bypassing the need to replicate the individual changes for each row. Refer to MS document Enhance Transactional Replication Performance and Publishing Stored Procedure Execution in Transactional Replication to get more.

In addition, suggest you reading below blog, it shares us the information about replication performance issues about batch operation in replication. And share us detail examples.

SQL Server Transactional Replication Issues

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

thanks @CathyJi-MSFT does my query sounds correct though? basically using this query i want to find out what is the count of rows each batch is submitting and i think i can get that from the last column of count(*).

also second question is if query is correct, and if there is large update /Delete /insert in progress it should appear in msrepl_commands table correct, this table wont get cleared up until batch is completely processesed to the subscriber.

Answer 3

Transactional Replication transfers COMMANDS run, not individual data changes.

To see the commands pending use:
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-browsereplcmds-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

Answer 4

@Tom Phillips i dont think thats correct. In a default replication settings, if an update statement is run on publisher that updates 10 rows, subscriber will get 10 individual update statements.

Moreover , my original question is to see if my query is correct. basically using this query i want to find out what is the count of rows each batch is submitting and i think i can get that from the last column of count(*).

also second question is if query is correct, and if there is large update /Delete /insert in progress it should appear in msrepl_commands table correct, this table wont get cleared up until batch is completely processesed to the subscriber.

Answer 5

Hi @SQLServerBro,

> does my query sounds correct though? basically using this query i want to find out what is the count of rows each batch is submitting and i think i can get that from the last column of count(*).

I think that you are right.

> also second question is if query is correct, and if there is large update /Delete /insert in progress it should appear in msrepl_commands table correct, this table wont get cleared up until batch is completely processesed to the subscriber.

Yes. But if you have large update /Delete /insert in a table, suggest you updating the published table using a stored procedure. I mentioned this in my first reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".