x

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 '12 at 12:59 PM in Default

Jokin42 gravatar image

Jokin42
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][1] meant for performance enhancement.

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

[1]: http://msdn.microsoft.com/en-us/library/ms152770%28v=sql.90%29.aspx
more ▼

answered Jan 18 '12 at 10:19 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1933
x293
x22

asked: Jan 18 '12 at 12:59 PM

Seen: 806 times

Last Updated: Jan 19 '12 at 02:41 AM