SQLServer テンポラルテーブルを検証してみた

2016.12.13 株式会社システムインテグレータ

SQL Server 2016 がリリースされて、はや半年が経とうとしていますが、新機能は皆さまどれくらい活用していますでしょうか。今回はSQL Server 2016 目玉機能の一つであります、テンポラルテーブルに注目してみました。

SQL Server 2016 では、過去の更新履歴を自動保存できる「テンポラルテーブル」機能が備わっているようで、これを利用して監査証跡を残すことができるとしています。マイナンバー導入後、監査証跡は大変重要で注目を集めていますが、やはり実装後の性能低下がどうしても出てしまうことが予想されます。

これは黙っちゃいられない、とDBラボ研究チームはSQL Server 2016 のテンポラルテーブルで性能観点から~~遊んでみました~~検証してみました。

SQLServer テンポラルテーブルを検証してみた 1

検証内容

SQL Server 2016 のテンポラルテーブルを利用して、テーブル更新(UPDATE)の証跡を記録した場合としない場合でどれくらいの性能差が出るかを検証します。

検証詳細

同じ件数のデータが格納されているテーブルを用意して、全行に対してそれぞれ更新した場合に要する時間を計測します。件数は何パターンか用意して、テンポラルテーブルを利用した場合としない場合で、５回ずつ試行し平均時間を比較します。

件数パターンは下記の4パターン行います。
・100件
・1,000件
・10,000件
・100,000件

検証環境

今回の検証環境は以下の通りです。

マシン情報

項目	情報
OS	Windows 10 64bit
CPU	Intel Core i7-4610M 3.00GHz
メモリ	16GB
DB	SQL Server 2016

検証テーブル情報(TEST_AUDIT)

列名	型	長さ	精度	PK
MY_NUMBER	NUMERIC	10	0	Y
NAME	NVARCHAR	50
TEL_NUMBER	NVARCHAR	20
ADDRESS	NVARCHAR	100

検証準備(テンポラルテーブルの利用方法)

OBを使用して、前述の検証テーブルを作成します。

テーブルで必要になる 2 つの列（日付を格納するための列）を追加します。

ALTER TABLE TEST_AUDIT 
 ADD sysstart datetime2 GENERATED ALWAYS AS ROW START 
      DEFAULT CONVERT(datetime2, '2016-01-01'), 
     sysend   datetime2 GENERATED ALWAYS AS ROW END 
      DEFAULT CONVERT(datetime2, '9999-12-31 23:59:59.9999999'), 
     PERIOD FOR SYSTEM_TIME ( sysstart, sysend )

テンポラリテーブルとして有効化します。

ALTER TABLE TEST_AUDIT
 SET ( SYSTEM_VERSIONING = ON
  ( HISTORY_TABLE = dbo.TEST_AUDIT_HISTORY) )

テンポラリテーブルを利用しない場合は無効化します。
```
ALTER TABLE TEST_AUDIT
 SET ( SYSTEM_VERSIONING = OFF )
```

OBのデータ生成機能を使用し、データを投入します。(投入する件数は各パターンごとに設定)

列名	データ生成方法
MY_NUMBER	連番。1から開始し1ずつカウントアップ
NAME	テンプレート　苗字名前
TEL_NUMBER	乱数値　電話番号
ADDRESS	テンプレート　住所

更新処理を各行に対して行うため、OBを使って既存データのUPDATEを行います。

SQLServer テンポラルテーブルを検証してみた 2

この既存データのUPDATEにかかった時間を計測します。
一回ごとにキャッシュをクリアします。
```
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
```

それでは検証開始です。

検証結果

実行時間は以下の通りとなりました。

件数	証跡	1回目	2回目	3回目	4回目	5回目	平均
100	なし	00:00.183	00:00.186	00:00.192	00:00.195	00:00.194	00:00.190
100	あり	00:00.240	00:00.235	00:00.243	00:00.245	00:00.246	00:00.242
1000	なし	00:01.806	00:01.806	00:01.806	00:01.800	00:01.789	00:01.801
1000	あり	00:02.391	00:02.373	00:02.367	00:02.367	00:02.368	00:02.373
10000	なし	00:17.253	00:16.961	00:16.818	00:16.653	00:16.707	00:16.878
10000	あり	00:23.759	00:23.111	00:22.574	00:22.840	00:22.479	00:22.953
100000	なし	02:48.735	02:53.240	02:59.872	03:00.320	02:53.291	02:55.092
100000	あり	04:09.337	04:26.802	03:52.256	03:59.174	04:16.983	04:08.910

各件数の平均実行時間を比較すると以下のようになります。

SQLServer テンポラルテーブルを検証してみた 3

全体を比較するとこのような感じになります。

SQLServer テンポラルテーブルを検証してみた 4

それぞれ「証跡なし」から見て、「証跡あり」は約30％～40％実行時間が延びています。

さて、更新自体に時間がかかることはわかりました。

が、証跡を残す以上データが蓄積されていくとどうなるのか気になるところです。

このまま引き続き貯めてみましょうか！ [RELATED_POSTS]

１０万件の更新を続けて、証跡をどんどん増やしてみた

証跡の件数	更新時間（10万レコード）
100000	04:05.251
200000	04:01.439
300000	04:01.948
400000	03:53.351
500000	03:55.381
600000	04:00.267
700000	03:59.230
800000	04:04.899
900000	03:55.431
1000000	04:02.863