SQL Server の 変更の追跡 では、クリーンアップ プロセス で変更の内容を記録するサイドテーブル (syscommittab / change_tracking_<オブジェクト ID>) のクリーンアップが実行されます。
このクリーンアップはバックグラウンド タスクとして実行されるため、ユーザー側では実行されるクエリの制御ができないのですが、トレースフラグ TF8286 / 8287 を有効にすることでヒント句を追加することができます。
このトレースフラグを有効化することで、クリーンアップで実行されるクエリがどのように変化をするのかを確認してみました。
変更の追跡のクリーンアップで実行されるクエリ
トレースフラグを有効化していない場合、クリーンアップでは次のようなクエリが実行されます。
delete top(@batch_size) from sys.[change_tracking_1221579390] where sys_change_xdes_id in (select xdes_id from sys.syscommittab ssct where ssct.commit_ts <= @csn)
このクエリで実行された場合、使用される実行プランは次のようなパターンが使用されることがあります。
「change_tracking_<オブジェクト ID>」の削除に、「syscommittab」「change_tracking_<オブジェクト ID>」が使用されるのですが、アクセス方式についてはオプティマイザにまかせた形となるため、駆動表 / アクセス方式が安定していない状態となります。
これを強制的に補正するのが冒頭に記載したトレースフラグとなります。
- TF8286: FORCESEEK ヒント句を追加
- TF8287: FORCE ORDER ヒント句を追加
このようなヒント句を強制することができます。
次のクエリを実行して、両方のトレースフラグを有効化してみます。
DBCC TRACEON(8286, -1) DBCC TRACEON(8287, -1)
この場合は、クリーンアップのクエリが次のようなクエリとなります。
delete top(@batch_size) from sys.[change_tracking_2139154666] where sys_change_xdes_id in (select xdes_id from sys.syscommittab ssct with (forceseek) where ssct.commit_ts <= @csn) option (force order)
syscommittab への検索に対して Seek が強制され (with (forceseek))、テーブルアクセスの順序が固定化 (option (force order) されています。
「change_tracking_<オブジェクト ID>」へのアクセスについては、ヒント句の対象外となるため、このテーブルに対してのアクセスは Scan が使用されるケースがありますが、後の個所については追加されたヒント句でプランの制御が実行できています。
どの程度、期待したアクセスプランとなるかは検証が必要ですが、変更の追跡のクリーンアップの制御方法としてトレースフラグが利用できることを把握しておくのは重要ではないでしょうか。