sql server replication: SQL2005 Merge - extremely slow on schema change -- sql server replication

SQL2005 Merge - extremely slow on schema change

Brian Jones
7/11/2007 12:00:00 AM

sql server replication:

I have a very strange issue with merge replication. I recently
published some changes to triggers and stored procedures. The script
took less than a minute to run on the master database. On a good link
(gigabit LAN) the changes replicated in about 5 minutes. However, on a
2Mb lease line it took about 3 hours !

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. Each call was on average 70ms - it looks like these are
handshaking calls between the publisher and the subscriber (the
distributor is on a separate server here).

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, or whether there are any settings that
could be tried.

Thanks

Re: SQL2005 Merge - extremely slow on schema change

Hilary Cotter
7/11/2007 12:00:00 AM

set your packetsize to something large, try 32768 or even 65536.

Also if you are making changes to stored procedures you should use snapshot
replication for this. Make your changes, and then run the snapshot. For
triggers consider using sp_addscriptexec.

--
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
[quoted text, click to view]