Re: Major Latency problems
From: frankm (frankm_at_nospam.postalias)
Date: 07/02/04
- Next message: Dean: "sp_MSupd_ stored procedures with IDENTITY property"
- Previous message: José Araujo: "Re: Replicate Trigger and Stored Procedure"
- In reply to: Hilary Cotter: "Re: Major Latency problems"
- Next in thread: Hilary Cotter: "Re: Major Latency problems"
- Reply: Hilary Cotter: "Re: Major Latency problems"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 2 Jul 2004 11:52:23 -0500
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
> >
> >
>
>
- Next message: Dean: "sp_MSupd_ stored procedures with IDENTITY property"
- Previous message: José Araujo: "Re: Replicate Trigger and Stored Procedure"
- In reply to: Hilary Cotter: "Re: Major Latency problems"
- Next in thread: Hilary Cotter: "Re: Major Latency problems"
- Reply: Hilary Cotter: "Re: Major Latency problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
