Sql server transactional replication latency

Question

I'm analyzing the distribution database to see if there any latency in the replication between publisher and subscriber

The architecture is one publisher, 1 distrubution db (on publisher server) and one subscriber on different server.

I would also like to build a custom historical log table that can contain a record of any latency issues over the past month. Looking at the distribution db there is a table called MSdistribution_history . But the latest row in this table is constantly changing e.g the comment could say "61 transactions with 230 commands delivered" . If I query the table in 5 seconds later the latest row will say have updated again and I'm having trouble understanding what this data means. Anyway I can't keep polling this table every few seconds, can you explain what is the best way to track latency over time and better understand the data in this table ?

Answer 1

Back when I supported environments with transactional replication, I had a policy of injecting tracer tokens on a regular interval. What I've typically seen is that they're only used once there's already significant lag at the subscriber (having been alerted through some other mechanism) and by that time it's already too late; you'll insert a tracer token and verify what you already know.

By injecting tokens on a cadence, you'll have a sense of when things started to get behind and which leg of the process (i.e. pub → dist or dist → sub, though it's almost always the latter) you should look at. Inject the token however you want (e.g. a SQL Agent job) using sp_posttracertoken.

Here's the view that I created in the distribution database to look at the token performance over time:

create view [dbo].[tokens] as
select
    ps.name as [publisher] ,
    p.publisher_db ,
    p.publication ,
    ss.name as [subscriber] ,
    da.subscriber_db ,
    t.publisher_commit ,
    t.distributor_commit ,
    h.subscriber_commit ,
    datediff(second, t.publisher_commit, t.distributor_commit) as [pub to dist (s)] ,
    datediff(second, t.distributor_commit ,h.subscriber_commit) as [dist to sub (s)] ,
    datediff(second, t.publisher_commit, h.subscriber_commit) as [total latency (s)]
from mstracer_tokens t
inner join MStracer_history h
    on t.tracer_id = h.parent_tracer_id
inner join mspublications p
    on p.publication_id = t.publication_id
inner join sys.servers ps
    on p.publisher_id = ps.server_id
inner join msdistribution_agents da
    on h.agent_id = da.id
inner join sys.servers ss
    on da.subscriber_id = ss.server_id;