How to Monitor Replication Throughput
Identify issues with SQL Server replication by checking what commands are being passed through the distributor

Phil Grayson
Published Sep 2017
It’s a right laugh when replication goes wrong, if you haven’t been there and use replication heavily, you’re in for the night / early morning of your life. Like any issue, the cause of replication problems could be one of many, but checking what commands are being passed through the distributor is always worthwhile, as an inefficient query could be updating the same data over and over, putting unnecessary strain on the distributor, infrastructure or the subscribers.
The following queries check the transaction counts per minute, with the second query breaking it down by article.
USE distribution;
GO
—Track the throughput per minute
SELECT CONVERT(SMALLDATETIME, t.entry_time) AS EntryTime,
COUNT(1) AS Commands
FROM MSrepl_commands cmds
INNER JOIN MSarticles a ON a.article_id = cmds.article_id
INNER JOIN MSrepl_transactions t ON cmds.xact_seqno = t.xact_seqno
GROUP BY CONVERT(SMALLDATETIME, t.entry_time)
ORDER BY CONVERT(SMALLDATETIME, t.entry_time) DESC;
—Track the throughput per article, per minute
SELECT a.article,
CONVERT(SMALLDATETIME, t.entry_time) AS EntryTime,
COUNT(1) AS Commands
FROM MSrepl_commands cmds
INNER JOIN MSarticles a ON a.article_id = cmds.article_id
INNER JOIN MSrepl_transactions t ON cmds.xact_seqno = t.xact_seqno
—WHERE a.article = 'SuspectedTable'
GROUP BY a.article, CONVERT(SMALLDATETIME, t.entry_time)
HAVING COUNT(1) > 1000
ORDER BY CONVERT(SMALLDATETIME, t.entry_time) DESC;

Phil Grayson
Published Sep 2017
Latest Posts
Here’s what we've been up to recently.

xTEN is now Cyber Essentials Plus certified
At xTEN security is a priority. Recently taken over by the IASME consortium (as of 1 April 2020), the Cyber Essentials certification consists of a self-assessment of 5 basic security controls which is then verified by a qualified assessor.

Tracie Binks
Apr 2020