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; |
One thought on “Tracking replication throughput”