Find highest latency in transactional replication in SQL Server 2012

Find highest latency in transactional replication in SQL Server 2012

Replication is one of the very handy and useful high availability features SQL Server has provided. It is very easy to setup but maintaining health and keeping eye on performance of replication is very crucial and mandatory.

We can find “MSTracer_History”, “MSDistribution_Agents” and “MSTracer_Tokens” tables into distribution database of replication and hence the query I am going to give you, would be used under distribution database only.

Let me give you brief introduction, from MSDN, of all three system tables of distribution database.

MSTracer_History: The MStracer_history table maintains a record of all tracer tokens that have been received at the Subscriber. This table is stored in the distribution database and is used by replication for performance monitoring.

MSDistribution_Agents: The MSdistribution_agents table has one row for each Distribution Agent running at the local Distributor. This table is stored in the distribution database.

MSTracer_Tokens:  The MStracer_tokens table maintains a record of tracer token records inserted into a publication. This table is stored in the distribution database and is used by replication for performance monitoring.

Now here is the query I use often to check the latency of replication.

[sourcecode language=”sql”]
SELECT
                SysServ.name AS SubscriberName,
                MSDA.subscriber_db AS SubscriberDatabase,
                MSDA.publication AS PublicationName,
                MAX(DATEDIFF(SS,publisher_commit,distributor_commit)) AS LatencyPublisherToDistributor,
MAX(DATEDIFF(SS, distributor_commit,subscriber_commit)) AS LatencyDistributorToSubscriber,
MAX(DATEDIFF(SS,publisher_commit,distributor_commit)
                + DATEDIFF(SS, distributor_commit,subscriber_commit)) AS TotalLatency
FROM
                MStracer_history MSTH
INNER JOIN
                MSdistribution_agents MSDA
ON
                MSTH.agent_id = MSDA.id
INNER JOIN
                sys.servers SysServ
ON
                MSDA.subscriber_id = SysServ.server_id
INNER JOIN
                MStracer_tokens
ON
                MSTH.parent_Tracer_id=MStracer_tokens.tracer_id
WHERE
                subscriber_commit >DATEADD(hh,-1,GETDATE())
GROUP BY
                SysServ.name,
                MSDA.subscriber_db,
                MSDA.publication
GO[/sourcecode]

You can modify above given query for your customize need. I have filtered (in WHERE clause) records for last one hour so it will give you highest latency of last one hour. You can even filter records on TotalLatency field, If you want to see resultset only if it cross certain seconds/minutes while transferring records from publisher to subscriber.

Do let me know what technique do you use to keep your eye on replication health?

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.