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

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,

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: