インデックス再構築と再構成の違い

 

神谷 雅紀
Escalation Engineer

 

 

インデックスの再構築 (alter index rebuild) と再構成 (alter index reorganize) のどちらを行うかを決定する際の目安として、sys.dm_db_index_physical_stats の avg_fragmentation_in_percent の値を用いる方法があります。これはこれで現在の状態を確認するという意味では有効であると思いますが、断片化の解消を目的とし、また、実際の運用を考慮した場合、再構築や再構成前の断片化の状態よりも、再構築や再構成の実行中の状況や実行後のインデックスの状態の方が重要ではないでしょうか?

当然断片化していないインデックスに対して再構築や再構成を行うことは無駄ですので、再構築や再構成を行う前に、断片化率を確認する必要はあるでしょう。しかし、再構築と再構成では、実行中のインデックスの使用可否や実行後のインデックスの状態が異なります。現在の断片化率だけで再構築と再構成のどちらを行うかを判断するよりも、再構築と再構成の違いを理解した上で判断した方がよいのではないでしょうか?

 

※ 再構築と再構成を行う必要があるかどうかについては、断片化についてに記載したとおり、avg_fragmentation_in_percent の目安値はあくまでも目安であり、すべてのシステムに画一的に適用できるとは限らず、個々のシステムによって調整する必要があります。

 

インデックスの再構築 (rebuild) と再構成 (reorganize) の違い

 

  再構築 (rebuild) 再構成 (reorganize)
処理対象 インデックス全体。 インデックスのリーフレベルのみ。
同時実行性 処理中は処理対象インデックスは使用不可。(ONLINE の場合は使用可) インデックス全体がロックされる。 処理中も処理対象インデックスは使用可。 処理しているページに対して、そのページの処理中のみロックが保持される。
処理を途中でキャンセルした場合 処理はすべて無効。(元の状態に戻される。) キャンセルした時点までの処理は有効。(キャンセル時点で処理が完了した部分については断片化が解消した状態が維持される。)
生成されるトランザクションログレコードの量 断片化の度合にほとんど影響されない。 断片化の度合が大きいと多くなる。
使用するデータファイル内の領域 再構成よりも多い。 再構築よりも少ない。
処理完了までに必要な時間 断片化の度合にほとんど影響されない。 断片化の度合が大きいと長くなる。

 

インデックスの再構築と再構成の最も大きな違いは、再構築がひとつのトランザクションでインデックスを完全に再作成するのに対して、再構成は処理単位ごと (処理単位はページ) にトランザクションが分割されていて、リーフページ間でインデックス行を移動させることで行を前に詰めて断片化を解消しているという点です。

再構築はひとつのトランザクションですので、キャンセルすれば当然そのトランザクションはロールバックされ、トランザクション開始前の状態、つまり、断片化した状態に戻されます。一方、再構成はトランザクションが分割されているため、キャンセルしても、既に完了しているトランザクションの結果は維持されます。

再構築はインデックスを作り直しますので、インデックス行数 (テーブルの行数) が増えない限り、大きく処理時間が変わることはありません。一方、再構成の場合は、前のページに空きがある場合に後ろのページから前のページへ行を移動するという処理であるため、移動する行が多い、つまり、断片化の度合が大きいほど処理時間が長くなります。

再構築の場合、再作成中も元のインデックスを削除することはできませんので、一時的には、断片化した古いインデックスと再作成した新しいインデックスがふたつ存在する状況となり、それらを格納するための容量がデータファイルに必要になります。さらに既定ではソート用の領域も必要になります。一方、再構成の場合は、ページ間で行を移動するだけなので、そのような追加の領域は基本的には必要としません。

生成されるトランザクションログの量についても、再構築の場合はインデックス行数が増えなければトランザクションログ量が大きく増えることはありませんが、再構成の場合は、断片化の度合が大きく、移動する行数が多くなれば、生成されるトランザクションログ量が多くなります。

 

実際の運用では、このような違いを考慮して、どちらを実行するのかを決定する必要があるのではないでしょうか。

例えば、処理に割り当てられる時間が限られていて、処理を途中でキャンセルしなければならない可能性があるのであれば、再構築よりも再編成の方が、キャンセルした場合にもすべてが無駄にならず、毎日時間の許される範囲で実行するという方法を取ることがきます。反対に、再構築に必要となる時間は確保できるので、実行時間にばらつきが出るよりもある程度一定の時間で完了することが優先されるのであれば、再構成よりも再構築の方が有利でしょう。

尚、インデックスの再構成は、行の移動を行っているページに対して排他ロックを獲得します。これは非常に短い期間のみ保持されるロックであるため、その他の処理が長期間ブロックされることはありません。ただし、その他の処理が獲得しようとするロックによっては、デッドロックとなる可能性はあります。

 

参考資料

再構築の OFFLINE と ONLINE、MAXDOP = 1 と MAXDOP > 1 の動作の違いについては、Books OnlineUS サポートチームの Blog が参考になります。