SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

変更の追跡 (Change Tracking) のクリーンアップについて

leave a comment

SQL Server では、変更の追跡 (Change Tracking) を使用することで、変更されたデータの主キーの項目を追跡することができます。

変更の追跡は、データベースレベルで保持期間の設定を実施することができます。

image

データの保持期間を設定することができ、「自動クリーンアップ」の設定を行うことができ、クリーンアップの挙動については、変更の追跡のクリーンアップ に記載されています。

クリーンアップについて調べる機会があったので、調べた内容をまとめておこうと思います。

最初に結論を書いておくと、

  • 変更の追跡のクリーンアップを手動で制御することはできない
  • 自動クリーンアップが有効になっていないと、変更の追跡のデータを削除できない

となると思います。

変更の追跡の詳細な動作については、次の記事を参照するとよいと思います。

手動でクリーンアップを実施したい場合については、次のドキュメントの内容が参考になります。(トラブルシューティングを実施する際に必要となる情報が多数記載されています)

クリーンアップで削除されるテーブル

変更の追跡のクリーンアップですが、次のテーブルのデータから保有期間を過ぎたデータを削除するために実行されています。

  • sys.syscommittab
    • トランザクションシーケンス番号とコミット時間を管理するテーブル
      • データベース単位に一つのみ存在し、変更の追跡が有効化されている全テーブルの情報を管理
      • クリーンアップ対象のデータを判別するために必要
      • sys.change_tracking_[オブジェクト ID] のデータはこのテーブルの内容を基に削除される
    • チェックポイントが発生したタイミングでテーブルにデータがフラッシュされる
  • sys.change_tracking_[オブジェクト ID]
    • 変更の追跡を有効にしたテーブル単位に作成される
    • CHANGETABLE 関数で取得されているデータの実体が含まれている

どちらのテーブルも DAC (専用管理者接続) でのみ情報の確認ができるテーブルであり、テーブル内の内容については、DAC で接続しているセッションでのみ確認することができます。

sys.syscommittab については、sys.dm_tran_commit_table という DMV も提供されていますので、DAC ではなく、こちらから情報を確認することができるケースもあると思います。

各テーブルのデータ件数については、通常のセッションからも確認できますので、データがどの程度存在しているかについては、次のクエリで確認することができます。

SELECT 
	object_name(p.object_id) as name, p.index_id, p.rows 
FROM 
	sys.partitions  AS p
	INNER JOIN sys.objects AS o
		ON o.object_id = p.object_id
WHERE 
	(p.object_id = object_id('sys.syscommittab') OR o.name LIKE 'change[_]tracking[_]%')
	AND p.index_id = 1

image

クリーンアップが実行されたタイミングでこれらのテーブルのデータが DELETE されるため、このテーブルにデータが蓄積され続け、一定量まで削除されていないようであれば、クリーンアップが間に合っていない可能性を考慮する必要があります。

変更の追跡の自動クリーンアップが実行されるタイミング

変更の追跡の自動クリーンアップは実行するタイミングを細かく調整することはできず、「30 分に 1 回」バックグラウンドタスクとして、自動的に実行されます。

実行タイミングを制御することはできないようですので、自動クリーンアップが実行されるタイミングを任意のタイミングに変更することはできません。

変更の追跡の自動クリーンアップついては「change_tracking_cleanup」という拡張イベントでイベントを確認することがでます。

image

変更の追跡の自動クリーンアップで実行されている処理については、この拡張イベントで様々な情報を確認することができ、自動クリーンアップの調査を実施するためには、この拡張イベントの取得が必須となると思います。

ただし、前述のとおり、自動クリーンアップは 30 分に 1 回の頻度で実行される処理となりますので、イベントのトレースを行うためには、拡張イベントを有効化してから数 10 分待機する必要があります。

削除に使用されるストアドプロシージャ

30 分に 1 回に自動的にバックグラウンドタスクが実行され、上述のテーブルを削除するため、次のようなストアドプロシージャが実行されることでテーブルのデータの削除が行われているようです。

  • sys.sp_changetracking_time_to_csn
  • sys.sp_flush_commit_table
  • sys.sp_flush_commit_table_on_demand
    • sys.syscommittab を削除するためのストアドプロシージャ
    • 最大 : 4,999 件削除
  • sys.sp_changetracking_remove_tran
    • change_tracking_[オブジェクト ID] を削除するためのストアドプロシージャ
    • 最大 : 5,000 件削除
    • ストアドプロシージャ内で、change_tracking_[オブジェクト ID] 単位に DELETE するためのクエリをアドホッククエリとして実行

