sql transaction replication cleanup job

Question

hi Folks i see in my sp_who2 output, following distribution table cleanup query runs for a long time sometimes 10-15 mins. Is there any way to speed itup.

msrepl_command has around 27 mil. rows. msrepl_trans has around 8 mill rows.

DELETE TOP(@deletebatchsize_commands) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands with (INDEX(ucMSrepl_commands)) where
publisher_database_id = @publisher_database_id and
xact_seqno <= @Max _xact_seqno and
-- do not delete directory, alt directory or script exec commands. they are deleted
-- above. We have to do this because we use a (nolock) hint and we have to make sure we
-- don't delete dir commands when the file has not been cleaned up in the code above. It's
-- ok to delete snap commands that are out of retention and perform lazy delete of dir
(type & ~@snapshot_bit) not in (@directory_type, @alt _directory_type) and
(type & ~@replpost_bit) <> @scriptexec_type and
(
-- Select the row if it is older than max retention.
xact_seqno <= @Max _immediate_sync_seqno or
-- Select the snap cmd if it is not for immediate_sync article
-- We know the command is for immediate_sync publication if
-- the snapshot tran include articles that has virtual
-- subscritptions. (use subscritpion table to avoid join with
-- article and publication table). We skip sync tokens because
-- they are never pointed to by subscriptions...
(
(type & @snapshot_bit) <> 0 and
(type & ~@snapshot_bit) not in (@syncinit, @syncdone) and
not exists (select * from MSsubscriptions s with (nolock) where
s.publisher_database_id = @publisher_database_id and
s.article_id = MSrepl_commands.article_id and
s.subscriber_id < 0)
)
)
OPTION (MAXDOP 1)

Answer 1

Hi @Heisenberg ,

Welcome to Microsoft Q&A!
I suggest you start reading here: https://learn.microsoft.com/en-us/sql/relational-databases/replication/administration/enhance-general-replication-performance?view=sql-server-ver15
From the left navigation bar you can also see suggestions for other replication performance improvements.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Obviously, removing OPTION(MAXDOP 1) could make it run faster - but it would also take more resources if you do this.

Have you looked at the query plan?

Since this is a procedure that is part of replication, I don't know how much is a fair game to modify.