SE の雑記

SQL Server では、変更の追跡 (Change Tracking: CT) の機能を使用することで、変更があった行のトラッキングを行うことができます。

CT では行の変更は次のテーブル (サイドテーブル) で管理が行われています。

• sys.syscommittab
• sys.change_tracking_<Object Id>

これらのテーブルは クリーンアップ により、定期的にデータの削除が行われます。

クリーンアップは、上述のリンクと Change Tracking の自動クリーンアップに関する問題のトラブルシューティング から挙動を確認することができます。

更新頻度が高いテーブルでは、サイドテーブルに格納されている行数が膨大になり、これらのクリーンアップのドキュメントに記載されている内容だけではトラブルシューティングが難しいことがあります。

本投稿では、変更の追跡のクリーンアップについてドキュメント外の情報について、参考情報をまとめておきたいと思います。

クリーンアップの挙動を確認する際に取得する情報

クリーンアップの挙動を確認する際には冒頭に記載したドキュメントの情報以外に、次の情報を確認することでクリーンアップの挙動をさらに理解することができます。

• 拡張イベント
• クリーンアップのトランザクション名のトランザクション
• クエリストア

拡張イベント

変更の追跡のクリーンアップは、SQL Server 2025 の変更 の次の記載のとおり、30 分間隔でバックグラウンドタスクとして起動されています。

この方法では、自動クリーンアップ スレッドは 30 分ごとにウェイクアップし、変更が追跡されたすべてのデータベースとテーブルをフェッチし、構成された保持期間に基づいて安全なクリーンアップ ポイントを検索し、すべてのテーブルをループして、対応するサイド テーブルからデータを消去します。

バックグラウンドタスクとして実行されているため、クエリ関連の DMV からでは実行中の挙動を確認することが難しいのですが、クリーンアップ用の拡張イベントとして、次のイベントが提供されています。

• syscommittab_cleanup_alert
• syscommittab_cleanup
• change_tracking_shallow_cleanup
• change_tracking_cleanup
• change_tracking_adaptive_cleanup

これらの拡張イベントを取得しておくことで、クリーンアップが発生した場合にイベントがトリガーされクリーンアップの実行状況を確認することができます。

クリーンアップのトランザクション名のトランザクション

変更の追跡のクリーンアップですが、トランザクション名としては「CtCleanupTblDelete」として実行が行われています。

このトランザクション名を利用して、次のようなクエリで情報を取得することが可能です。

SELECT * FROM sys.dm_tran_active_transactions
WHERE name = 'CtCleanupTblDelete'

私は、クリーンアップのトランザクションで取得されているロック数の取得で活用したのですが、クリーンアップのトランザクションでどのような挙動が発生しているかを確認する場合、トランザクション名を起点にして調査を行うことが有効なケースがありますので「CtCleanupTblDelete というトランザクションのバックグラウンドタスクでクリーンアップが実行されている」というのは覚えておくとよいかと思います。

クエリストア

クリーンアップが実行されると「sys.syscommittab」「sys.change_tracking_<Object Id>」のテーブルの DELETE が行われますが、change_tracking のテーブルの削除についてはクエリストアで確認できる確度が高くなっているように思えました。

クエリストアでサイドテーブルのテーブル名を検索してみると次のような DELETE 文を取得することができます。

バックグラウンドタスクで実行されているクエリため、クエリを細かに制御することはできないのですが、現在どのようなクエリでクリーンアップが実行されているかは、クエリストアから確認を行うことができます。

クリーンアップのクエリの補正に関連する情報

変更の追跡のクリーンアップはバックグラウンドタスクとして実行されるため、クエリの補正を柔軟に実行することはできないのですが、補正が必要となった場合にいくつかの対応案があります。

• トレースフラグ
• クエリストアのプランの強制
• ロックメモリの解放

トレースフラグ

クリーンアップは「TOP 4999」を対象としてデータの削除が行われるため、5,000 のロック数によるロックエスカレーション を防ごうとしているクエリにはなっているのですが、実行プランによっては大量のロックが取得され、クリーンアップによりロックエスカレーションが発生することがあります。

変更の追跡はトリガーのような形で、ベーステーブルに対して変更は発生した場合に同期的にサイドテーブルに書き込みを行うという処理となっている関係で、サイドテーブルでロックエスカレーションが発生すると、ベーステーブルの更新の同時実行性も影響が出ます。

そのため、変更の追跡で大量のデータがトラッキングされる可能性がある場合、トレースフラグ「TF1224」を設定し、ロック数によるロックエスカレーションの発生を抑制させるこで、同時実行性の低下を防ぐことができる可能性があります。

また、SQL Server 2019 CU19 / SQL Server 2022 CU1 以降では、「TF8285」「TF8286」を使用することで、クリーンアップのクエリにヒントを強制することができるようになりました。

これにより、クリーンアップのクエリに「FORCESEEK」「FORCE ORDER」のヒントを強制することができるようになります。

実際に実行されるクエリが次の内容となりますが、TF を設定することでクエリ内に強制的にヒント句が埋め込まれるようになります。

delete top(@batch_size) from sys.[change_tracking_1221579390] 
where sys_change_xdes_id in (select xdes_id from sys.syscommittab ssct with (forceseek) 
where ssct.commit_ts <= @csn) option (force order)

ヒント句を強制することで必ずしも効率が以前されるということではなく、悪化するケースもありますので、実行されているワークロードで効果があるかはクエリストアと組み合わせながら計測しながら利用有無を判断する必要があります。

クエリストアのプランの強制

上述のとおり、クリーンアップで「sys.change_tracking_<Object Id>」の削除で使用されたクエリはクエリストアに記録され、記録されたクエリは、クエリストアのプランの強制を行うことができます。

クリーンアップが実行される際には次の実行プランのようにサイドテーブルの検索が Clustered Index Scan となるケースがあります。

テーブルの結合順 / syscommittab の検索方法については、TF8285 / TF8286 で制御を行うことができますが、残念ながら「sys.change_tracking_<Object Id>」の検索方法については制御を行うことができません。(DELETE に対しての検索方法については固定化できないかと)

TOP 句が指定されているため、Scan が発生しても少量のデータの読み取りで済むケースもあるのですが、大量のデータ検索が行われ、これに伴い取得されるロック数が増大するケースがあります。

ロックエスカレーションを禁止している場合は、取得されるロック数が増えロックメモリが他のメモリを圧迫することがあります。

このような場合は、Seek でサイドテーブルの検索が行われているプランを強制することで補正を行うことができます。

ロックメモリの解放

ロックエスカレーションを禁止 / サイドテーブルに Scan が行われた場合、ロックメモリの肥大化につながることがあります。

SQL Server 2025 CU5 以降であれば、サーバー構成: 最大ロック マネージャー キャッシュ メモリ (%) の設定を使用することで、ロックの確保を維持するコミット済みメモリの合計を成業することができますがそれ以外のバージョンでは、バッファキャッシュの 60% 程度までロックのコミット済みメモリを確保した状態を維持することがあります。

この挙動については ロックのエスカレーションのしきい値 にも記載されています。

ロックメモリが肥大化した場合、次のようなクエリでロックメモリをピンポイントで開放することができます。

DBCC FREESYSTEMCACHE('Lock Manager : Node 0')

ロックメモリは NUMA ノード単位でメモリ管理がされているため、複数のノードを持つ環境では、各ノードに対してロックメモリの解放を実行する必要がありますが、このクエリを使用することでロックメモリを強制的に解放することができます。

変更の追跡のクリーンアップ処理について挙動を確認する場合には、これらの情報も参考にすると、挙動についての理解が深まるのではないでしょうか。