Post reply

Table lock on MSmerge_contents

  • July 1, 2013 at 6:13 pm

    #276797

    We are running merge replication over a WAN.

    Only changes to the Subscriber are replicated to the Publisher ( -ExchangeType 1 ).

    We are using pull subscriptions

    When the merge agent is selecting records from the MSmerge_contents on the subscriber to pull data to the publisher, we are getting Table locks on MSmerge_contents.

    This in turn blocks writes to the tables because the triggers are blocked. These can last as long as 1500ms and more, negatively affecting the application writing at the subscriber.

    I have uploadwritechangesperbatch, uploadwritechangesperbatch and uploadgenerationsperbatch all set to 1000 for increased performance.

    Should I reduce the setting of uploadgenerationsperbatch?

    All indexes on the meta data tables are rebuilt every night. I also keep the meta data tables trimmed to 14 days of data.

    Any ideas as to how I can avoid the table lock is appreciated.

  • July 3, 2013 at 9:05 am

    #1629562

    I am testing reducing the value for uploadgenerationsperbatch.

    I will update once I have conclusive results.

    In the meantime any insights from the community would be appreciated.

    Thanks,

    -ap

  • July 8, 2013 at 5:55 pm

    #1630615

    After testing with uploadgenerationsperbatch set to 10 and 20, I still see table locks,

    but they are shorter in duration (100-500ms as opposed to 1-5 seconds).

    However I am getting 30-40% less throughput.

    I will be testing with uploadgenerationsperbatch set to 50 and 100 later in the week,

    once my remote site is back on line.

    Does anyone have any insights?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply