UPDATE Statement and Filtered Merge Publication

VDT-7677 121 Reputation points
2022-07-01T00:55:56.023+00:00

Hi,

Running SQL Server 2008 R2. Publisher and distributor are on same machine in a merge replication topology using pull subscriptions.

A query was executed this morning which updated almost 1M rows in one column and 35K rows in another (SET Column = 0 WHERE Column IS NULL).

The vast majority of the updated rows would not be included in our merge publication (the top-level article the updated table is joined to uses a filter). However, our subscriber synchronizations have slowed to a crawl since that statement was ran. I suspect that this is due to the metadata generated due to the UPDATE statement that was executed. Is there any way I can confirm this, and if so, remove the metadata of that particular statement?

Thanks!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,314 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,416 Reputation points
    2022-07-01T06:31:32.203+00:00

    Hi @VDT-7677 ,

    Welcome to Microsoft Q&A!
    Please refer to this document: https://learn.microsoft.com/en-us/sql/relational-databases/replication/administration/enhance-merge-replication-performance?view=sql-server-ver16
    Hope this would give you some help.

    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.


  2. Tom Phillips 17,711 Reputation points
    2022-07-01T15:31:29.297+00:00

    Replication captures the commands and reruns the commands on the subscribers. There is no way to prevent some commands from being run and others to run. It must run all the commands.

    You will need to wait for it to finish.