MS_IndexRebuildAndDefrag

インデックスの再構築・デフラグ

• 戻る（SQL Server）
  • インデックスの再構築・デフラグ
  • SQL Server のインデックス / SQL Server の管理 / データ ファイルの圧縮と拡張

概要

インデックスの断片化を解消し、
ディスク I/O を減らす。

監視

DBCC SHOWCONTIG ステートメントで、「インデックスの断片化」を特定できる。
（DBCC SHOWCONTIG は削除予定なので、sys.dm_db_index_physical_stats を使用する）

補足（最新化）: DBCC SHOWCONTIG は
SQL Server 2012 で削除済みであり、現在は使用できない。
本ページの DBCC SHOWCONTIG に関する記述は
歴史的な説明として読み、実際には
sys.dm_db_index_physical_stats を使う
（具体的なクエリはSQL Server のインデックスの
「断片化の測り方と対処」を参照）。

出力項目の対応は以下。

DBCC SHOWCONTIG	sys.dm_db_index_physical_stats
スキャンされたページ数	page_count
論理スキャン フラグメンテーション	avg_fragmentation_in_percent
平均ページ密度 (全体)	avg_page_space_used_in_percent
スキャン密度	（廃止。相当する列は無い）

断片化の監視

• DBCC SHOWCONTIG ステートメントを使用することで、
  「インデックスの断片化」レベルを監視し、断片化の進んだインデックスを特定できる。

• DBCC SHOWCONTIG ステートメントの TABLERESULTS オプションを使用すると、
  情報を行セットとして返すので、これをテーブルに定期的に書き込めば、
  時系列に「インデックスの断片化」レベルを記録、監視できる。

• 負荷の高いサーバで DBCC SHOWCONTIG ステートメントを実行するときは、
  WITH FAST オプションを使用し、インデックスの「リーフ ページ」が
  スキャンされないようにして、性能を向上できる。
  ただし、「ページ密度」の測定はできない。

補足: WITH FAST に相当するのが、
sys.dm_db_index_physical_stats の第 5 引数のモード指定である。

モード	走査範囲	ページ密度
LIMITED	親レベルのみ（最速）	取得できない
SAMPLED	1% をサンプリング	取得できる
DETAILED	全ページ（最も重い）	取得できる

定期監視では SAMPLED が実用的である。

実行結果

• 再構築したばかりの「クラスタ化インデックス」に対して、
  DBCC SHOWCONTIG ステートメントを実行した際の出力。

- スキャンされたページ数...........................	: 52632
- スキャンされたエクステント数................		: 6604
- 切り替えられたエクステント数................		: 6603
- エクステントごとの平均ページ数............		: 8.0
- スキャン密度 [最善 :実際] ......................	: 99.62% [6579:6604]
- 論理スキャン フラグメンテーション......		: 0.01%
- エクステント スキャン フラグメンテーション....	: 0.14%
- ページごとの平均空きバイト数...............		: 10.5
- 平均ページ密度 (全体).............................	: 99.87%

• 再構築したばかりの「非クラスタ化インデックス」
  （「クラスタ化インデックス」が存在しない場合）に対して、
  DBCC SHOWCONTIG ステートメントを実行した際の出力。

- スキャンされたページ数............................	: 55274
- スキャンされたエクステント数..................	: 6913
- 切り替えられたエクステント数.................	: 6912
- エクステントごとの平均ページ数.............		: 8.0
- スキャン密度 [最善 :実際]........................	: 99.96% [6910:6913]
- エクステント スキャン フラグメンテーション ....	: 0.03%
- ページごとの平均空きバイト数.................	: 397.0
- 平均ページ密度 (全体)..............................	: 95.10%

出力結果

ここでは、DBCC SHOWCONTIG ステートメントで出力した情報から、
「インデックスの断片化」レベルを確認する 2 つの方法について説明する。

「スキャン密度（%）」で確認

• 「ページ分割」が発生すると、
  分割された一部の「リーフ レベル ページ」が、
  別の「エクステント」に格納されることがあるので、
  「インデックスの断片化」が進むと、
  正味のデータ量の割に「エクステント」の数が大きくなる。

• この問題は、「スキャン密度（％）」で判断することができる。
  「スキャン密度（％）」が小さくなった場合、
  「インデックスの断片化」が進んでいることを示す。

• 「スキャン密度（％）」は、次の式で算出される。

値	説明（式）
スキャン密度（％）	最善のエクステント変更回数 / 現在のエクステント変更回数 * 100
最善のエクステント変更回数	すべての「ページ」が連続的にリンクされる場合、スキャン処理によってエクステントが変更される回数
現在のエクステント変更回数	実際のスキャン処理を実行してエクステントが変更された回数

