インデックスの再構築・デフラグのバックアップ(No.5)

- スキャンされたページ数...........................	: 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」ステートメントの出力結果の分析 †

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

↑

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

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

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

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

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

↑

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

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

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

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

↑

「インデックスの断片化」の修正 †

↑

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

テーブル、インデックスの構造や制約がわからない場合でも、
１つのステートメントでテーブルの全ての「再構築」ができるため、
複数の「DROP INDEX」と「CREATE INDEX」を使用して「再構築」するより簡単。
同時に、オプティマイザの使用する統計が更新される。
オプションで「ページ密度」を変更することができる。

↑

再構成 (alter index reorganize) †

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

↑

比較 †

インデックス再構築と再構成の違い | Microsoft SQL Server Japan Support Team Blog
https://blogs.msdn.microsoft.com/jpsql/2013/02/28/977/

↑

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

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

↑

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

「再構築」
- インデックスの「ページ」のイメージをトランザクションログに記録する。
- このため、トランザクションログ領域を大量に使用することがある。
- 必要なトランザクションログ領域は、大まかに見積もって、「ページ」数×8 KBとなる。
- 「ページ」数は、「DBCC SHOWCONTIG」ステートメントで確認できる。

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

「最適化」
- 一般的に、「最適化」のトランザクションログ領域の使用量は、
  「再構築」のトランザクションログ領域の使用量より少量になる。ただし、「最適化」処理の作業量による。

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

↑

その他、注意事項 †

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

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

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

インデックスの再構築・デフラグ のバックアップ(No.5)

目次 †

概要 †

「インデックスの断片化」レベルの監視 †

「DBCC SHOWCONTIG」ステートメントを使用した、「インデックスの断片化」レベルの監視 †

「DBCC SHOWCONTIG」ステートメントの実行結果 †