Technical Blog

Replication: How to Monitor Throughput

Identify issues with SQL Server replication by checking what commands are being passed through the distributor

Phil Grayson

Published

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;

Get Started With

Start Now
icon_bluestone98