「論理スキャン フラグメンテーション」で確認

• 「クラスタ化インデックス」の場合、
  「リーフ レベル ページ」の「データ ページ」中のデータが
  物理的に順序正しく並べられる。
  しかし、「ページ分割」が発生すると、分割された一部の「データ ページ」が、
  物理的に離れた位置に格納されることがあるので、
  順序が不正な「データ ページ」の割合が増える。

• この問題は、「論理スキャン フラグメンテーション」で判断することができる。
  この値が大きくなった場合、「インデックスの断片化」が進んでいることを示す。
  この値はできるだけ 0% に近い値にする。0 ~ 10% が許容範囲であり、
  これを超えると、「インデックス スキャン」の性能が低下する可能性がある。

値	説明（式）
論理スキャン フラグメンテーション（％）	物理的に順序正しく並べられていない「リーフ レベル ページ」の割合
エクステント スキャン フラグメンテーション（％）	物理的に離れた位置にある「エクステント」の割合

修正

インデックスの再構築 (alter index rebuild)

再構築なので断片化の度合に影響されず、All or Nothing。

• テーブル、インデックスの構造や制約がわからない場合でも、
  1 つのステートメントでテーブルの全ての「再構築」ができるため、
  複数の DROP INDEX と CREATE INDEX を使用して「再構築」するより簡単。

• 同時に、オプティマイザの使用する統計が更新される。

• オプションで「ページ密度」を変更することができる。

移行メモ（誤字）: 元ページの「度合に響されず」「度合に響され」は
「度合に影響されず／され」の誤記と思われる。

補足（統計の更新について）: 「同時に統計が更新される」のは正しいが、
REBUILD は FULLSCAN 相当の精度で更新されるのに対し、
REORGANIZE は統計を更新しないという違いがある。

このため、REORGANIZE を選んだ場合は
別途 UPDATE STATISTICS が必要になる
（SQL Server のオプティマイザ）。
なお、REBUILD 後に UPDATE STATISTICS を重ねて実行すると、
サンプリング精度が下がることがあるため無駄かつ有害である。

再構成 (alter index reorganize)

再構成なので断片化の度合に影響され、キャンセル時点までの再構成は有効。

• 「リーフ レベル ページ」を物理的に順序正しく並べ替え、「最適化」する。
• これにより、「インデックス スキャン」の性能が向上する。
• ただし、この方法は「再構築」の操作よりも劣る。
• 期待した効果が得られない場合は、「再構築」が必要になる。

補足（REORGANIZE は中断できる）: 「キャンセル時点までの再構成は有効」は
運用上たいへん重要な性質である。
REORGANIZE は常にオンラインで、小さなトランザクションの積み重ねで進むため、
メンテナンス時間が尽きたら中断してよい。
一方 REBUILD は 1 つのトランザクションなので、
中断するとすべてロールバックされる（時間もログも無駄になる）。

なお、SQL Server 2017 以降は
RESUMABLE = ON を指定すると REBUILD も
中断・再開できるようになった（Enterprise Edition）。

ALTER INDEX IX_Name ON dbo.Table1
  REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

比較

同時実行性

• 「再構築」

  • 処理中インデックス全体がロックされインデックスは使用不可
  • ONLINE オプションを付与した場合は使用可だが、
    再構築を完了するためにかなり多くのリソースを使用する。

• 「最適化」
  処理中ページのみロックされインデックスは使用可

補足: ONLINE = ON は Enterprise Edition の機能である
（SQL Server のエディション）。
Standard Edition ではオフラインでの再構築しかできないため、
24 時間止められないシステムでは REORGANIZE が主力になる。

また、ONLINE = ON であっても、
処理の開始時と終了時に短時間のスキーマ ロックが必要である。
長時間実行中のトランザクションがあるとここでブロックされるため、
WAIT_AT_LOW_PRIORITY オプションで
待ち方（待つ・自分が諦める・相手を切る）を指定できる。

必要なデータ領域の空き容量

• 「再構築」
  再構成よりも多い。

  • 「再構築」には、「データ ファイル」に十分な空き容量が必要になる。
  • 必要な空き領域は、再構築するインデックス量に応じて変化する。
  • 「クラスタ化インデックス」の場合、
    「必要な空き領域 = 1.2 * (平均行サイズ) * (行数)」が目安となる。

• 「最適化」
  再構築よりも少ない。

