latecny

How can I get an idea of how long it takes for a transaction after it is made in my table I am publishing before it hits the subscriber?
TIA
Miriam
Miriam,
have a look at the Performance monitor. There are several counters for
latency eg Replication Dist. Dist: Delivered Latency.
HTH,
Paul Ibison
|||I don't like the performance monitor counters as they aren't granular
enough. For instance I could have a publication and subscriber on the same
server which will skew these counter values very high.
I prefer to do the following, on my subscriber in the subscription database
I issue a
alter table MSreplication_subscriptions
alter column time datetime
then on my publisher I issue the following
sp_serveroption 'SubscriberServerName','data access','true'
and then in my distribution database I do the following
select time, entry_time from
SubscriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
msrepl_transactions
where transaction_timestamp=xact_seqno
This will show when the distribution agent last replicated something to the
subscription database. This could be the last command or the last time the
distribution agent started.
The difference between time and entry_time is the time the transaction hit
the distribution database and the time it hit the subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23T7fKHeWEHA.500@.TK2MSFTNGP09.phx.gbl...
> Miriam,
> have a look at the Performance monitor. There are several counters for
> latency eg Replication Dist. Dist: Delivered Latency.
> HTH,
> Paul Ibison
>
|||Hi Miriam,
Assuming transactional replication here, you could monitor the dataflow by
querying MSlogreader_history (Publisher->Distributor) &
MSdistribution_history(Distributor->Subscriber).
Alternatively, create a dummy table with three columns at publisher two of
datatype datetime and at the subscriber attach a trigger on the table which
calls getdate() to populate one of the datetime columns.
Regards,
Imtiaz Govani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Would this trigger fire over the network? So supposed I have two servers 1)
the Publisher, and 2) the Subscriber. On the Subscriber table, when a record
arrives in the table I am replicating to, I create a insert/update/delete
trigger that will write to my distribution database on my Publisher that a
replicated record has arrived?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Imtiaz Govani" <imtiazg@.online.microsoft.com> wrote in message
news:3GOlszCZEHA.3272@.cpmsftngxa06.phx.gbl...
> Hi Miriam,
> Assuming transactional replication here, you could monitor the dataflow by
> querying MSlogreader_history (Publisher->Distributor) &
> MSdistribution_history(Distributor->Subscriber).
> Alternatively, create a dummy table with three columns at publisher two of
> datatype datetime and at the subscriber attach a trigger on the table
which
> calls getdate() to populate one of the datetime columns.
> Regards,
> Imtiaz Govani [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
|||Hi,
Step 1: Create this table in your publishing database
CREATE TABLE ReplLatency
(
c1 INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
pubtime DATETIME NOT NULL DEFAULT getdate(),
subtime DATETIME NULL
)
Step 2: Create a separate publication for this table
Step 3: Push this publication to the existing subscribers.
You can do this even if there are already existing publications on the
database.
Step 4: During synchronisation, the subscriber will receive the following
"Insert Stored Procedure" as part of the schema:
Add the blue line below to the Insert Stored Procedure
create procedure "sp_MSins_ReplLatency" @.c1 int,@.c2 datetime,@.c3 datetime
AS
BEGIN
select @.c3 = getdate() -- This line was added to obtain the time when
the record arrived at the subscriber
insert into "ReplLatency"(
"c1", "pubtime", "subtime"
)
values (
@.c1, @.c2, @.c3
)
END
GO
Step 5: Add a row to the publisher every xx minutes with the following
script:
WHILE 1=1
BEGIN
INSERT ReplLatency DEFAULT VALUES
WAITFOR DELAY '00:01:00'
END
Or use a job that executes the INSERT command every xx minutes.
#Using the metrics
SELECT DATEDIFF(SS,pubtime,subtime) AS Latency_in_Seconds, * FROM
ReplLatency
--To find out when we had the maximum latency:
SELECT DATEDIFF(SS,pubtime,subtime) AS Maximum_Latency_in_Seconds,* FROM
ReplLatency
WHERE (DATEDIFF(SS,pubtime,subtime)) = (SELECT MAX(
DATEDIFF(SS,pubtime,subtime)) FROM ReplLatency )
--Or the 10 longest times:
SELECT TOP 10 DATEDIFF(SS,pubtime,subtime),* FROM ReplLatency ORDER BY 1
DESC
Regards,
Imtiaz Govani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
sql