Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table lock on MSmerge_contents Expand / Collapse
Author
Message
Posted Monday, July 1, 2013 6:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:51 AM
Points: 248, Visits: 793
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.
Post #1469250
Posted Wednesday, July 3, 2013 9:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:51 AM
Points: 248, Visits: 793

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
Post #1470101
Posted Monday, July 8, 2013 5:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:51 AM
Points: 248, Visits: 793
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?
Post #1471396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse