SQL トランザクションレプリケーションの遅延計測のやり方

はじめに
トレーサートークンの作り方
トレーサートークンの削除
全トレーサートークンの遅延時間を出力
まとめ

はじめに

今回、仕事でこの件について調べたので、備考録として書きたいと思います。
SQLレプリケーションを行った時に、一番気になるのは、その同期スピードではないでしょうか？
一応、レプリケーションモニターから概要は見れますが、いまいち良くわからないですよね。
その場合、今回紹介する「トレーサートークン」機能が有用です。
マイクロソフトのページでは、以下に詳細が載せられています。

待機時間の計測と接続の検証 (トランザクション) - SQL Server

SQL Server Management Studio (SSMS)、Transact-SQL (T-SQL)、またはレプリケーション管理オブジェクト (RMO) でレプリケーションモニターを使用し、SQL Server でトランザクシ...

ページを見てもらったら分かるように、仕組みはいたって単純で、トークンと呼ばれる印をつけたデータがどのようにディストリビュータに渡り、サブスクライバーの渡るかを計測できます。

トレーサートークンの作り方

この操作はレプリケーションモニターからも出来ます。
ジョブなどで定期的に作成したい場合は、以下のように作ってあげると良いと思います。

DECLARE @tokenID AS INT

EXEC sp_posttracertoken   @publication      = 'PubFirst'
                        , @tracer_token_id  = @tokenID OUTPUT

SELECT @tokenID AS [TokenID]

※このスクリプトは、バプリケーション側で動かしてください。

トレーサートークンの削除

レプリケーションモニターには、なぜかこの機能がありません。
テスト後に綺麗に全部削除したい場合は、以下のようなスクリプトで削除する必要がありますね。

DECLARE @publication AS sysname;
SET @publication = N'DummyDB'; 

CREATE TABLE #tokens (tracer_id int, publisher_commit datetime);

INSERT #tokens (tracer_id, publisher_commit)
EXEC sys.sp_helptracertokens @publication;

select * from #tokens

declare @w_tracer_id int
declare @w_publisher_commit datetime
declare cur_tokens cursor for
    select tracer_id, publisher_commit
    from #tokens


open cur_tokens;

fetch next from cur_tokens
into @w_tracer_id,@w_publisher_commit;

while @@FETCH_STATUS = 0
begin

    exec sp_deletetracertokenhistory @publication , @w_tracer_id

    fetch next from cur_tokens
    into @w_tracer_id,@w_publisher_commit;
end

close cur_tokens;
DEALLOCATE cur_tokens;

--result
EXEC sys.sp_helptracertokens @publication;

drop table #tokens

※このスクリプトは、バプリケーション側で動かしてください。
※@publicationには、パブリケーション名を入れてください。

全トレーサートークンの遅延時間を出力

レプリケーションモニターでは１件ずつは見ることが出来ます。
一覧で見たいときは、めんどくさいですが、下記のようなスクリプトで出力してあげる必要があります。

DECLARE @publication AS sysname;
SET @publication = N'DummyDB'; 

CREATE TABLE #tokens (tracer_id int, publisher_commit datetime);
CREATE TABLE #tokenshistory (distributor_latency bigint,subscriber sysname,subscriber_db sysname,subscriber_latency bigint,overall_latency bigint);
CREATE TABLE #tokensresult (subscriber sysname,subscriber_db sysname,publisher_commit datetime,distributor_latency bigint,subscriber_latency bigint,overall_latency bigint);

INSERT #tokens (tracer_id, publisher_commit)
EXEC sys.sp_helptracertokens @publication;

select * from #tokens

declare @w_tracer_id int
declare @w_publisher_commit datetime
declare cur_tokens cursor for
    select tracer_id, publisher_commit
    from #tokens

open cur_tokens;

fetch next from cur_tokens
into @w_tracer_id,@w_publisher_commit;

while @@FETCH_STATUS = 0
begin
    INSERT #tokenshistory (distributor_latency,subscriber,subscriber_db,subscriber_latency,overall_latency)
    EXEC sys.sp_helptracertokenhistory @publication,@w_tracer_id

    insert #tokensresult
    select top 1 subscriber,subscriber_db,@w_publisher_commit,distributor_latency,subscriber_latency,overall_latency from #tokenshistory

    truncate table #tokenshistory

    fetch next from cur_tokens
    into @w_tracer_id,@w_publisher_commit;
end

close cur_tokens;
DEALLOCATE cur_tokens;

select * from #tokensresult

drop table #tokens
drop table #tokenshistory
drop table #tokensresult