asked
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?
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
