The message said replication was broken: the reporting subscriber was forty-five minutes behind the publisher and the gap was growing. Replication was not broken. The distribution agent was cheerfully applying commands, one at a time, as fast as its single thread could go — and the command it was working through was a DELETE that had touched two million rows on the publisher in one transaction. On the publisher that delete took ninety seconds of set-based work. On the subscriber it was replaying as two million individual DELETE statements, each with its own round trip, and the queue behind it contained the next forty minutes of normal traffic. Nothing was down, nothing was hung, and no restart would fix anything. The pipeline was doing exactly what it was built to do, at the speed it was built to do it.
Transactional replication latency is almost always a pipeline question, and the pipeline has exactly three stages. Learn to read the stages and the diagnosis stops being guesswork. This is the version I use, on SQL Server 2016 through 2022 with push or pull subscriptions.
What are the three stages, and which one is actually slow?
Stage one is the log reader agent, one per published database: it scans the publisher's transaction log, picks out the transactions belonging to published articles, and writes them as commands into the distribution database. Stage two is the distribution database itself — a queue on disk, holding commands not yet delivered. Stage three is the distribution agent, one per subscription (unless you share via independent agents off), which reads commands from the distribution database and applies them at the subscriber. Latency is end-to-end, publisher commit to subscriber commit, and it is the sum of the stages — so the first diagnostic act is always to decompose it, because the fix for a slow log reader has nothing in common with the fix for a slow distribution agent.
The decomposition tool is the tracer token: a synthetic marker you push through the pipeline, and Replication Monitor reports how long it took at each stage. From T-SQL you get the same data without the GUI:
-- on the publisher, in the published database:
EXEC sys.sp_posttracertoken @publication = N'OrdersPub';
-- moments later, see where it is:
EXEC sys.sp_helptracertokenhistory @publication = N'OrdersPub',
@tracer_id = @@IDENTITY;The history splits the time into publisher-to-distributor and distributor-to-subscriber. A tracer token that crosses stage one in two seconds and stage three in forty minutes has just told you the log reader is innocent and the distribution leg is guilty, without touching a single agent log. In my incident the token confirmed what the queue already suggested: commands were arriving in the distribution database promptly and leaving slowly.
How do I measure the backlog and the agent health?
The distribution database keeps both. The backlog question — how much work is queued — is answered by counting undelivered commands, and the agent behavior question is answered by each agent's history tables, which log sessions, rates, and errors with timestamps:
-- backlog per subscription (run in the distribution database):
EXEC sys.sp_replmonitorsubscriptionpendingcmds
@publisher = N'PUB01',
@publisher_db = N'Orders',
@publication = N'OrdersPub',
@subscriber = N'SUB01',
@subscription_type = 0;
-- recent distribution agent sessions:
SELECT TOP 20 start_time, duration, delivered_commands,
delivered_transactions, error_id
FROM dbo.MSdistribution_history
ORDER BY start_time DESC;MSdistribution_history is the table I live in during an incident: delivered_commands per interval tells you the agent's actual throughput, and when you divide the pending command count by that throughput you get an honest ETA instead of a shrug. That arithmetic is also how you decide whether to intervene. A queue of 300,000 commands draining at 400 per second is thirteen minutes — walk away and let it drain. The same queue draining at 8 per second is ten hours, and now you have a real problem and a mandate to change something.
Why does one big transaction dominate the pipe?
Because replication replays changes as commands, and a set-based statement becomes row-based on the wire. The publisher executes DELETE FROM Orders WHERE OrderDate < '2023-01-01' as one set operation; the log reader converts the affected rows into two million delete commands; the distribution agent applies them one by one — or in small batches — through a single connection. The asymmetry is the entire story of most replication incidents: any batch operation, index rebuild on a published table, or mass update that is trivially cheap set-based becomes expensive row-by-row at the subscriber. Two million rows at a few thousand commands per second is the forty-five-minute gap, and the traffic queued behind the batch inherits the delay.
The agent has knobs that help around the edges: -SubscriptionStreams lets one distribution agent apply batches over multiple parallel connections (with its own consistency caveats — streams can serialize again on dependencies between batches), and agent profiles tune commit batch sizes. These move the throughput, sometimes substantially, but they do not change the physics that row-based replay of a giant batch is slow. The durable fix is upstream: never run the giant batch as one giant batch on the publisher. Delete in chunks of a few thousand rows per transaction, and each chunk flows through the pipeline as a small, independently applicable unit that interleaves with normal traffic instead of damming it. Chunked writes are mildly more work for the publisher and dramatically kinder to every downstream system — the same advice I give for the log itself in the transaction log notes, arriving by a different route.
What else makes each stage slow?
The log reader's classic enemies are an overgrown log to scan — a publisher with a huge active log or poor VLF health makes the reader work harder, which is another reason the VLF hygiene matters — and a publisher that is simply too busy to let the agent keep pace. The distribution database's enemy is its own cleanup: the cleanup job that purges delivered commands can fall behind, the MSrepl_commands table grows into the tens of millions of rows, and every agent query against it slows down, which slows delivery, which grows the table further. If your distribution database lives on undersized storage, latency incidents will recur at a cadence matching your cleanup struggles. The distribution agent's enemies beyond batch size are subscriber-side: blocking on the subscriber from reporting queries holding locks the agent needs — the same chain mechanics from my blocking notes, with the agent as the victim — missing indexes on the subscriber's side of a heavily updated table, and a subscriber that is underpowered for the write rate it signed up for.
One trap that deserves its own sentence: do not "fix" latency by reinitializing unless you have confirmed the pipeline is corrupt rather than merely queued. A reinitialization snapshots and reapplies entire articles, which for a large publication is hours of load on publisher, network, and subscriber — and if the underlying cause was a chunked-delete problem, you will be back in the same queue next month with the reinit scars added. Reinitialization is the answer to inconsistency. Latency is almost never inconsistency.
What should monitoring catch before the tickets do?
Latency decomposed by stage, because the aggregate number alone does not tell you where to work. Pending command counts per subscription, trended, so the queue is visible while it is still small. Distribution agent throughput from the history tables, because a declining commands-per-second rate is the earliest warning of subscriber-side trouble. Distribution database size and cleanup lag. And a standing tracer token on a schedule — a token posted every fifteen minutes costs nothing and turns "is replication behind?" from a question into a chart. The Always On lag notes cover the availability-group side of the same instinct: any pipeline that moves data should report its own delay, per stage, continuously.
Watching replication latency with MonPG when SQL Server support lands
The counters that matter here are exactly the pipeline reads from above: tracer token latency split by stage, undelivered commands per subscription, agent delivery rates, and distribution database growth — plus alerting on the derivative, because a queue growing faster than its drain rate is the incident, whatever the absolute numbers say. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, the pending-commands procedure and the history-table query on a five-minute schedule, written to a table you chart, is a complete replication latency monitor in about forty lines of SQL.