SE の雑記

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

インデックスのオンライン/オフライン再構築のシンプルな比較

leave a comment

SQL Database / SQL Server Enterprise Edition を使用している場合、インデックスの再構築 (REBUILD) をオンラインで実施することができます。
オンラインのインデックス再構築の仕組みについては、オンライン インデックス操作の動作原理 を確認していただければ。

オンラインのインデックス再構築は、データアクセスをブロックすることなく、インデックスのメンテナンスを実施することができますが、オフラインでの再構築と比較していろいろとオーバーヘッドがありますので、軽く比較してみたいと思います。

今回は、100 万件 / 100MB 程度のサイズのインデックスを再構築しています。

まずは処理時間と、生成されるトランザクションログのサイズを比較してみたいと思います。
# 処理時間は、sec でざっくりと取得しているので、実際には msec の処理時間のケースもあります。

以下の表が「完全」復旧モデルを使用した場合の比較となります。

再構築方法

処理時間

ログレコード

ログサイズ (Byte)

ログバックアップ
ページ数

オフライン

00:00:01

26,824

1,841,928

14,101

オンライン

00:00:05

2,590,166

157,913,868

55,493

 

オフラインとオンラインで処理時間や、ログレコードの状態に違いがあることが確認できますね。
次は、ログレコードレベルでどのような差があるかを確認してみたいと思います。

完全復旧 オフライン 完全復旧 オンライン
operation count RecordFix operation count RecordFix
LOP_BEGIN_CKPT 1 96 LOP_BEGIN_CKPT 1 96
LOP_BEGIN_XACT 97 7,372 LOP_BEGIN_XACT 27,715 2,106,340
LOP_BUF_WRITE 30 1,020 LOP_BUF_WRITE 76 2,584
LOP_BULK_EXT_ALLOCATION 216 9,936
LOP_COMMIT_XACT 97 7,760 LOP_COMMIT_XACT 27,715 2,217,200
LOP_COUNT_DELTA 6 1,248
LOP_CREATE_ALLOCCHAIN 1 40 LOP_CREATE_ALLOCCHAIN 2 80
LOP_DELETE_ROWS 11 682 LOP_DELETE_ROWS 18 1,116
LOP_DELETE_SPLIT 478 28,680
LOP_END_CKPT 1 136 LOP_END_CKPT 1 136
LOP_EXPUNGE_ROWS 6 372 LOP_EXPUNGE_ROWS 13 806
LOP_FORMAT_PAGE 13,739 1,099,120 LOP_FORMAT_PAGE 59,476 4,758,080
LOP_HOBT_DDL 5 180 LOP_HOBT_DDL 6 216
LOP_HOBT_DELTA 36 2,304 LOP_HOBT_DELTA 29,478 1,886,592
LOP_INSERT_ROWS 44 2,728 LOP_INSERT_ROWS 2,027,852 125,726,824
LOP_INSYSXACT 204,186 10,209,300
LOP_LOCK_XACT 31 744 LOP_LOCK_XACT 31,581 757,944
LOP_MIGRATE_LOCKS 27,004 810,120
LOP_MODIFY_HEADER 28 1,736 LOP_MODIFY_HEADER 121,388 7,526,056
LOP_MODIFY_ROW 4,008 248,496 LOP_MODIFY_ROW 9,776 606,112
LOP_ROOT_CHANGE 12 1,152
LOP_SET_BITS 8,685 468,990 LOP_SET_BITS 15,156 818,424
LOP_SET_FREE_SPACE 2 104 LOP_SET_FREE_SPACE 2 104
LOP_SHRINK_NOOP 1 24 LOP_SHRINK_NOOP 2 48
LOP_XACT_CKPT 1 24 LOP_XACT_CKPT 1 24
26,823 1,841,832 2,582,161 157,469,218

 

トランザクションの開始の頻度や、変更されているレコード数が大きく違いますね。
オンラインとオフラインでは、データの書き込みの粒度が異なっており、それが処理効率に影響を与えていることが、この比較から確認できるかと思います。

それでは、一括ログ復旧にすると、どのような傾向となるかも比較してみたいと思います。
# 一括ログについては、ログバックアップ時にログバックアップ時にデータファイルについても取得されるため、その情報も記載しています。

再構築方法

処理時間

ログレコード

ログサイズ (Byte)

ログバックアップ
ページ数

オフライン

00:01

7,973

452,714

データファイル : 13,936
ログファイル : 165

オンライン

00:03

389,177

22,554,606

データファイル : 27,256
ログファイル : 42,72

 

完全復旧と比較すると、処理速度が向上していますが、オフライン / オンラインの傾向は同じですね。
ログレコードの内容についても傾向は同じになります。

一括ログ復旧 オフライン 一括ログ復旧 オンライン
operation count RecordFix operation count RecordFix
LOP_BEGIN_CKPT 1 96 LOP_BEGIN_CKPT 1 96
LOP_BEGIN_XACT 97 7,372 LOP_BEGIN_XACT 27,729 2,107,404
LOP_BUF_WRITE 50 1,700 LOP_BUF_WRITE 26,868 913,512
LOP_BULK_EXT_ALLOCATION 217 9,982
LOP_COMMIT_XACT 97 7,760 LOP_COMMIT_XACT 27,729 2,218,320
LOP_COUNT_DELTA 6 1,248
LOP_CREATE_ALLOCCHAIN 1 40 LOP_CREATE_ALLOCCHAIN 2 80
LOP_DELETE_ROWS 11 682 LOP_DELETE_ROWS 18 1,116
LOP_DELETE_SPLIT 487 29,220
LOP_END_CKPT 1 136 LOP_END_CKPT 1 136
LOP_EXPUNGE_ROWS 4 248 LOP_EXPUNGE_ROWS 13 806
LOP_FORMAT_PAGE 31 2,480 LOP_FORMAT_PAGE 30,495 2,439,600
LOP_HOBT_DDL 5 180 LOP_HOBT_DDL 6 216
LOP_HOBT_DELTA 36 2,304 LOP_HOBT_DELTA 29,973 1,918,272
LOP_INSERT_ROWS 44 2,728 LOP_INSERT_ROWS 30,433 1,886,846
LOP_INSYSXACT 4,532 226,600
LOP_LOCK_XACT 31 744 LOP_LOCK_XACT 32,674 784,176
LOP_MIGRATE_LOCKS 27,095 812,850
LOP_MODIFY_HEADER 26 1,612 LOP_MODIFY_HEADER 121,899 7,557,738
LOP_MODIFY_ROW 4,018 249,116 LOP_MODIFY_ROW 10,247 635,314
LOP_RANGE_INSERT 110 3,960
LOP_ROOT_CHANGE 14 1,344
LOP_SET_BITS 8,943 482,922 LOP_SET_BITS 18,631 1,006,074
LOP_SET_FREE_SPACE 2 104 LOP_SET_FREE_SPACE 2 104
LOP_SHRINK_NOOP 1 24 LOP_SHRINK_NOOP 2 48
LOP_XACT_CKPT 1 24 LOP_XACT_CKPT 1 24
13,400 760,272 389,185 22,555,086

 

オンラインインデックスの再構築は、データアクセスの同時実行性を低下させずにインデックスをメンテナンスすることが可能ですが、処理時間やトランザクションログの書き込み負荷とのトレードオフとなりますので、データへのアクセスを停止できるようなメンテナンスウィンドウがある場合は、状況に応じて使い分けていくと、良いかと。

Written by masayuki.ozawa

7月 31st, 2016 at 4:52 pm

Leave a Reply

*