Open棟梁Project - マイクロソフト系技術情報 Wiki
目次 †
概要 †
インデックスの断片化を解消し、ディスクI/Oを減らす。
「インデックスの断片化」レベルの監視 †
「DBCC SHOWCONTIG」ステートメントで、「インデックスの断片化」を特定できる。
(DBCC SHOWCONTIGは削除予定なので、sys.dm_db_index_physical_stats を使用する)
「DBCC SHOWCONTIG」ステートメントを使用した、「インデックスの断片化」レベルの監視 †
- 「DBCC SHOWCONTIG」ステートメントを使用することで、
「インデックスの断片化」レベルを監視し、断片化の進んだインデックスを特定できる。
- 「DBCC SHOWCONTIG」ステートメントの「TABLERESULTS」オプションを使用すると、情報を行セットとして返すので、
これをテーブルに定期的に書き込めば、時系列に「インデックスの断片化」レベルを記録、監視できる。
- 負荷の高いサーバで「DBCC SHOWCONTIG」ステートメントを実行するときは、「WITH FAST」オプションを使用し、
インデックスの「リーフ ページ」がスキャンされないようにして、性能を向上できる。ただし、「ページ密度」の測定はできない。
「DBCC SHOWCONTIG」ステートメントの実行結果 †
「DBCC SHOWCONTIG」ステートメントの出力結果の分析 †
ここでは、「DBCC SHOWCONTIG」ステートメントで出力した情報から、
「インデックスの断片化」レベルを確認する2つの方法について説明する。
「スキャン密度(%)」で確認 †
- 「ページ分割」が発生すると、
分割された一部の「リーフ レベル ページ」が、別の「エクステント」に格納されることがあるので、
「インデックスの断片化」が進むと、正味のデータ量の割に「エクステント」の数が大きくなる。
- この問題は、「スキャン密度(%)」で判断することができる。
「スキャン密度(%)」が小さくなった場合、「インデックスの断片化」が進んでいることを示す。
- 「スキャン密度(%)」は、次の式で算出される。
値 | 説明(式) |
スキャン密度(%) | 最善のエクステント変更回数 / 現在のエクステント変更回数 * 100 |
最善のエクステント変更回数 | すべての「ページ」が連続的にリンクされる場合、スキャン処理によってエクステントが変更される回数 |
現在のエクステント変更回数 | 実際のスキャン処理を実行してエクステントが変更された回数 |
「論理スキャン フラグメンテーション」で確認 †
- 「クラスタ化インデックス」の場合、
「リーフ レベル ページ」の「データ ページ」中のデータが物理的に順序正しく並べられる。
しかし、「ページ分割」が発生すると、分割された一部の「データ ページ」が、
物理的に離れた位置に格納されることがあるので、順序が不正な「データ ページ」の割合が増える。
- この問題は、「論理スキャン フラグメンテーション」で判断することができる。
この値が大きくなった場合、「インデックスの断片化」が進んでいることを示す。
この値はできるだけ0%に近い値にする。0 ~ 10%が許容範囲であり、
これを超えると、「インデックス スキャン」の性能が低下する可能性がある。
値 | 説明(式) |
論理スキャン フラグメンテーション(%) | 物理的に順序正しく並べられていない「リーフ レベル ページ」の割合 |
エクステント スキャン フラグメンテーション(%) | 物理的に離れた位置にある「エクステント」の割合 |
「インデックスの断片化」の修正 †
インデックスの再構築 (alter index rebuild) †
- テーブル、インデックスの構造や制約がわからない場合でも、
1つのステートメントでテーブルの全ての「再構築」ができるため、
複数の「DROP INDEX」と「CREATE INDEX」を使用して「再構築」するより簡単。
- 同時に、オプティマイザの使用する統計が更新される。
- オプションで「ページ密度」を変更することができる。
再構成 (alter index reorganize) †
- 「リーフ レベル ページ」を物理的に順序正しく並べ替え、「最適化」する。
- これにより、「インデックス スキャン」の性能が向上する。
- ただし、この方法は「再構築」の操作よりも劣る。
- 期待した効果が得られない場合は、「再構築」が必要になる。
比較 †
必要なデータ領域の空き容量 †
- 「再構築」には、「データ ファイル」に十分な空き容量が必要になる。
- 必要な空き領域は、再構築するインデックス量に応じて変化する。
- 「クラスタ化インデックス」の場合、
「必要な空き領域 = 1.2 * (平均行サイズ) * (行数)」が目安となる。
トランザクション ログ量の比較 †
- 「再構築」
- インデックスの「ページ」のイメージをトランザクション ログに記録する。
- このため、トランザクション ログ領域を大量に使用することがある。
- 必要なトランザクション ログ領域は、大まかに見積もって、「ページ」数×8 KBとなる。
- 「ページ」数は、「DBCC SHOWCONTIG」ステートメントで確認できる。
- ただし、「再構築」のトランザクション ログは、
「一括ログ復旧モデル」では記録されないので、必要に応じて「復旧モデル」を変更する。
- 「最適化」
- 一般的に、「最適化」のトランザクション ログ領域の使用量は、
「再構築」のトランザクション ログ領域の使用量より少量になる。ただし、「最適化」処理の作業量による。
- 「最適化」のトランザクション ログは、「一括ログ復旧モデル」でも記録される。
その他、注意事項 †
- 旧式のI/Oサブシステムを使用している環境では、
「インデックスの断片化」を修正する前に「ディスクの断片化」を修正する。
- SANを使用している環境では、「ディスクの断片化」を修正する必要はない。
- 「インデックスの断片化」はI/O処理能力に影響するため、
「インデックス ページ」、「データ ページ」がデータ キャッシュ内に存在するクエリの性能には影響を与えない。
- SANでは、一般的に大容量のデータ キャッシュが提供されるため、小規模環境に比べると、影響を受け難い。
Tags: :データアクセス, :SQL Server