セールス: 1-800-867-1380
展開 最小化

sys.dm_db_index_physical_stats

更新日: 2011年4月

このトピックの情報は現在使用できません。最新バージョンは、SQL 14 Transact-SQL Reference. でご覧いただけます。

Important重要
このトピックは保守されていません。現在のバージョンについては、「sys.dm_db_index_physical_stats」を参照してください。

指定したテーブルまたはビューのデータとインデックスに関する、サイズおよび断片化情報を返します。インデックスの場合、各パーティションの B ツリーのレベルごとに 1 行のデータが返されます。ヒープの場合、各パーティションの IN_ROW_DATA アロケーション ユニットごとに 1 行のデータが返されます。ラージ オブジェクト (LOB) データの場合、各パーティションの LOB_DATA アロケーション ユニットごとに 1 行のデータが返されます。テーブルに行オーバーフロー データが存在する場合、各パーティションの ROW_OVERFLOW_DATA アロケーション ユニットごとに 1 行のデータが返されます。xVelocity メモリが最適化された列ストア インデックスに関する情報は返されません。

Transact-SQL 構文表記規則

sys.dm_db_index_physical_stats( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

database_id | NULL | 0 | DEFAULT
データベースの ID を指定します。database_id のデータ型は smallint です。有効な入力値は、データベースの ID 番号、NULL、0、または DEFAULT です。既定値は 0 です。このコンテキストでは、NULL、0、および DEFAULT は等価です。

SQL Server のインスタンスのすべてのデータベースに関する情報を返すには NULL を指定します。database_id に NULL を指定する場合は、object_idindex_id、および partition_number にも NULL を指定する必要があります。

組み込み関数 DB_ID を指定できます。データベース名を指定しないで DB_ID を使用する場合、現在のデータベースの互換性レベルが 90 以上である必要があります。

object_id | NULL | 0 | DEFAULT
インデックスがあるテーブルまたはビューのオブジェクト ID を指定します。object_id のデータ型は int です。

有効な入力値は、テーブルおよびビューの ID 番号、NULL、0、または DEFAULT です。既定値は 0 です。このコンテキストでは、NULL、0、および DEFAULT は等価です。

NULL を指定すると、指定されたデータベース内にあるすべてのテーブルとビューに関する情報が返されます。object_id に NULL を指定する場合は、index_id および partition_number にも NULL を指定する必要があります。

index_id | 0 | NULL | -1 | DEFAULT
インデックスの ID を指定します。index_id のデータ型は int です。有効な入力値は、インデックスの ID 番号、NULL、-1、または DEFAULT です。object_id がヒープの場合は、0 も有効です。既定値は -1 です。このコンテキストでは、NULL、-1、および DEFAULT は等価です。

NULL を指定すると、ベース テーブルまたはビューのすべてのインデックスに関する情報が返されます。index_id に NULL を指定する場合は、partition_number にも NULL を指定する必要があります。

partition_number | NULL | 0 | DEFAULT
オブジェクト内のパーティション番号を指定します。partition_number のデータ型は int です。有効な入力値は、インデックスやヒープの partion_number、NULL、0、または DEFAULT です。既定値は 0 です。このコンテキストでは、NULL、0、および DEFAULT は等価です。

NULL を指定すると、所有するオブジェクトのすべてのパーティションに関する情報が返されます。

partition_number は 1 から始まります。非パーティション インデックスまたはヒープでは、partition_number が 1 に設定されます。

mode | NULL | DEFAULT
モードの名前を指定します。mode には統計の取得に使用されるスキャン レベルを指定します。mode のデータ型は sysname です。有効な入力値は DEFAULT、NULL、LIMITED、SAMPLED、DETAILED です。既定値 (NULL) は LIMITED です。

 

列名 データ型 説明

database_id

smallint

テーブルまたはビューのデータベース ID。

object_id

int

インデックスがあるテーブルまたはビューのオブジェクト ID。

index_id

int

インデックスのインデックス ID。

0 = ヒープ。

partition_number

int

所有するオブジェクト (テーブル、ビュー、インデックス) 内の 1 から始まるパーティション番号。

1 = パーティション分割されていないインデックスまたはヒープ。

index_type_desc

nvarchar(60)

インデックスの種類の説明。

HEAP

CLUSTERED INDEX

NONCLUSTERED INDEX

PRIMARY XML INDEX

SPATIAL INDEX

XML INDEX

alloc_unit_type_desc

nvarchar(60)

アロケーション ユニットの種類の説明。

IN_ROW_DATA

LOB_DATA

ROW_OVERFLOW_DATA

LOB_DATA アロケーション ユニットには、textntextimagevarchar(max)nvarchar(max)varbinary(max)xml 型の列に格納されているデータが含まれます。詳細については、「Data Types (Transact-SQL)」を参照してください。

ROW_OVERFLOW_DATA アロケーション ユニットには、行外に出された varchar(n)nvarchar(n)varbinary(n)sql_variant 型の列に格納されているデータが含まれます。

index_depth

tinyint

インデックス レベルの数です。

1 = ヒープ、LOB_DATA アロケーション ユニット、または ROW_OVERFLOW_DATA アロケーション ユニット。

index_level

tinyint

インデックスの現在のレベル。

インデックスのリーフ レベル、ヒープ、LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは 0 になります。

非リーフ インデックス レベルでは 0 より大きい数になります。index_level はインデックスのルート レベルで最大になります。

インデックスの非リーフ レベルは、mode = DETAILED の場合にのみ処理されます。

avg_fragmentation_in_percent

float

インデックスの論理的な断片化、または IN_ROW_DATA アロケーション ユニットでのヒープのエクステントの断片化。

値はパーセンテージ (%) で表され、複数のファイルが考慮されます。論理的な断片化とエクステントの断片化の定義については、「解説」を参照してください。

LOB_DATA および ROW_OVERFLOW_DATA アロケーション ユニットでは 0 になります。

ヒープでは、mode = SAMPLED の場合、NULL になります。

fragment_count

bigint

IN_ROW_DATA アロケーション ユニットのリーフ レベルでのフラグメントの数。フラグメントの詳細については、「解説」を参照してください。

インデックスの非リーフ レベル、および LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは NULL になります。

ヒープでは、mode = SAMPLED の場合、NULL になります。

avg_fragment_size_in_pages

float

IN_ROW_DATA アロケーション ユニットのリーフ レベルにおける、フラグメントあたりのページの平均数。

インデックスの非リーフ レベル、および LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは NULL になります。

ヒープでは、mode = SAMPLED の場合、NULL になります。

page_count

bigint

インデックス ページまたはデータ ページの合計数。

インデックスでは、IN_ROW_DATA アロケーション ユニットに含まれる B ツリーの現在のレベルにおけるインデックス ページの合計数になります。

ヒープでは、IN_ROW_DATA アロケーション ユニットに含まれるデータ ページの合計数になります。

LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは、アロケーション ユニットに含まれる合計ページ数になります。

avg_page_space_used_in_percent

float

すべてのページで、使用可能なデータ ストレージ領域のうち使用されているパーセンテージ (%) の平均。

インデックスでは、IN_ROW_DATA アロケーション ユニットに含まれる B ツリーの現在のレベルでの平均になります。

ヒープでは、IN_ROW_DATA アロケーション ユニットに含まれるすべてのデータ ページの平均になります。

LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは、アロケーション ユニットに含まれるすべてのページの平均になります。

mode = LIMITED の場合は NULL です。

record_count

bigint

レコードの総数。

インデックスでは、IN_ROW_DATA アロケーション ユニットに含まれる B ツリーの現在のレベルにおけるレコードの合計数になります。

ヒープでは、IN_ROW_DATA アロケーション ユニットに含まれるレコードの合計数になります。

noteメモ
ヒープでは、この関数から返されるレコード数が、ヒープに対して SELECT COUNT(*) を実行したときに返される行数と一致しない場合があります。これは、1 行に複数のレコードが含まれる場合があるためです。たとえば、更新の状況によっては、更新操作の結果として転送元レコードと転送先レコードが 1 つのヒープ行に含まれることがあります。また、大きな LOB 行のほとんどは、LOB_DATA ストレージ内で複数のレコードに分割されます。

LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは、アロケーション ユニット全体でのレコードの合計数になります。

mode = LIMITED の場合は NULL です。

ghost_record_count

bigint

アロケーション ユニットで、非実体クリーンアップ タスクによる削除の準備ができているゴースト レコードの数。

IN_ROW_DATA アロケーション ユニットに含まれるインデックスの非リーフ レベルでは 0 になります。

mode = LIMITED の場合は NULL です。

version_ghost_record_count

bigint

アロケーション ユニット内で、未処理のスナップショット分離トランザクションによって保持されているゴースト レコードの数。

IN_ROW_DATA アロケーション ユニットに含まれるインデックスの非リーフ レベルでは 0 になります。

mode = LIMITED の場合は NULL です。

min_record_size_in_bytes

int

最小レコード サイズ (バイト単位)。

インデックスでは、IN_ROW_DATA アロケーション ユニットに含まれる B ツリーの現在のレベルでの最小レコード サイズになります。

ヒープでは、IN_ROW_DATA アロケーション ユニットに含まれる最小レコード サイズになります。

LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは、アロケーション ユニット全体での最小レコード サイズになります。

mode = LIMITED の場合は NULL です。

max_record_size_in_bytes

int

最大レコード サイズ (バイト単位)。

インデックスでは、IN_ROW_DATA アロケーション ユニットに含まれる B ツリーの現在のレベルでの最大レコード サイズになります。

ヒープでは、IN_ROW_DATA アロケーション ユニットに含まれる最大レコード サイズになります。

LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは、アロケーション ユニット全体での最大レコード サイズになります。

mode = LIMITED の場合は NULL です。

avg_record_size_in_bytes

float

平均レコード サイズ (バイト単位)。

インデックスでは、IN_ROW_DATA アロケーション ユニットに含まれる B ツリーの現在のレベルでの平均レコード サイズになります。

ヒープでは、IN_ROW_DATA アロケーション ユニットに含まれる平均レコード サイズになります。

LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは、アロケーション ユニット全体での平均レコード サイズになります。

mode = LIMITED の場合は NULL です。

forwarded_record_count

bigint

別のデータの場所への転送ポインターを持つ、ヒープ内の転送されたレコード数 (この状態は、更新中に、新しい行を格納できる十分なスペースが元の場所にない場合に発生します)。

ヒープの IN_ROW_DATA アロケーション ユニット以外のアロケーション ユニットでは NULL になります。

ヒープでは、mode = LIMITED の場合、NULL になります。

compressed_page_count

bigint

圧縮されたページ数。

  • ヒープの場合、新しく割り当てられたページはページ圧縮されません。ヒープは、2 つの特殊な条件の下でページ圧縮されます。1 つはデータが一括インポートされた場合で、もう 1 つはヒープが再構築された場合です。ページ割り当ての原因となる通常の DML 操作ではページ圧縮されません。compressed_page_count の値が目標のしきい値を超えた場合は、ヒープを再構築してください。

  • クラスター化インデックスを含むテーブルの場合、compressed_page_count の値はページ圧縮の効果を示します。

sys.dm_db_index_physical_stats 動的管理関数は、DBCC SHOWCONTIG ステートメントの代わりに使用できます。この動的管理関数には、CROSS APPLY および OUTER APPLY からの相関パラメーターを指定できません。

関数が実行されるモードによって、関数で使用する統計データを取得するためのスキャンのレベルが決まります。mode は LIMITED、SAMPLED、DETAILED のいずれかになります。この関数は、ページ チェーンをスキャンすることにより、テーブルまたはインデックスの指定パーティションを構成するアロケーション ユニットを検索します。sys.dm_db_index_physical_stats には実行するモードに関係なく、インテント共有 (IS) テーブル ロックのみが必要です。

LIMITED モードは最も高速なモードで、スキャンするページ数は最小です。インデックスでは、B ツリーの親レベルのページ (リーフ レベルより上のページ) だけがスキャンされます。ヒープでは、関連付けられた PFS ページおよび IAM ページが調べられます。LIMITED モードではヒープのデータ ページがスキャンされます。

LIMITED モードでは、は B ツリーの非リーフ ページとヒープの IAM および PFS ページのみをスキャンするので、compressed_page_count は NULL です。compressed_page_count の推定値を取得するには SAMPLED モードを使用し、compressed_page_count の実際の値を取得するには DETAILED モードを使用します。SAMPLED モードでは、インデックスまたはヒープの全ページの 1% のサンプルに基づく統計情報が返されます。SAMPLED モードの結果は近似と見なす必要があります。インデックスまたはヒープのページが 10,000 ページに満たない場合は、SAMPLED モードの代わりに DETAILED モードが使用されます。

DETAILED モードではすべてのページがスキャンされ、すべての統計が返されます。

各モードで実行される処理は LIMITED、SAMPLED、DETAILED の順に増加し、それに応じて処理時間は遅くなります。テーブルまたはインデックスの断片化レベルのサイズをすばやく計測するには、LIMITED モードを使用します。LIMITED モードは最も高速で、インデックスの IN_ROW_DATA アロケーション ユニットにおける各非リーフ レベルの行は返されません。

断片化は、テーブルとテーブルに定義されたインデックスに対して、INSERT、UPDATE、DELETE ステートメントによるデータ変更が行われる過程で発生します。これらの変更は、テーブルとインデックスのすべての行に均等に分散されるわけではないので、時間が経つにつれ各ページのゆとりに差が生じることになります。このような断片化があるときに、クエリでテーブルのインデックスの一部または全部をスキャンしようとすると、余分なページの読み取りが必要になり、データの並列スキャンの妨げになります。

インデックスまたはヒープの断片化レベルは、avg_fragmentation_in_percent 列で確認できます。ヒープの場合、この列の値はヒープのエクステントの断片化を表します。インデックスの場合、この値はインデックスの論理的な断片化を表します。DBCC SHOWCONTIG とは異なり、どちらの場合の断片化計算アルゴリズムでも複数のファイルにまたがるストレージが考慮されており、正確な計算値が得られます。

論理的な断片化

これは、インデックスのリーフ ページでの順序が不正なページの割合です。順序が不正なページとは、インデックスに割り当てられている次の物理的なページと、現在のリーフ ページの次ページ ポインターが示すページが異なるページのことです。

エクステントの断片化

これは、ヒープのリーフ ページでの順序が不正なエクステントの割合です。順序が無効なエクステントとは、ヒープの現在のページを含むエクステントの物理的な位置が、前のページを含むエクステントの直後でない状態のエクステントを指します。

最大のパフォーマンスを得るには、avg_fragmentation_in_percent の値をできるだけ 0 に近くする必要があります。ただし、0 ~ 10% の値は許容範囲です。再構築、再構成、再作成など、断片化を解消するためのさまざまな手段を使用することによって、この値を下げることができます。インデックス内の断片化度を分析する方法の詳細については、「Reorganizing and Rebuilding Indexes」を参照してください。

クエリのパフォーマンスに影響が出るほどインデックスが断片化している場合、断片化を解消するには 3 つの方法があります。

  • クラスター化インデックスを削除し、再作成する。

    クラスター化インデックスを再作成すると、データが再分配され、データ ページにデータが満たされます。ページのゆとりのレベルは、CREATE INDEX の FILLFACTOR オプションを使用して構成できます。この方法の短所は、削除と再作成が行われている間、インデックスがオフラインになることと、操作がアトミックであることです。インデックスの作成が中断されると、インデックスは再作成されません。詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。

  • DBCC INDEXDEFRAG の代わりの ALTER INDEX REORGANIZE を使用して、インデックスのリーフ レベル ページを論理順序の順に並べ替える。これはオンライン操作のため、ステートメントの実行中もインデックスを使用できます。操作を中断しても、それまでに完了した作業は失われません。この方法の短所は、インデックスの再構築操作ほど、データの再構成が適切に行われず、統計も更新されないことです。

  • DBCC DBREINDEX の代わりの ALTER INDEX REBUILD を使用して、オンラインまたはオフラインでインデックスを再構築する。詳細については、「ALTER INDEX (Transact-SQL)」を参照してください。

断片化だけでは、インデックスを再構成または再構築する十分な理由にはなりません。断片化の影響は主に、インデックスのスキャン中にページの先行読み取りのスループットが減速することです。これは、応答時間の遅れの原因となります。断片化したテーブルまたはインデックスでのクエリのワークロードにスキャンが関係していない場合、ワークロードは主に単一参照のため、断片化を解消しても効果はありません。詳細については、Microsoft Web サイトを参照してください。

noteメモ
DBCC SHRINKFILE または DBCC SHRINKDATABASE を実行した場合、圧縮操作中にインデックスが部分的または完全に移動すると、断片化が発生することがあります。このため、圧縮操作を実行する必要がある場合は、断片化の解消前に行ってください。

ヒープのエクステントの断片化を解消するには、テーブルにクラスター化インデックスを作成し、インデックスを削除します。これによって、クラスター化インデックスの作成中にデータが再分配されます。この操作では、データベースの空き領域の分布を考慮に入れて、可能な限り最適化も行われます。この後、ヒープを再作成するためにクラスター化インデックスを削除しても、データは移動せず、最適な状態が保たれます。この操作の実行方法については、「CREATE INDEX」および「DROP INDEX」を参照してください。

Caution注意
テーブルでクラスター化インデックスを作成して削除すると、そのテーブルのすべての非クラスター化インデックスが 2 回再構築されます。

既定では、ALTER INDEX REORGANIZE ステートメントを実行すると、ラージ オブジェクト (LOB) データを含むページが圧縮されます。LOB ページは空の場合でも割り当てを解除されないので、多くの LOB データまたは LOB 列が削除された場合は、このデータを圧縮することでディスク領域の使用が改善されます。

特定のクラスター化インデックスを再構成すると、そのクラスター化インデックスに含まれるすべての LOB 列が圧縮されます。非クラスター化インデックスを再構成すると、そのインデックス内で非キー列 (付加列) となっているすべての LOB 列が圧縮されます。ステートメントで ALL を指定した場合は、指定したテーブルまたはビューに関連付けられているすべてのインデックスが再構成されます。さらに、クラスター化インデックス、基になるテーブル、または付加列を含む非クラスター化インデックスに関連付けられているすべての LOB 列も圧縮されます。

avg_page_space_used_in_percent 列には、ページのゆとりが示されます。ディスク領域の使用を最適にするには、ランダムな挿入があまり行われないインデックスの場合はこの値を 100% に近づけます。一方、ランダムな挿入が多く行われ、いっぱいになったページが多いインデックスの場合はページ分割数が増えるため、断片化が大きくなります。したがって、ページ分割数を減らすには、値を 100% よりも少なくする必要があります。FILLFACTOR オプションを指定してインデックスを再構築すると、ページのゆとりをインデックスのクエリ パターンに合わせて変更できます。FILL FACTOR の詳細については、「Fill Factor」を参照してください。また、ALTER INDEX REORGANIZE は最後に指定された FILLFACTOR までページが埋まるよう、インデックスを圧縮します。これにより、avg_space_used_in_percent の値が増加します。ALTER INDEX REORGANIZE はページのゆとりを調整できないことに注意してください。代わりに、インデックスの再構築を実行する必要があります。

フラグメントは、1 つのアロケーション ユニットに含まれる同一ファイル内の、物理的に連続するリーフ ページで構成されます。1 つのインデックスには少なくとも 1 つのフラグメントが含まれます。インデックスが持つことのできるフラグメントの最大数は、インデックスのリーフ レベルのページ数と同じです。フラグメントが大きいほど、同じ数のページの読み取りに必要なディスクの I/O が少なくなります。したがって、avg_fragment_size_in_pages 値が大きいほど、範囲スキャンのパフォーマンスは向上します。avg_fragment_size_in_pages と avg_fragmentation_in_percent の値は、互いに反比例します。したがって、インデックスを再構築または再構成すると、断片化が解消し、フラグメントのサイズが大きくなります。

次の権限が必要です。

  • データベース内の指定したオブジェクトに対する CONTROL 権限。

  • オブジェクトのワイルドカード @object_id= NULL を使用して、特定のデータベース内にあるすべてのオブジェクトに関する情報を返す VIEW DATABASE STATE 権限。

VIEW DATABASE STATE 権限を許可すると、特定のオブジェクトに対して CONTROL 権限が拒否されていたとしても、データベース内のすべてのオブジェクトを取得することができます。

VIEW DATABASE STATE 権限を拒否すると、特定のオブジェクトに対する CONTROL 権限が許可されていたとしても、そのデータベース内のどのオブジェクトも取得できません。また、データベースのワイルドカード @database_id=NULL を指定した場合、データベースは省略されます。

詳細については、「Dynamic Management Objects」を参照してください。

この情報は役に立ちましたか。
(残り 1500 文字)
フィードバックをいただき、ありがとうございました
表示:
© 2014 Microsoft