Tag Archives: MSTracer_History

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.

 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

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.