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?
Reference: Ritesh Shah
Sharing IT tips at “Quick Learn“
Note: Microsoft Books online is a default reference of all articles.