関連するクエリの取得については、自動クリーンアップが実行されたタイミングでクエリのキャッシュから取得するとよいかと。

select * from sys.dm_exec_query_stats
outer apply sys.dm_exec_sql_text(sql_handle)
outer apply sys.dm_exec_query_plan(plan_handle)
where text like '%change_%'

select * from sys.dm_exec_procedure_stats
outer apply sys.dm_exec_sql_text(sql_handle)
outer apply sys.dm_exec_query_plan(plan_handle)
where text like '%change_%'

「sys.sp_flush_commit_table_on_demand」「sys.sp_changetracking_remove_tran」では 5,000 件近くのデータが削除されます。

「sys.syscommittab」「change_tracking_[オブジェクト ID] 」については、ロック数の閾値によるロックエスカレーション が発生するタイミングがあるようです。

実際にロックエスカレーションが発生した状態がこちらです。

image

「change_tracking_[オブジェクト ID] であれば次のようなクエリで削除が行われるのですが、batch_size については「5,000」が上限として使用されているようです。

create procedure sys.sp_changetracking_remove_tran
(
    @objid int
    ,@csn bigint
	,@batch_size int
    ,@stat_value bigint OUTPUT
)
as
begin
	set nocount on
	
	-- executed from within an engine transaction, no explicit one required

	declare @stmt nvarchar(1000)
	
	select @stat_value = 0

	if object_name(@objid) is not null and @csn is not null
    begin
        if @csn is not null 
        begin
			begin try
				select @stmt = N'SET LOCK_TIMEOUT 30000;
							delete top(@batch_size) from sys.' + quotename(object_name(@objid)) + ' where sys_change_xdes_id in 
								(select xdes_id from sys.syscommittab ssct where
	               				ssct.commit_ts <= @csn)'
				exec sp_executesql @stmt = @stmt,
					@params = N'@csn bigint, @batch_size int',
					@csn = @csn,
					@batch_size = @batch_size

				select @stat_value = @@rowcount	
			end try
			begin catch
				declare @error int
				select @error = ERROR_NUMBER()
				-- If the lock request timeout exception is thrown, set the stat_value to -1 which will let the caller to proceed
				-- with deletion of other tables
				if (@error = 1222)
					select @stat_value = -1
				else
					throw
			end catch
        end
	end
end

削除時に使用される実行プランは次のようなものになります。

TOP 5000 でリミットをかけているのですが、まれに5,000 件ちょっとのロックを取得してロックエスカレーションするらしいです…。

image

ロックエスカレーションの抑制

両テーブルともにシステムテーブルであり、ロックエスカレーションをテーブル (インデックス) 単位で制御することはできません。

ろロック数の閾値によるロックエスカレーションが同時実行性に影響を与えるのであれば、「TF1224」を設定し、ロック数に基づくロックエスカレーションの停止を検討する必要があるかもしれません。

手動クリーンアップ用のストアドプロシージャ

手動でクリーンアップする際のストアドプロシージャとしては、次のようなストアドプロシージャを使用することができます。

  • sys.sp_flush_commit_table_on_demand
    • sys.syscommittab 削除用
  • dbo.sp_flush_CT_internal_table_on_demand
    • change_tracking_[オブジェクト ID] 削除用
    • SQL Server 2012 SP4 / 2014 SP3 / SP1 以降で使用可能

dbo.sp_flush_CT_internal_table_on_demand については、次のドキュメントで情報が公開されています。

手動クリーンアップ用のストアドプロシージャですが「任意のタイミングでストアドを実行することでテーブルのクリーンアップを実行する」というものではなく、削除可能なデータ (厳密には削除可能なトランザクションシーケンス番号) について、削除を行うというものです。

そのため「どのデータまで削除が実行できるか」のウォーターマークが現在どの値に設定されているかが重要となります。

ウォーターマークの設定は、自動クリーンアップが実行される初期処理として設定が行われているようで、手動クリーンアップ用のストアドプロシージャは「任意のタイミングでクリーンアップを実行する」のではなく、「自動クリーンアップで設定されたウォーターマークまでデータの削除を行う」処理となるようで、どこまで削除できるかはウォーターマークの設定状況次第となるようです。

そのため、保有期間が過ぎているデータが存在していても、ウォーターマークの設定が更新されていない場合は、ストアドプロシージャを実行してもデータの削除は行われないようです。

削除対象のデータが存在していてもウォーターマークが適切に更新されていない場合は、ストアドプロシージャを実行しても削除対象のデータは存在しないものとして認識が行われ、データの削除が行われません。

