SQL Server 2016 がリリースされて、はや半年が経とうとしていますが、新機能は皆さまどれくらい活用していますでしょうか。今回はSQL Server 2016 目玉機能の一つであります、テンポラルテーブルに注目してみました。
SQL Server 2016 では、過去の更新履歴を自動保存できる 「テンポラル テーブル」機能が備わっているようで、これを利用して監査証跡を残すことができるとしています。マイナンバー導入後、監査証跡は大変重要で注目を集めていますが、やはり実装後の性能低下がどうしても出てしまうことが予想されます。
これは黙っちゃいられない、とDBラボ研究チームはSQL Server 2016 のテンポラルテーブルで性能観点から遊んでみました検証してみました。
検証内容
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を行います。
- この既存データの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 |
| あり | 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 |
| あり | 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 |
| あり | 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 |
| あり | 04:09.337 | 04:26.802 | 03:52.256 | 03:59.174 | 04:16.983 | 04:08.910 |
各件数の平均実行時間を比較すると以下のようになります。
全体を比較するとこのような感じになります。
それぞれ「証跡なし」から見て、「証跡あり」は約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 |
証跡が蓄積されても更新時間は劣化しない！！
結論
今回の検証結果では、以下のようになりました。
・テンポラルテーブルは簡単なSQLを実行することで利用できる。
・既存のテーブルもテンポラルテーブルとして利用が可能。
・テンポラルテーブルを利用して証跡を残すと更新処理のパフォーマンスは低下する。
・データの蓄積によって性能は劣化しない。
テンポラルテーブルの特性上、更新の際に別のテーブルに同じレコードを挿入して証跡を残すため、当初の想定通り性能の低下がみられました。
今回の実験では結果にある通りの低下率となりましたが、項目数やSQL１回の実行に対するに更新行数によっても異なってくるかと思われます。
監査証跡を目的として導入する場合は、実行が予測されるSQLと相談になりそうです。
また、今回検証して分かったこととして、テンポラリテーブルは既存テーブルにも使え、簡単なSQL実行で開始/終了ができるので、導入難易度は低いと感じました。
これくらいの性能低下は許容できるようであれば、とても使いやすいと考えます。
そしてデータの蓄積により性能は劣化しないという結果が出ました。
これは長期に渡って証跡を残しても大丈夫、ということになります。証跡は保存期間が重要なため、大きなメリットとなるのではないでしょうか。
今回の検証結果は以上となります。
![いまさら聞けない Oracleの基本 [初級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b.png)
![いまさら聞けない Oracleの基本 [中級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b2.png)
