Re: Major Latency problems

From: Hilary Cotter (hilaryk_at_att.net)
Date: 07/02/04 

Date: Fri, 2 Jul 2004 14:05:04 -0400

you don't have to do this alter. If you do your results will be in seconds
as opposed to a to the minute value.

This table is created when you create a subscription. I don't think there is
anything I can do to make you comfortable about doing this change, and
that's fine with me. The reason that I don't trust the latency figures in
replication monitor is because they are occasionally wrong. They tend to
represent cumulative values since the last time the distribution agent
started or did something. So for instance let suppose you have no updates
for 1 minute and then you do an update. Replication monitor latency is 1
minute + the distribution time.

This is why I am trying to point you into a direction of getting a more
accurate estimate of acutal latency. On a side note regarding performance:

How large is your msrepl_commands in your distribution database?

What is your retention? run sp_helpdistributor for that
figure(min_distrib_retention and max_distrib_retention)? Is your
distribution clean up agent running every 10 minutes?

Have you thought about replicating the execution of stored procedures? This
can provide drammatic performance improvments. 

-- 
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"frankm" <frankm@nospam.postalias> wrote in message
news:%23UP$zTFYEHA.2944@TK2MSFTNGP11.phx.gbl...
> Ok, you're asking me to alter a stock replication system table.
> I need to understand the ramifications of this. It seems to me that this
is
> not good practice.
> Why is the latency figure in replication monitor not useful?
>
>
>
> "Hilary Cotter" <hilaryk@att.net> wrote in message
> news:etebTUEYEHA.2972@TK2MSFTNGP12.phx.gbl...
> > The most critical part of this discussion is how are you measuring
> latency?
> >
> > Can you run this script for me.
> >
> > on your guinea pig subscriber in your subscription database run this
> >
> > alter table MSreplication_subscriptions
> > alter column time datetime
> >
> > then on your publisher issue the following  in your master database
> >
> > sp_serveroption 'GuineaPigServerName','data access','true'
> >
> > and then in your distribution database run the following
> >
> > select time, entry_time from
> >
>
GuineaPigServerName.SubscriberDatabaseName.dbo.MSreplication_subscriptions,
> > msrepl_transactions
> > where transaction_timestamp=xact_seqno
> >
> > This will tell you the current latency on your subscriber. You might
want
> to
> > do this for all subscribers,
> >
> >
> > -- 
> > Hilary Cotter
> > Looking for a book on SQL Server replication?
> > http://www.nwsu.com/0974973602.html
> >
> >
> > "frankm" <frankm@nospam.postalias> wrote in message
> > news:u6K6saDYEHA.384@TK2MSFTNGP10.phx.gbl...
> > > Scenario is:
> > > Publisher database on 16way/8gb box, distributor on 4way/4GB
> (standalone),
> > 7
> > > subscribers  (used as reporting - not updateable), continuous
> > transactional
> > > replication. The storage is a new EMC DMX3000.
> > > During working hours, the latency increases almost continually to a
> point
> > > that it will be 1 - 3 hours on latency, there are some small drops in
> > > milliseconds (100-6000) but the curve is almost linear - up. At least
3
> of
> > > the subscribers will increase latency even with no users on (these
> servers
> > > are used only for the 1 reporting database each). We just added a new
> > > subscriber (7) that is a 64bit / some Solid State disk and it is in
the
> > 2-20
> > > second latency (the ~20 seconds are very short duration). I am doing
> index
> > > defrag nightly with update stats and "Auto Update Statistics" turned
> off.
> > > The filesystems are defraged. Most of the files are on their own
volumes
> > > (logs - data - indexes), tempdb is on it's own volume and is broken up
> to
> > 10
> > > files 2GB. Each  file is oversized so as not to incur growth costs.
> Queue
> > > lengths generally spike in the 5-10 range (perfmon - spikes - not
> > > continuous) and spread across multiple disks so the queue lengths are
> > > effectively near 0. On the subscriber agents; I have
> > > added -OutputVerboseLevel 0 -HistoryVerboseLevel 1 to the command
line.
> > >
> > > I have watched this for at least 3 months now and cannot attribute the
> > > problem to anything.
> > > Right now I have picked one machine / subscriber to use as my guinea
> pig.
> > >
> > > Any help - pointers - etc would be very much appreciated.
> > >
> > > frankm
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Major Latency problems
    ... you're asking me to alter a stock replication system table. ... Why is the latency figure in replication monitor not useful? ... > This will tell you the current latency on your subscriber. ...
    (microsoft.public.sqlserver.replication)
  • Re: Major Latency problems
    ... give you a latency by second, not altering it will give you latency by ... your Distribution Agent has possibly failed at some ... Looking for a book on SQL Server replication? ...
    (microsoft.public.sqlserver.replication)
  • Re: Major Latency problems
    ... Point 1 - Alter Table ... ... Point 2 - Latency Figures ... ... SERVER NULL NULL LOGIN NULL NULL NULL NULL NULL repl_distributor ... > replication monitor is because they are occasionally wrong. ...
    (microsoft.public.sqlserver.replication)
  • Re: Distribution CleanUp creates latency
    ... You could run the distribution clean up job nightly and see how the ... Looking for a SQL Server replication book? ... latency would always be under 2 seconds. ... > What can be done to tame the distribution cleanup jog. ...
    (microsoft.public.sqlserver.replication)
  • Re: Missing ForestDNSZones and DomainDNSZones partitions under child AD 2003 domain
    ... REPLICATION LATENCY WARNING ... Replication of new changes along this path will be delayed. ... Latency information for 3 entries in the vector were ... had no latency information (Win2K DC). ...
    (microsoft.public.windows.server.dns)