Re: Bad replication performance in SQL2k5 on links with high latency
- From: "Hilary Cotter" <hilary.cotter@xxxxxxxxx>
- Date: Mon, 13 Nov 2006 07:54:54 -0500
the distribution agents in SQL 2005 has autosense network link technology
built into them. They will close down when they detect the link going down
and start up again when it is reconnected. I suspect you are bumping into
this.
I would open a support incident with MS to see if you can disable this
behavior or troubleshoot it further.
--
Hilary Cotter
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
"andsm" <andsm@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D153A24B-63DF-45A6-A4B4-22A00221CA29@xxxxxxxxxxxxxxxx
I tested replication performance on slow network channels, for SQL2k and
SQL2k5. And quite disapplointed by SQL2k5 results...
Network channels were simulated, ping time 200 ms between distributor and
subscriber, unlimited bandwidth.
I tested how many transactions replication can deliver to subsciber. All
transactions are nearby same, they represent typical transactions in my
system.
Publisher, distributor and subsciber are all located on different
computers.
In order to check capabilities of hardware, on each of the computers I run
tests to check speed of how many transactions per second it may insert
into
database with turned on replication. For each computer, result was ~112
transactions per second, both for SQL2k and SQL2k5.
Next I started to measure replication delivery rate, how many transactions
per second distributor will be able to deliver to subscriber.
In SQL2k, replication in such condition was able to deliver 9 transactions
per second. The best result was obtained with 1 sec polling interval for
log
reader and distribution agent, 64kb packet size for distribution agent. No
more differences from default profile.
In case if turn off network delay, rate of inserting transactions on
publisher become same as rate of delivering - replication start to deliver
~112 transactions per second.
For SQL2k5, results were around 5 transactions per second. The best result
was obtained on 1 sec polling interval for log reader and distribution
agent,
64kb packet size for distribution agent, 3 SubscriptionStreams. No more
differences from default profile. If turn off network delays, it again
deliver at same rate as it inserting on publisher, about 112 transactions
per
second.
Network delay simulator was tested several times to check is it really
simulate network delays as expected. All tests showed correct work of
network
delay simulator. For simulating network delays, was used Shundra Cloud.
The question is - Is the replication in SQL2k5 really lost performance in
comparison with SQL2k? Is anything can be done to improve replication
delivery rate in SQL2k5?
.
- Follow-Ups:
- Prev by Date: Re: SQL 2005 Management Studio and "logbased manualview" articles
- Next by Date: Best Way of data transfer
- Previous by thread: Re: SQL 2005 Management Studio and "logbased manualview" articles
- Next by thread: Re: Bad replication performance in SQL2k5 on links with high laten
- Index(es):
Relevant Pages
|