EXEC sys.sp_flush_commit_table_on_demand 
EXEC sp_flush_CT_internal_table_on_demand @TableToClean = 'CT_01'

image

手動クリーンアップ用のストアドプロシージャについては、「自動クリーンアップによって削除しきれなかったデータを、任意のタイミングで再度削除を行う」というような処理を実現するためのものとなるのではと考えています。

クリーンアップ用のウォーターマークの設定タイミング

クリーンアップ用のウォーターマークの設定ですが、「自動クリーンアップの処理」の初期処理として実行されているようで、ウォーターマークを任意の値に設定することは通常の方法では実施することはできないようです。

削除可能なデータについては、sys.change_tracking_tables で確認をすることができます。

min_valid_version / cleanup_version がクリーンアップ用のウォーターマークとして使用されているようで、変更の追跡のクリーンアップで削除可能なデータについては、この値の範囲までとなるようです。

image

この値は「自動クリーンアップ」の初期タイミングで更新 (UpdateRetention / UpdateInvalidCleanup) されているようで、この値が更新されないとストアドプロシージャを実行してもデータの削除は行われないようです。

自動クリーンアップを無効にした状態だと、ウォーターマークの値が更新されるタイミングがないため、ストアドプロシージャを実行しても変更の追跡のデータを削除することはできないと思います。

変更の追跡で取得されたデータを削除するためには、ウォーターマークの設定が必要不可欠となり、自動クリーンアップが有効化され、自動クリーンアップの処理が行われる必要があるので、自動クリーンアップの有効化が必要となるのではないでしょうか。

ウォーターマークの管理ですが、KB4538365 – FIX: Change Tracking cleanup does not work when invalid cleanup and hardened cleanup version are negative in SQL Server 2017 and 2019 の情報からも確認できるようです。

select * from sys.sysobjvalues where valclass = 7 and objid = 1003 -- invalid
select * from sys.sysobjvalues where valclass = 7 and objid = 1004 -- Hardened

sys.objvalues の valclass = 7 の objid が 1003 / 1004 のレコードが、ウォーターマークとして使用されているようです

1003 については、クリーンアップ処理の初期で、「UpdateInvalidCleanup」によって、情報が書き換えられるようで、無効となる syscommittab のタイムスタンプが設定されているようです。

image

クリーンアップの最後で「UpdateHardenedCleanup」が実行され、 実際に削除が完了したレコードを示すタイムスタンプを格納している、1004 のレコードが更新されることで、ウォーターマークの情報の更新が完了するようです。

image

Change Tracking Cleanup Deep Dive

この方法は無理やりクリーンアップを実行していますので運用では絶対に使用しないでください。あくまでも動作を確認するために実施した方法です。SQL Server の操作を把握できていることを前提で記載していますので、作業の細かな説明は省略しています。

Change Tracking Cleanup Deep Dive として、自動クリーンアップを無効にした状態で、クリーンアップを自分で実行してみたいと思います。

システムテーブルのデータを確認するので、DAC で接続しての作業が必要となります。(sys.syscommittab の代替として、sys.dm_tran_commit_table を使用すればよい個所もありますが、Water Mark の更新で DAC が必要となるので、DAC で作業しています)

ウォーターマークのタイムスタンプの取得

ウォーターマークとして、どのタイムスタンプを設定するかを取得します。

-- システムテーブルの確認
SELECT
	*,
	'0x' + SUBSTRING(ts,7,2) + SUBSTRING(ts,5,2) + SUBSTRING(ts,3,2) AS warter_mark
FROM
(
SELECT TOP 10 
	*, 
	CONVERT(varchar(8), CONVERT(varbinary(3), commit_ts,1), 1) AS ts
FROM
	sys.syscommittab 
ORDER BY 
	commit_ts DESC
) AS T

image

ウォーターマークを変更するページ ID を取得

ウォーターマークは、システムテーブルの「sys.sysobjvalues」に格納されており、システムテーブルのデータはシングルユーザーモードで接続をしてからでないと変更できないはずです。

SQL Server のプロセスを再起動するのが面倒なので、直接ページのデータを書き換えるために、対象ページのページ ID を取得します。

SELECT * FROM sys.change_tracking_tables

select * from sys.sysobjvalues 
cross apply sys.fn_PhysLocCracker(%%physloc%%)
where value = (
	SELECT min_valid_version FROM sys.change_tracking_tables 
	WHERE object_id = OBJECT_ID('CT_01')
)

今回は上記のクエリを実行して、128 ページに格納されていることが確認できましたので、変更するページは 128 ページをターゲットとしていきます。

