Replication Performance tuning question

I am trying to make sure the new replicate
distribution server is the best it can be. Below
are some tips I read on a site. It seems like
the first two tips almost conflict with each
other. however, they could be talking about
maybe a log reader agent on the first one. Most
of the publications I am going to be setting up
are large (5gb each x 20). anyone have some good
settings for a log reader profile settings for
publications around this size? Lots of these
publications are all going to be setup at the
same time, which seems to cause havoc on log
readers for replication.
Are the tips below good ones? can anyone add
some?
Thanks,
You can enhance the performance of transactional
replication in your application and on your
network by:
- Running agents continuously instead of on
frequent schedules.
- Reducing the distribution frequency when
replicating to numerous Subscribers. (we do
15min, default is 1hr)
- Configuring the Distributor on a dedicated
server. (we do this)
- Increasing memory on the Distributor. -
Minimizing the retention period for transactions
and history.
- Increasing the read batch size for the Log
Reader Agent.
- Using custom stored procedures for inserts,
updates, and deletes at Subscribers. (
The items you mention seem reasonable, apart from the last one which I
haven't heard of, apart from in the context of bidirectional transactional
replication. For an indepth, numerical analysis of parameters, have a look
at this article:
http://www.microsoft.com/technet/pro.../tranrepl.mspx
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||For small numbers of subscribers run continuously. The more subscribers you
have the more likely you will run into locking contention at some point in
time. You will then have to start scheduling your subscriber agents.
The log reader agent should always run continously.
The merge agent runs best when it processess small chunks of data. So if you
have a high volume application and a small number of subscribers run it
continously. If you have many subscribers you should schedule it to run
frequently. The frequency you run your merge agent at is a function of the
rate of transactions and the number of subscribers. Then you have to factor
in the fact that if you have too many subscribers running simultaneously
there will be locking.
If you have a large numberof transactions occuring up your batch size for
the log reader. Most of the time your log reader will keep up with the
transactions been written to the log. Issue sp_repltrans to get an idea of
the number of transactions in the queue to be read. If there are many
transactions there you might want to think about increasing the batch size.
When they talk about using custom stored procedures they are talking about
the custom stored procedures that SQL Server generates to apply the
transactions. Your other option is to use SQL statements, ie insert, update,
select. These SQL Statements are not optimized.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1bb3cc80fffa6a039896ae@.news.newsreader.co m...
> I am trying to make sure the new replicate
> distribution server is the best it can be. Below
> are some tips I read on a site. It seems like
> the first two tips almost conflict with each
> other. however, they could be talking about
> maybe a log reader agent on the first one. Most
> of the publications I am going to be setting up
> are large (5gb each x 20). anyone have some good
> settings for a log reader profile settings for
> publications around this size? Lots of these
> publications are all going to be setup at the
> same time, which seems to cause havoc on log
> readers for replication.
> Are the tips below good ones? can anyone add
> some?
> Thanks,
>
> You can enhance the performance of transactional
> replication in your application and on your
> network by:
> - Running agents continuously instead of on
> frequent schedules.
> - Reducing the distribution frequency when
> replicating to numerous Subscribers. (we do
> 15min, default is 1hr)
> - Configuring the Distributor on a dedicated
> server. (we do this)
> - Increasing memory on the Distributor. -
> Minimizing the retention period for transactions
> and history.
> - Increasing the read batch size for the Log
> Reader Agent.
> - Using custom stored procedures for inserts,
> updates, and deletes at Subscribers. (
|||In article <OMBeaAMnEHA.608
@.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
says...
> For small numbers of subscribers run continuously. The more subscribers you
> have the more likely you will run into locking contention at some point in
> time. You will then have to start scheduling your subscriber agents.
> The log reader agent should always run continously.
> The merge agent runs best when it processess small chunks of data. So if you
> have a high volume application and a small number of subscribers run it
> continously. If you have many subscribers you should schedule it to run
> frequently. The frequency you run your merge agent at is a function of the
> rate of transactions and the number of subscribers. Then you have to factor
> in the fact that if you have too many subscribers running simultaneously
> there will be locking.
> If you have a large numberof transactions occuring up your batch size for
> the log reader. Most of the time your log reader will keep up with the
> transactions been written to the log. Issue sp_repltrans to get an idea of
> the number of transactions in the queue to be read. If there are many
> transactions there you might want to think about increasing the batch size.
> When they talk about using custom stored procedures they are talking about
> the custom stored procedures that SQL Server generates to apply the
> transactions. Your other option is to use SQL statements, ie insert, update,
> select. These SQL Statements are not optimized.
>
thanks once again hilary..I appreciate yours and
pauls help..
-comb