Re: Major Latency problems
From: Hilary Cotter (hilaryk_at_att.net)
Date: 07/07/04
- Next message: Hilary Cotter: "Re: huge tempdb"
- Previous message: Hilary Cotter: "Re: Missing table log_shipping_monitor table"
- In reply to: frankm: "Re: Major Latency problems"
- Next in thread: frankm: "Re: Major Latency problems"
- Reply: frankm: "Re: Major Latency problems"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 7 Jul 2004 10:41:03 -0400
Point 1 - no problem, you don't have to alter this table. Altering it will
give you a latency by second, not altering it will give you latency by
minute
Point 2 - I'm confused here. Initially you said that latency is sometimes
1-3 hours, and now you are saying it is 3-5 seconds? Am I missing something?
Do you perhaps mean that the latency can be 3-5 hours, but it is currently
3-5 seconds? If so, your Distribution Agent has possibly failed at some
point in time. Check the msrepl_errors table and the MSdistribution_history
to see if you can figure out what happened. The Agent History dialog might
also help here.
Point 3 - that's a lot of commands, but not atypical for high volume
publishers. What is your min and max retention period? Has your Distribution
Clean Up Agent run recently? Does it's agent history report errors? I'd ask
you to run an sp_helpdistributor and report the values of min distrib
retention and max distrib retention, but something seems very wrong with
this procedure on your Publisher? I take it that is where you ran this proc.
Point 4 - see point 3
Point 5 - OK, this is not always a viable option.
-- Hilary Cotter Looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html "frankm" <frankm@nospam.postalias> wrote in message news:%23xJf3oBZEHA.3432@TK2MSFTNGP10.phx.gbl... > Point 1 - Alter Table ... I would much rather stay away from the alter, this > is a really critical server and db... > Point 2 - Latency Figures ... The Repl Monitor figures for latency will fall > back to 3-5 second range and will fall slightly with less use. > Point 3 - msrepl_commands ... 14,164,568 > Point 4 - sp_helpdistributor .... all I get back is mostly NULLS > SERVER NULL NULL LOGIN NULL NULL NULL NULL NULL repl_distributor > distributor_admin > Point 5 - SP Execution ... no choice - we need to replicate the tables. > > > > > "Hilary Cotter" <hilaryk@att.net> wrote in message > news:O%23nlj$FYEHA.3260@tk2msftngp13.phx.gbl... > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
- Next message: Hilary Cotter: "Re: huge tempdb"
- Previous message: Hilary Cotter: "Re: Missing table log_shipping_monitor table"
- In reply to: frankm: "Re: Major Latency problems"
- Next in thread: frankm: "Re: Major Latency problems"
- Reply: frankm: "Re: Major Latency problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
