May 23, 2006 at 5:57 am
Hi
We replicate a large amount of tranactions all day to about 20 SQL 2000 subscriber servers that serve our website. These database servers were setup on blades with RAID 1 by my predecessor. Obviously this was a massive mistake as the amount of transactional replication is in itself enough to kill the disk io on the blades and cause disk queues that probably average about 100. This continuous replication is necessary so we cannot run timed or out of hours snapshot replication. I am in the process of ordering 10 new AMD 585 servers to replace these blades but these might take about a month to arrive.
Does anyone know the correct setting to use in the distribution agent profile to slow down the amount of transactions sent to the subscriber? I am wondering if i slow it down a bit the disk io will not be hammered so much and I can possible improve performance. When the replication rolls out my application tier gets failed results from the blade database servers.
-BcpBatchSize | 100000 | 100000 | 1000 | 100000 | 2147473647 |
-CommitBatchSize | 100 | 100 | 100 | 100 | 100 |
-CommitBatchThreshold | 1000 | 1000 | 1000 | 1000 | 1000 |
-HistoryVerboseLevel | 1 | 2 | 1 | 1 | 1 |
-KeepAliveMessageInterval | 300 | 300 | 300 | 300 | 300 |
-LoginTimeout | 15 | 15 | 15 | 15 | 15 |
-MaxBcpThreads | 1 | 1 | 1 | 1 | 1 |
- |
I know its these probably just need to know any suggestions from people out there.
thanks
May 23, 2006 at 7:12 am
Grant
I would consider the following:
(1) Set OutputVerboseLevel=0, unless you need to do any debugging. The default is 2 and this is probably wasting resources logging error messages and progress reports.
(2) Experiment with smaller values of CommitBatchSize. Because you have 20 subscribers, all continuous, it's likely that you have contention for resources in your distribution database. One way to alleviate this is to force individual distribution agents not to hold locks for so long by committing smaller batches at a time.
For more information, see the Replication Distribution Agent Utility topic in Books Online. Also, there are some great tips on sql-server-performance.com.
John
May 24, 2006 at 6:53 pm
Do you have the data files and log files on separate drives? This should improve performance considerably.
Also, I like to put the tempdb on a third drive all its own.
Lastly, have you looked into using a SAN?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply