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

Posted on 2008-10-09
Last Modified: 2008-11-11
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 ?
Question by:Phil Catterall
  • 2
LVL 35

Expert Comment

by:David Todd
ID: 22683932

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.

LVL 35

Expert Comment

by:David Todd
ID: 22683936

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.


Accepted Solution

Phil Catterall earned 0 total points
ID: 22901072
Got Microsoft involved. Turned out it was a bug fixed in SQL2005 SP2;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 .

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Querying data from 3 SQL tables 2 32
SQL - Update field defined as Text 6 16
SQL Query assistance 16 24
SQL Count issue 24 15
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question