2005 Merge replication performance problem

I am relatively new to replication. I had never worked with it before and when I started my current job it had just been set up, this was about 1 and 1/2 years ago.

  1. Our setup - We have merge replication setup between our 2 plants, 1 plant is the publisher and distributor while the other plant is the subscriber. The merge agent runs every 5 minutes.

  2. Our problem - The merge/syncronizations were taking 1 minute or so to complete, but recently, about 2 weeks ago, the amount of time each run took started increasing to the point that it now takes around 19 minutes. Looking at the replication monitor I can see that the uploads take 99% of the time, as an example, the last merge took 19:09 to complete, there were 84 uploads(48 inserts, 30 updates, and 6 deletes) which accounted for 18:43 of the job where the download had 1985 transactions(1191 inserts, 741 updates and 53 deletes) which only accounted for 19 seconds.

What is boggling to me is how 4% of the records take up 99% of the time. There is obviously something going on, I've done a lot of reading but have yet to find a solution, or a method to troubleshoot the problem. One other thing, not every time, but occasionally the replication monitor shows the message, "The replication agent has not logged a progress message in 10 minutes." . All records are being replicated and the subscriber, publisher and distibutor all seem to be up and running.

Does anyone have any idea as to what could be causing the problem?

more ▼

asked Jan 18, 2012 at 12:59 PM in Default

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

There could be many reasons for this. Since replication monitor displays detailed statistics in the Synchronization History tab for each article processed during synchronization, including the amount of time spent in each processing phase (uploading changes, downloading changes, and so on). It can help pinpoint specific tables that are causing slow downs and is the best place to troubleshoot performance issues with merge subscriptions. Moreover, this could lead to you missing indexes on columns used in row filters and join filters (depending upon your environment). One thing most of the time overlooked is re-indexing merge replication system tables occasionally.

Please visit this BOL meant for performance enhancement.

Moreover, since everything is working fine, you can ignore the progress message.

more ▼

answered Jan 18, 2012 at 10:19 PM

avatar image

Usman Butt
14.8k 6 13 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 18, 2012 at 12:59 PM

Seen: 1225 times

Last Updated: Jan 19, 2012 at 02:41 AM

Copyright 2018 Redgate Software. Privacy Policy