Transactional Changes in SQL Server 2005 (1): Using Tracer Tokens to Monitor Latency

Introduction

Transactional replication in SQL Server 2005 has a tracer token functionality which gives us a simple method of measuring latency in transactional replication. Basically, a token is written to the transaction log of the publisher. This is treated as a normal replicated command, and passes from the transaction log to the distribution database and then is 'run' by the distribution agent. In actual fact there is nothing really to apply at the subscriber but nevertheless, this technique allows accurate determination of Publisher -> Distributor -> Subscriber time latencies.

To take advantage of this useful functionality, the Publisher, Distributor and Subscriber must be SQL 2005 for pull subscriptions, while for push, the subscriber may be SQL 2000, although only Publisher -> Distributor latencies will be measured. There are also a number of other minor caveats listed in BOL.

Implementation

(a) via the GUI

To activate the tracer token functionality, enter the replication monitor graphical interface and select the publication in the left pane (PubDocs in the case below). There are 3 tabs on the RHS. The first lists the distribution agents, while the last lists the snapshot and logreader agents. The middle one gives access to the tracer token functionality. To add a tracer token, select 'Insert New Tracer...'. It'll then be listed in the grid, and initially the 'Publisher to Distributor' and 'Distributor to Subscriber' sections will indicate 'Pending', as the tracer token has only been added to the transaction log and hasn't yet entered the replication pathways. The grid is set to refresh automatically every 10 seconds, although my experience is that the tracer token may incorrectly remain in the pended state after a 'refresh' and manually initiating a refresh from the left pane will correctly update the state.

(b) via TSQL

To do this manually, the script would be:

DECLARE @myTokenID AS int
EXEC sp_posttracertoken
@publication = 'pubdocs',
@tracer_token_id = @myTokenID OUTPUT;
select @myTokenID

Note that this is slightly different to the BOL script. The correct datatype for the @tracer_id argument is int and not smallint as listed, and the BOL definition of sp_posttracertoken incorrectly lists the first argument only. The second one is actually optional but is very useful if you want to check on the resulting latencies for this particular token. So,

EXEC sp_helptracertokenhistory
@publication = 'pubdocs',
@tracer_id = @myTokenID

returns subscriber_latency and overall_latency, and although publisher -> distributer latency is not listed it is simply derived (overall_latency - subscriber_latency).

To see this and all the other tracer tokens, you can use:

select * from distribution..MStracer_tokens

They'll be listed as numbers like -214748364 and resulting latencies. These same values are visible using the old-fashioned method of sp_browsereplcmds where the type = 1073741871 and the command returned is the actual token - in this case -2147483647.

BTW, if you're really interested and want to see exactly how this all is processed, use this query to follow the MS TSQL:

select object_definition(object_id('sys.sp_MShelptracertokenhistory'))