July 3, 2007 at 4:36 am
Hi all,
I have a strange problem with a merge replication setup. Schema changes (as mild as altering a trigger or stored procedure) take a very long time. The merge replication itself runs fine. Some of the servers are on a 10Mb link, and it is here that the performance issue is most visible. The link is not particularly slow, the latency is not too bad either, so this makes no sense. Bear in mind that these are trigger/sp changes, not actual chagnes to table schemas.
Is this just standard behaviour, or is there some areas I could look ? I'm not sure that the agent profile for slow connections will make much difference as this seems to deal with the replication of data rather than ddl changes.
Thanks
July 3, 2007 at 8:30 am
You could try profiling the merge replication to gain a better insight into what is happening behind the scenes.
Paul
July 4, 2007 at 6:40 am
Had a chance to look at this today. It runs the sp_MSunmarkschemaobject on every object (I think), irrespective of whether it has been touched by the trigger/sp changes. This strikes me as being a little strange - I can't see why it needs to do this.
The server in question was offline when the original changes were applied, so is replicating the changes first then appears to replicate the data differences after.
I've searched a bit for any documentation on this procedure - no joy yet. MS documentation is sparse on this to say the least.
Any help would be appreciated.
July 9, 2007 at 7:21 am
Further information, the calls to sp_MSunmarkschemaobject, although excessive, take very little time. It calls this SP for each and every object in the database once per change. So if you are applying say 10 changes, this SP will be called 10 times for each object in the database.
However, this was not the real killer of the performance. Analysis of the trace showed that calls to sp_MSreplcheck_subscribe and sp_MSreplcheck_subscribe_withddladmin were extremely excessive. For 172 changes, these were called 112663 and 28036 times respectively, which equated to about 2 hours 50 mins over a 2 meg link. The changes ran in less than a minute on the source database.
We tried the "slow" profile for the distribution agent - had little or no effect.
So I guess the question is - does anyone know how to limit the calls to these procedures as these are definitely the reason that this replication performs so poorly.
Thanks again.
July 9, 2007 at 3:51 pm
Well that sounds like a terrible bug. I guess you will have to open a case with PSS.
Sorry!
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply