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

神谷 雅紀
Escalation Engineer

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

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

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

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

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

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

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

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

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

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

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

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

• Anonymous
  August 27, 2015
  表のタイトルのreorganizeの役が誤っています。「再作成」ではなく「再構成」です。