sql server replication: Usage of several connections to deliver changes from distr to subs -- sql server replication

Usage of several connections to deliver changes from distr to subs

andsm
10/20/2006 10:41:02 AM

sql server replication: I simulating transactional replication on network with high bandwidth but
with big latency (it is expected between Chicago and Singapore). I set 400 ms
network latency and unlimited network bandwidth, with usage of network delay
simulation software. Next I measure rates at which transactions are delivered
to subscriber from distributor. If set SubscriptionStreams to 2, it deliver
transactions approximately 1.7 times faster than by default
(SubscriptionStreams = 1). In case if set it to 3 - very small increase over
2. If set it to 64 - it simply not works, no tranactions were delivered. Load
on subscriber during the tests was very low, was no any CPU or disk IO
bottlenecks. So, it looks like maximum potential effect from
SubscriptionStreams is : ~2 times more transaction delivery rate, and
optimal value of SubscriptionStreams is 2. Anyone have experience of usage

Re: Usage of several connections to deliver changes from distr to subs

Hilary Cotter
10/20/2006 2:04:52 PM

You get maximum benefit from subscriptionstreams with better hardware. It is
also possible that there is an error in the batch and there are considerable
retries. When there is a batch the subscriptionstreams property drops to 1
until the error is cleared and then increases to the original parameter. You
might be bumping into this problem.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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]