SQL Server: 変更の追跡に関する注意点まとめ

背景

変更の追跡を本番環境で運用して1年以上たったので、導入から運用までで遭遇したトラブルと、どのように解決してきたかについてまとめます。

変更の追跡とは

テーブル単位で設定する。
設定すると、どの行が挿入、更新、削除されたかを追うことができる。

設定時の挙動の確認

use test
--1
ALTER DATABASE TEST 
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  

CREATE TABLE [dbo].[bcp_in](
    [C1] [nvarchar](30) NOT NULL,
    [C2] [int] NULL,
    [C3] [int] NULL,
    [C4] [nvarchar](max) NULL,
    [C5] [datetime] NULL,
 CONSTRAINT [PK_bcp_in] PRIMARY KEY CLUSTERED 
(
    [C1] ASC
))

--2
ALTER TABLE bcp_in  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON) 

--3
truncate table bcp_in
CHECKPOINT

--4
INSERT INTO bcp_in VALUES(SUBSTRING(CAST(NEWID() AS varchar(36)),1,20), 1,1,1,GETDATE())
select * from sys.fn_dblog(NULL, NULL)

トランザクションログの中身。
テーブルへのINSERTと変更の追跡用テーブルへのINSERTが同一のトランザクションIDで行われていることがわかる。
したがって、同期的な処理となるため、1トランザクションあたりの実行時間が伸びる。

変更情報の取得

--設定を確認
select top 100 object_name(object_id) as table_name, * from sys.change_tracking_tables

→is_track_columns_updated_onが1になっており、行の更新まで追跡可能な状態

select
    *
from
     changetable(changes bcp_in, 0) as c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts
order by tc.commit_ts

「I」＝INSERTの履歴が格納される。DELETEしたら「D」
UPDATEは「U」

どのカラムがUPDATEされたのかの判別には「change_tracking_is_column_in_mask」を使う

update top (1) bcp_in set c2 = 2 where c2 = 1

select
    (case when sys_change_columns is null then 0 else change_tracking_is_column_in_mask(column_id , sys_change_columns) end) as is_updated
    ,*
from
     changetable(changes bcp_in, 1) as c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts
cross join sys.columns where object_id = object_id('bcp_in')
order by tc.commit_ts

関連するシステムテーブル

■ sys.syscommittab
1DBあたり1つ作成される。
変更の追跡を設定しているテーブルの更新があった際、そのコミット日時やトランザクションの内部IDなどを持つ。
内部テーブルのためDACでないとSELECTできないが、sys.dm_tran_commit_tableでラップしてあるので、そこからSELECT可能。
1トランザクションごとに1レコードINSERT。

■ sys.change_tracking_
変更の追跡を設定しているテーブルごとに1つ作成される。
1レコードごとに1レコードINSERT。
テーブルをTRUNCATEすると、このサイドテーブルも一緒にTRUNCATEされる模様。
同じくDACでないと直接はSELECTできないが、changetable()を経由してアクセスできる。

オーバーヘッド

同一トランザクション内で同期的にサイドテーブルへも書き込むため、更新時間が伸びる。
オーバーヘッドについての計測はこちらで実施したところ、「インデックスを1つ追加することによる影響よりは小さいが、同程度」という結果になった。

導入時の注意点

DB単位の設定

ALTER DATABASE [TEST] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 1 DAYS)

本番運用環境で数台実施したが、ブロッキングは起きなかった。

テーブル単位の設定

ALTER TABLE [bcp_in] ENABLE CHANGE_TRACKING  

テーブルにSch-Mロックをかける必要があるため、本番環境下だとブロッキングが発生することもある。
かつ、Sch-Mロックがブロッキングチェーンに存在すると、該当テーブルへのあらゆるクエリがブロックされるため、ブロッキングの発生は最小限に抑えるべき。set lock_timeoutを設定してから実行するか、ブロッキングが発生しやすい環境でALTER系クエリを実行させるための方法を使ってリリースすると安全性が高まる。

運用時の注意点

クリーンアップジョブによるブロッキングの発生

こちらにまとめています。

変更の追跡に関するシステムテーブルの肥大化

こちらにまとめています。

変更の追跡に関するシステムテーブルのデータ確認

期限切れのデータをクリーンアップジョブが削除しきれているかは以下のクエリによって確認できます。

--■ 削除用のウォーターマーク
--タイムスタンプを確認
select * from sys.sysobjvalues where valclass = 7 AND objid IN (1003,1004)
 
--｢ここまで削除してもOK」という日時を確認
select commit_time from sys.syscommittab with(nolock)
where commit_ts = (select value from sys.sysobjvalues where valclass = 7 AND objid IN (1003))
 
--「削除がここまで完了しているよ」と認識されている日時を確認
select commit_time from sys.syscommittab with(nolock)
where commit_ts = (select value from sys.sysobjvalues where valclass = 7 AND objid IN (1004))
 
--■ sys.syscommittab
--sys.syscommittabの件数と最小値を確認
select min(commit_time) as min_commit_time, count(*) as count, min(commit_ts) as min_commit_ts from sys.syscommittab with(nolock)
 
--■ sys.change_tracking_<object_id>
set nocount on
Declare @stmt_select nvarchar(1000)
Declare @CTSideTables sysname
 
Declare curCTSideTables Cursor
For Select name from sys.internal_tables where internal_type=209 order by name
Open curCTSideTables
Fetch Next From curCTSideTables Into @CTSideTables
While @@Fetch_Status=0
Begin
    select @stmt_select =
    N'declare @min_sys_change_xdes_id bigint
    select @min_sys_change_xdes_id = min(sys_change_xdes_id) from sys.' + @CTSideTables + ' with(nolock)
    if @min_sys_change_xdes_id is not null select top 10 ''' + @CTSideTables + ''' as table_name ,* from sys.syscommittab with(nolock) where xdes_id = @min_sys_change_xdes_id'
    --↑サイドテーブルが0レコードの場合は情報出さないようにする
 
    exec sp_executesql @stmt_select
if (@@rowcount = 0)
begin
print @CTSideTables
end
    Fetch Next From curCTSideTables Into @CTSideTables
End
 
close curCTSideTables
deallocate curCTSideTables

変更の追跡に関する注意点まとめ

• 1テーブルに設定するオーバーヘッドは、インデックスを1つ作成するのと同程度
• DBへの導入時は時間が数十秒かかることもあるがブロッキングは起きたことは無い（経験上）
• テーブルへの導入時はブロッキングの発生期間を最小限に抑える工夫が必須
• 運用開始後は関連システムテーブルの削除の進み具合をチェックする
  • 間に合っていないとブロッキングが発生しやすくなる → サーバーレベルでのロックエスカレーション無効化を検討
  • 間に合っていないとCHANGETABLES()を使ったSELECTのパフォーマンスが必要以上に劣化 → 手動削除を検討