How to Monitor Replication Throughput

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;