Tracer tokens

Tracer tokens are a feature in SQL Server replication that allow you to monitor the latency or delay between the Publisher and Subscriber databases. When you insert a tracer token into the replication stream, it serves as a marker that can be tracked as it moves through the replication process. The primary purpose of tracer tokens is to measure the time it takes for changes made at the Publisher to be replicated and applied at the Subscriber.

Here's how tracer tokens work in SQL Server replication:

1.    Insertion of Tracer Tokens:

·  You can manually insert tracer tokens using SQL Server Management Studio (SSMS) or Transact-SQL commands. When you insert a tracer token, it is added to the replication stream alongside regular replicated transactions.

·  Tracer tokens are typically inserted using the Replication Monitor or by executing the sp_posttracertoken stored procedure.

2.    Movement through Replication:

·  Once a tracer token is inserted, it follows the same replication path as regular transactions. For example, in transactional replication, tracer tokens are picked up by the Log Reader Agent, transmitted to the Distribution database, and delivered to the Subscriber by the Distribution Agent.

·  In merge replication, tracer tokens are handled by the Merge Agent, which manages bidirectional synchronization between the Publisher and Subscriber.

3.    Tracking Latency:

·  As tracer tokens move through the replication process, you can monitor their progress and track the time it takes for them to be replicated and applied at the Subscriber.

·  The Replication Monitor provides visibility into tracer token status, including the time elapsed since insertion and the time taken for replication.

4.    Analyzing Latency and Performance:

·  By analyzing tracer token data, you can assess the latency or delay in replication. This information is valuable for identifying bottlenecks, diagnosing performance issues, and optimizing your replication setup.

·  Tracer tokens help you ensure that replication latency meets your desired service level agreements (SLAs) and performance requirements.

5.    Usage in Troubleshooting:

·  Tracer tokens are often used in troubleshooting scenarios to pinpoint replication delays or failures. They can help isolate issues such as network latency, agent processing delays, or conflicts that may impact replication performance.

 TSQL Commands to generate and track Tracer Tokens

Tracer Tokens are a great way to monitor the flow of transactions from the Publisher to the Subscriber.  These can be generated and tracked from SQL Server Replication Monitor or via TSQL commands.  This post includes some of the common TSQL commands used to generate and track Tracer Tokens.  Details can be found on SQL Server Books Online.

–Tracer Token Tracking Tables

USE Distribution

SELECT * FROM MStracer_tokens

SELECT * FROM MStracer_history

 

–End to End time

SELECT publication_id, agent_id,

       t.publisher_commit, t.distributor_commit, h.subscriber_commit

FROM MStracer_tokens t

JOIN MStracer_history h

ON t.tracer_id = h.parent_tracer_id

 

–Let’s see that in seconds

SELECT publication_id, agent_id, t.publisher_commit,

       Datediff(s,t.publisher_commit,t.distributor_commit) as ‘Time To Dist (sec)’,

       Datediff(s,t.distributor_commit,h.subscriber_commit) as ‘Time To Sub (sec)’

FROM MStracer_tokens t

JOIN MStracer_history h

ON t.tracer_id = h.parent_tracer_id

 

publication_id agent_id    publisher_commit        Time To Dist (sec) Time To Sub (sec)

————– ———– ———————– —————— —————–

1              3           2010–01–23 14:25:33.503 2                  39

1              3           2010–01–23 14:29:46.180 2                  38

1              3           2010–01–23 20:13:45.170 2                  205

1              3           2010–01–23 21:09:33.953 1                  4

1              3           2010–01–23 21:17:44.940 3                  5

1              3           2010–01–23 13:10:15.197 1                  2433

1              3           2010–01–23 13:28:16.373 2                  1368

 

—Find worst performing subscribers.

SELECT

convert(varchar(10),agent_id) as ‘agent id’,

max(Datediff(s,distributor_commit,subscriber_commit)) as ‘MAXTime To Sub (sec)’,

avg(Datediff(s,distributor_commit,subscriber_commit)) as ‘AVG Time To Sub (sec)’

FROM MStracer_tokens t

JOIN MStracer_history h

ON t.tracer_id = h.parent_tracer_id

group by agent_id

order by 2 desc

 

agent id   MAXTime To Sub (sec) AVG Time To Sub (sec)

———- ——————– ———————

3          2433                 211

5          262                  262

 

–Who is Agent 3?

SELECT name, publication, subscriber_id,subscriber_db

FROM distribution.dbo.MSdistribution_agents

 

       Sample data:

       CHRISSKACER–AdventureWorksLT–TranProducts–CHRISSKACER–3

       TranProducts

       AdventureWorks_TranSub1

 

–View Job command line to get Subscriber Name

SELECT sjs.step_name, sjs.command from msdb.dbo.sysjobsteps sjs

join msdb.dbo.sysjobs sj

on sj.job_id = sjs.job_id

Where name = ‘CHRISSKACER-AdventureWorksLT-TranProducts-CHRISSKACER-3’

 

–Look for -Subscriber [CHRISSK1\SQL2K8] property which identifies the server

Technorati Tags: Tracer Token.

— When latency is observed, use commands below to

—     insert a new tracer token in the publication database

—     every 1 to 5 minutes.

WHILE (1=1)

BEGIN

       EXEC sys.sp_posttracertoken

       @publication = ‘TranProducts’

       — Wait 1 minute

       WAITFOR DELAY ’00:01:00′

END

 

sp_helptracertokens is used in transactional replication.

sp_helptracertokens is used to obtain tracer token IDs when executing sp_helptracertokenhistory (Transact-SQL).

DECLARE @publication AS sysname;

DECLARE @tokenID AS int;

SET @publication = N'AdvWorksProductTran';

 

USE [AdventureWorks2022]

-- Insert a new tracer token in the publication database.

EXEC sys.sp_posttracertoken

  @publication = @publication,

  @tracer_token_id = @tokenID OUTPUT;

SELECT 'The ID of the new tracer token is ''' +

    CONVERT(varchar,@tokenID) + '''.'

GO

-- Wait 10 seconds for the token to make it to the Subscriber.

WAITFOR DELAY '00:00:10';

GO

-- Get latency information for the last inserted token.

DECLARE @publication AS sysname;

DECLARE @tokenID AS int;

SET @publication = N'AdvWorksProductTran';

CREATE TABLE #tokens (tracer_id int, publisher_commit datetime)

-- Return tracer token information to a temp table.

INSERT #tokens (tracer_id, publisher_commit)

EXEC sys.sp_helptracertokens @publication = @publication;

SET @tokenID = (SELECT TOP 1 tracer_id FROM #tokens

ORDER BY publisher_commit DESC)

DROP TABLE #tokens

-- Get history for the tracer token.

EXEC sys.sp_helptracertokenhistory

  @publication = @publication,

  @tracer_id = @tokenID;

GO