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

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

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

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

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

表紙_201612.png

検証内容

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

検証詳細

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

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

検証環境

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

マシン情報

 

検証テーブル情報(TEST_AUDIT)

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

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

    1. OBを使用して、前述の検証テーブルを作成します。
    2. テーブルで必要になる 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 )
      
    3. テンポラリ テーブルとして有効化します。
      ALTER TABLE TEST_AUDIT
       SET ( SYSTEM_VERSIONING = ON
        ( HISTORY_TABLE = dbo.TEST_AUDIT_HISTORY) )
      
    4. テンポラリテーブルを利用しない場合は無効化します。
      ALTER TABLE TEST_AUDIT
       SET ( SYSTEM_VERSIONING = OFF )
      
    5. OBのデータ生成機能を使用し、データを投入します。(投入する件数は各パターンごとに設定)
      列名 データ生成方法
      MY_NUMBER 連番。1から開始し1ずつカウントアップ
      NAME テンプレート 苗字 名前
      TEL_NUMBER 乱数値 電話番号
      ADDRESS テンプレート 住所
    6. 更新処理を各行に対して行うため、OBを使って既存データのUPDATEを行います。

データベース開発に関するお役立ち資料

既存データのUPDATE.png

    1. この既存データのUPDATEにかかった時間を計測します。
    2. 一回ごとにキャッシュをクリアします。
      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

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

グラフ_201612_1.png

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

グラフ_201612_2-1.png

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

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

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

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

 

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

証跡の件数 更新時間(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

無題.png

証跡が蓄積されても更新時間は劣化しない!!

結論

今回の検証結果では、以下のようになりました。
・テンポラルテーブルは簡単なSQLを実行することで利用できる。
・既存のテーブルもテンポラルテーブルとして利用が可能。
・テンポラルテーブルを利用して証跡を残すと更新処理のパフォーマンスは低下する。
・データの蓄積によって性能は劣化しない。

テンポラルテーブルの特性上、更新の際に別のテーブルに同じレコードを挿入して証跡を残すため、当初の想定通り性能の低下がみられました。
今回の実験では結果にある通りの低下率となりましたが、項目数やSQL1回の実行に対するに更新行数によっても異なってくるかと思われます。

監査証跡を目的として導入する場合は、実行が予測されるSQLと相談になりそうです。

また、今回検証して分かったこととして、テンポラリテーブルは既存テーブルにも使え、簡単なSQL実行で開始/終了ができるので、導入難易度は低いと感じました。
これくらいの性能低下は許容できるようであれば、とても使いやすいと考えます。

そしてデータの蓄積により性能は劣化しないという結果が出ました。
これは長期に渡って証跡を残しても大丈夫、ということになります。証跡は保存期間が重要なため、大きなメリットとなるのではないでしょうか。

今回の検証結果は以上となります。

新規CTA

RECENT POST「DBlab」の最新記事


この記事が気に入ったらいいねしよう!
OBトライアル
ブログ購読のお申込み

RANKING人気資料ランキング

RANKING人気記事ランキング

RECENT POST 最新記事