SQL Merge replication slowing down each time a new Snapshot is generated

I have merge replication set up with 2 publications of a database. Everything is fine except recently we noticed the initial sync is taking longer and longer. Incremental syncs are fine. We have tracked the slow performance down to the fact that the number of inserts on each table (info obtained by looking at the Synchronization hostory in replication monitor) was some 20 times the number of rows in the table (always an exact multiple). Now, if I re-initialise the subscriptions and create a new snapshot, the number of inserts increments by the number of rows in each table. This is happening on both publications on this server, but on our develpment server this does not happen. Any clues anyone ?
Phil CatterallAsked:
Who is Participating?
 
Phil CatterallAuthor Commented:
Got Microsoft involved. Turned out it was a bug fixed in SQL2005 SP2
http://support.microsoft.com/default.aspx?scid=kb;EN-US;919949
What compounded the problem was that it turned out that the SQL server was reporting it was at SP2 but wasn't .
0
 
David ToddSenior DBACommented:
Hi,

I just tripped over an on-line article a couple of days ago that showed SQL slows down more and more for each snapshot - with something like 50 times slow-down at 5+ snapshots.

HTH
  David
0
 
David ToddSenior DBACommented:
Hi,

http://www.sqlservercentral.com/articles/Performance/64080/

Short answer is that snapshots are a good feature, but at a hefty performance cost. Avoid them if you can or performance is an issue.

HTH
  David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.