補足: REBUILD は新しいインデックスを作ってから古いものを破棄するため、
一時的に 2 倍近い領域が要る。
空き容量が足りないと自動拡張が走り、
それ自体が性能問題を引き起こす
（データ ファイルの圧縮と拡張）。
SORT_IN_TEMPDB = ON を指定すると並べ替え作業を tempdb に逃がせるが、
今度は tempdb の容量が必要になる
（SQL Server のファイルの配置）。

トランザクション ログ量の比較

• 「再構築」

  • インデックスの「ページ」のイメージをトランザクション ログに記録する。

  • このため、トランザクション ログ領域を大量に使用することがある。

  • 必要なトランザクション ログ領域は、大まかに見積もって、
    「ページ」数 × 8 KB となる。

  • 「ページ」数は、DBCC SHOWCONTIG ステートメントで確認できる。

  • ただし、「再構築」のトランザクション ログは、
    「一括ログ復旧モデル」では記録されないので、
    必要に応じて「復旧モデル」を変更する。

• 「最適化」

  • 一般的に、「最適化」のトランザクション ログ領域の使用量は、
    「再構築」のトランザクション ログ領域の使用量より少量になる。
    ただし、「最適化」処理の作業量による（断片化の度合が大きいと多くなる）。

  • 「最適化」のトランザクション ログは、「一括ログ復旧モデル」でも記録される。

補足（一括ログ復旧モデルの副作用）: 「再構築のために復旧モデルを変更する」は
ログ量削減に有効だが、ポイントインタイム復旧ができない区間が生じる。
前後にログ バックアップを取得して
ログの鎖をつなぎ直す手順が必要になる
（SQL Server 大量データ処理時の性能問題、
SQL Server の障害復旧）。

なお、ONLINE = ON の再構築は最小ログ記録の対象外なので、
一括ログ復旧モデルにしてもログ量は減らない点に注意。

注意事項

• 旧式の I/O サブシステムを使用している環境では、
  「インデックスの断片化」を修正する前に「ディスクの断片化」を修正する。

• SAN を使用している環境では、「ディスクの断片化」を修正する必要はない。

  • 「インデックスの断片化」は I/O 処理能力に影響するため、
    「インデックス ページ」、「データ ページ」がデータ キャッシュ内に
    存在するクエリの性能には影響を与えない。
  • SAN では、一般的に大容量のデータ キャッシュが提供されるため、
    小規模環境に比べると、影響を受け難い。

補足（最新化：SSD 時代の断片化）: 上記の指摘は
SSD / NVMe の時代にはさらに強く当てはまる。
ランダム アクセスのコストが極めて小さいため、
論理的な断片化（avg_fragmentation_in_percent）が
性能に与える影響は HDD 時代よりはるかに小さい。

現在、断片化対策で本当に効くのは以下である。

指標	影響
avg_fragmentation_in_percent（順序の乱れ）	SSD ではほぼ無視できる
avg_page_space_used_in_percent（ページ密度）	低いとページ数が増え、I/O とメモリを無駄に消費する

つまり、「順序が乱れているから」ではなく
「スカスカだから」再構築する、という判断に軸が移っている。

また、インデックス メンテナンスの真の価値は
統計情報が更新されることにある、という指摘も多い。
断片化の解消より、統計の鮮度を保つことのほうが
実行プランへの影響が大きいためである
（SQL Server のオプティマイザ）。

補足（運用の定石）: 判断基準と実装は以下。

断片化率	対処
〜5%	何もしない
5〜30%	ALTER INDEX ... REORGANIZE
30%〜	ALTER INDEX ... REBUILD

ただしページ数が 1000 未満のインデックスは無視してよい。

実装は自作せず、Ola Hallengren のメンテナンス スクリプト
（IndexOptimize）を使うのが実務では一般的である。
断片化率としきい値、統計更新、時間枠での打ち切りまで
まとめて面倒を見てくれる。

参考情報

• Rebuilding SQL Server indexes using the ONLINE option
  https://www.mssqltips.com/sqlservertip/2361/rebuilding-sql-server-indexes-using-the-online-option/

• Microsoft Learn

  • インデックス再構築と再構成の違い
    https://learn.microsoft.com/ja-jp/archive/blogs/jpsql/

  • SQL Server

    • オンライン インデックス操作のガイドライン
      https://learn.microsoft.com/ja-jp/sql/relational-databases/indexes/guidelines-for-online-index-operations

    • パフォーマンスを向上させ、リソース使用率を削減するために
      インデックスを最適に維持する
      https://learn.microsoft.com/ja-jp/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

---

Tags: 移行, データアクセス, SQL Server