none
Transactional replication - merge command performance

    Question

  • Hello,

    I'm trying to find a solution to one performance issue with replication that I'm facing at the moment, so I'm hoping someone here could help.

    I have one publisher database with 3 subscribers, and occasionally I need to update the publisher database with relatively large number of rows.

    For that purpose I'm using merge operation, having separate database with new data as source and publisher database as target. Result of merge operation shows that there are 3 "problematic" tables that generate total of around 300 millions of rows to be distributed.

    Publisher to Distributor History shows that data is transferred to distributor in acceptable time manner, but Distributor to Subscriber is taking very long. I traced the process on subscriber using the Profiler and saw that each row is being updated/inserted/deleted separately, and I believe that this causes the performance issue.

    I was wandering if there is a way to do the merge operation on subscriber in batches, and not row by row?

    I did the reinitialization on one subscriber and it worked fine and relatively quick, but it is not the option since I'm expecting increase of number of subscribers in future, and I need the process to be automatic.

    Any help would be highly appreciated! :)

    BR,

    Miljan

    Tuesday, June 11, 2013 8:42 AM

Answers

All replies

  • Merge replication does not move data from the Pub to Dist but will directly move from Pub to Dist.

    Explore the parameters downloadgenerationsperbatch, DownloadWriteChangesPerBatch for the merge agent.

    http://msdn.microsoft.com/en-us/library/ms147839.aspx

    Tuesday, June 11, 2013 9:37 PM
  • sp_changemergepublication@publication='<publication name>',

    @property= 'generation_leveling_threshold',

    @value= '10000'

    http://blogs.msdn.com/b/repltalk/archive/2011/04/24/reducing-impact-of-large-updates-on-merge-replication.aspx

    Manish

    Monday, June 17, 2013 4:51 AM
  • I solved the problem by switching those big tables to separate snapshot replication.

    This works well enough for now :)

    Thank you for your time and help!

    Best regards,

    Miljan

    Monday, June 17, 2013 7:13 AM
  • Hello,

    Thanks for your sharing. It will be very beneficial for other community members who have similar requirement.

    Regards,

    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Tuesday, June 18, 2013 7:43 AM