対象データのオフセットを確認

ページ ID が取得できましたので、該当データのオフセットを確認します。

今回は、Page ID 128 の Slot Id 2, 3 となっていますので、このデータのオフセットを確認にします。

DBCC TRACEON(3604)
DBCC PAGE('changetracking', 1, 128,1)

image

Slot Id 2, 3 のオフセット値が取得できましたので、このデータを直接確認します。

ページの変更

オフセットと変更対象のデータが確認できましたので、直接ページのデータを前段で取得したウォーターマークの値に書き換えます。

書き換える場所は取得したオフセットから 26 バイトずらした場所となりますのでそのデータを書き換えます。

-- オフセット+26
DBCC WRITEPAGE('changetracking', 1, 128,6076, 3 , 0x17F827, 0)
DBCC WRITEPAGE('changetracking', 1, 128,6110, 3 , 0x17F827, 0)

DBCC WRITEPAGE を使用して、直接ウォーターマークのデータの書き換えが完了しました。

image

ストアドプロシージャの実行

これでウォーターマークの変更されたので削除ができるようになりますので、次の順番でストアドプロシージャを実行します。

EXEC dbo.sp_flush_CT_internal_table_on_demand @TableToClean = 'CT_01'
EXEC sys.sp_flush_commit_table_on_demand

ウォータマークが適切に変更されていると、次のようなメッセージが出力され変更の追跡の削除が実行されるはずです。

image

クリーンアップはこのような処理の実施が必要 / 実行されていると考えると、処理の内容を把握しやすくなるのではないでしょうか。

手動クリーンアップ実行時の重要な情報

sp_flush_commit_table_on_demand は sys.committab のデータを削除するものとなるのですが、このストアド (sproc) の実行についてはかなり癖があるようです。

手動クリーンアップについては、Change Tracking (aka Syscommittab) Issues and Cleanup – Part 1 を一読しておくとよいかと思います。

sp_flush_commit_table_on_demand は次のような形式で実行を行うことができます。(

exec sys.sp_flush_commit_table_on_demand @numrows = 100000
GO

実行結果がこちらです。

image

ストアドの中身については、次のようなクエリで確認を行うことができます。

sp_helptext 'sys.sp_flush_commit_table_on_demand'

SQL Server のバージョンによって内容は少し異なるのですが、sys.syscommittab の削除対象となる commit_ts については、次のようなクエリで対象を決定しています。

-- checking for change tracking side table watermark
DECLARE @deleted_rowcount INT
SET @cleanup_ts = change_tracking_hardened_cleanup_version ()
RAISERROR(22866,0,1,35502,@cleanup_ts)

-- checking for safe cleanup watermark
DECLARE @cleanup_version BIGINT
set @cleanup_version = safe_cleanup_version ()
RAISERROR(22866,0,1,35503,@cleanup_version)

IF @cleanup_ts > @cleanup_version
SET @cleanup_ts = @cleanup_version

～省略～

DELETE TOP(@batch_size) sys.syscommittab WHERE commit_ts < @cleanup_ts

「change_tracking_hardened_cleanup_version()」と「safe_cleanup_version()」で取得した、commit_ts を比較して、小さい値の commit_ts を削除対象として使用するというロジックとなっています。

「change_tracking_hardened_cleanup_version()」で取得されているのは、「sys.sysobjvalues」の「valclass = 7 and objid = 1004」となっているようなのですが、「safe_cleanup_version()」で取得される値が、どこから取得されているのかが不明でした。(1003 のレコードではないようでした)

Change Tracking (aka Syscommittab) Issues and Cleanup – Part 1 でも触れられているのですが、「safe_cleanup_version()」で取得される値が「0」となることがあるようで、0 になった場合は、上述の比較ロジックで 0 が commit_ts との比較で採用され「commit_ts < 0」で DELETE 対象が判断されるため、削除が行われません。

この関数の仕様は公開が行われていないため、現状 (2021/11/1 時点でも) の解決方法としては、0 以外の結果が返ってくるまで何回も実行してみるしかないようです…。

SQL Server 2016 SP3または、SQL Server 2017 CU26 で dbo.MSchange_tracking_history というテーブルが追加 されており、safe_cleanup_version が 0 を返したかどうかについては、このテーブルから確認することができるのですが、どのような場合に 0 となるのかがわからないのですよね。

image

Share

Written by Masayuki.Ozawa

9月 26th, 2021 at 3:34 pm

Posted in SQL Server

Tagged with

«
»

Leave a Reply