sys.dm_db_index_physical_stats

[アーティクル]
12/16/2008

更新 : 2007 年 9 月 15 日

指定したテーブルまたはビューのデータとインデックスに関する、サイズおよび断片化情報を返します。インデックスの場合、各パーティションの B-Tree のレベルごとに 1 行のデータが返されます。ヒープの場合、各パーティションの IN_ROW_DATA アロケーションユニットごとに 1 行のデータが返されます。ラージオブジェクト (LOB) データの場合、各パーティションの LOB_DATA アロケーションユニットごとに 1 行のデータが返されます。テーブルに行オーバーフローデータが存在する場合、各パーティションの ROW_OVERFLOW_DATA アロケーションユニットごとに 1 行のデータが返されます。アロケーションユニットとパーティションの詳細については、「テーブルとインデックスのアーキテクチャ」を参照してください。

トピックリンクアイコン 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_id、index_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

XML INDEX

alloc_unit_type_desc

nvarchar(60)

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

IN_ROW_DATA

LOB_DATA

ROW_OVERFLOW_DATA

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

ROW_OVERFLOW_DATA アロケーションユニットには、行外に出された varchar(n)、nvarchar(n)、varbinary(n)、sql_variant 型の列に格納されているデータが含まれます。詳細については、「8 KB を超える場合の行オーバーフローデータ」を参照してください。

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-Tree の現在のレベルにおけるインデックスページの合計数になります。

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

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

avg_page_space_used_in_percent

float

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

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

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

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

mode = LIMITED の場合は NULL です。

record_count

bigint

レコードの総数。

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

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

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

mode = LIMITED の場合は NULL です。

ms188917.note(ja-jp,SQL.90).gif メモ :

ヒープでは、この関数から返されるレコード数が、ヒープに対して SELECT COUNT(*) を実行したときに返される行数と一致しない場合があります。これは、1 つの行に複数のレコードが含まれている可能性があるためです。たとえば、更新を実行している状況では、1 つのヒープ行に前方向レコードと、更新操作の結果として転送されたレコードがそれぞれ 1 つずつ含まれる場合があります。また、多くの大きな LOB 行は、LOB_DATA ストレージ内の複数のレコードに分割されます。

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-Tree の現在のレベルに適用される最小レコードサイズになります。

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

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

mode = LIMITED の場合は NULL です。

max_record_size_in_bytes

int

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

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

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

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

mode = LIMITED の場合は NULL です。

avg_record_size_in_bytes

float

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

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

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

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

mode = LIMITED の場合は NULL です。

forwarded_record_count

bigint

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

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

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

解説

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

スキャンモード

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

LIMITED モードは最も高速なモードで、スキャンするページ数は最小です。このモードでは、ヒープのすべてのページがスキャンされますが、インデックスに対してはリーフレベルの上位ページである親レベルのページだけがスキャンされます。

SAMPLED モードでは、インデックスまたはヒープの全ページのうち、1% のサンプルに基づく統計が返されます。インデックスまたはヒープのページが 10,000 ページに満たない場合は、SAMPLED モードの代わりに DETAILED モードが使用されます。

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

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

システム関数によるパラメータ値の指定

Transact-SQL 関数 DB_ID および OBJECT_ID を使用して、database_id および object_id パラメータの値を指定できます。ただし、これらの関数に無効な値を渡すと、意図しない結果が生じる可能性があります。たとえば、データベースまたはオブジェクト名が存在しないか、スペルが間違っていることが原因で見つからない場合は、両方の関数で NULL が返されます。sys.dm_db_index_physical_stats 関数では、NULL 値はすべてのデータベースまたはすべてのオブジェクトを指定するワイルドカード値として解釈されます。

また、OBJECT_ID 関数は、sys.dm_db_index_physical_stats 関数の呼び出し前に処理されます。したがって、database_id で指定したデータベースではなく、現在のデータベースのコンテキストで評価されます。この動作により、OBJECT_ID 関数で NULL 値が返される場合があります。または、オブジェクト名が現在のデータベースのコンテキストと指定したデータベースの両方に存在する場合は、エラーメッセージが返されることがあります。次の例は、こうした意図しない結果を示すものです。

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database. 
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the 
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

推奨事項

DB_ID または OBJECT_ID を使用する場合は、必ず有効な ID が返されるようにしてください。たとえば、OBJECT_ID を使用する場合は、OBJECT_ID(N'AdventureWorks.Person.Address') のように 3 部構成の名前を指定するか、関数で返される値を sys.dm_db_index_physical_stats 関数で使用する前にテストします。後の例 A と例 B は、データベース ID とオブジェクト ID を安全に指定する方法を示しています。

断片化の検出

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

SQL Server 2005 では、SQL Server 2000 よりも断片化計算のアルゴリズムが正確になりました。この結果、断片化の値は以前より高く表示されます。たとえば SQL Server 2000 では、テーブルの 11 ページと 13 ページが同じエクステントにあり、12 ページがない場合、そのテーブルは断片化しているとはみなされません。しかし、これら 2 ページにアクセスするには 2 回の物理的な I/O 操作が必要になります。したがって、SQL Server 2005 ではこの状態は断片化とみなされます。

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

論理的な断片化

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

エクステントの断片化

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

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

インデックスの断片化の解消

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

クラスタ化インデックスを削除し、再作成する。
クラスタ化インデックスを再作成すると、データが再分配され、データページにデータが満たされます。ページのゆとりのレベルは、CREATE INDEX の FILLFACTOR オプションを使用して構成できます。この方法の短所は、削除と再作成のサイクルの間インデックスがオフラインになることと、操作がアトミックであることです。インデックスの作成が中断されると、インデックスは再作成されません。詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。
DBCC INDEXDEFRAG の代わりの ALTER INDEX REORGANIZE を使用して、インデックスのリーフレベルページを論理順序の順に並べ替える。これはオンライン操作のため、ステートメントの実行中もインデックスを使用できます。操作を中断しても、それまでに完了した作業は失われません。この方法の短所は、インデックスの再構築操作ほど、データの再構成が適切に行われず、統計も更新されないことです。
DBCC DBREINDEX の代わりの ALTER INDEX REBUILD を使用して、オンラインまたはオフラインでインデックスを再構築する。詳細については、「ALTER INDEX (Transact-SQL)」を参照してください。

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

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

ヒープの断片化の解消

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

ラージオブジェクトデータの圧縮

既定では、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 権限。
データベースのワイルドカード @database_id = NULL を使用して、すべてのデータベースに関する情報を返す場合は、VIEW SERVER STATE 権限。

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

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

詳細については、「動的管理ビューと動的管理関数」を参照してください。

例

A. 指定したテーブルの情報を返す

次の例では、AdventureWorks データベース内にある Person.Address テーブルのすべてのインデックスとパーティションについて、サイズとフラグメントの統計を返します。パフォーマンスを向上させるには、スキャンモードを 'LIMITED' に設定し、返される統計を制限します。このクエリを実行するには、少なくとも Person.Address テーブルに対する CONTROL 権限が必要です。

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B. ヒープに関する情報を返す

次の例では、AdventureWorks データベースのヒープ dbo.DatabaseLog に関するすべての統計を返します。テーブルには LOB データが含まれているため、ヒープのデータページに格納されている IN_ROW_ALLOCATION_UNIT アロケーションユニットの行だけではなく、LOB_DATA アロケーションユニットの行も返されます。このクエリを実行するには、少なくとも dbo.DatabaseLog テーブルに対する CONTROL 権限が必要です。

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.dbo.DatabaseLog');
IF @object_id IS NULL 
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

C. すべてのデータベースに関する情報を返す

次の例では、すべてのパラメータでワイルドカード NULL を指定して、SQL Server インスタンス内のすべてのテーブルとインデックスに関する統計を返します。このクエリを実行するには、VIEW SERVER STATE 権限が必要です。

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

D. sys.dm_db_index_physical_stats を使用してインデックスを再構築または再構成する

次の例では、平均断片化が 10% を超えるデータベース内のすべてのパーティションを、自動的に再構成または再構築します。このクエリを実行するには、VIEW DATABASE STATE 権限が必要です。この例では、データベース名を指定せずに DB_ID を 1 番目のパラメータとして指定しています。この場合、現在のデータベースの互換性レベルが 80 以下であるとエラーが生成されます。このエラーを解決するには、DB_ID() を有効なデータベース名に置き換えます。データベースの互換性レベルの詳細については、「sp_dbcmptlevel (Transact-SQL)」を参照してください。

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

参照

その他の技術情報

テーブルとインデックスの編成

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース	履歴
2007 年 9 月 15 日	変更内容 record_count に、返されるレコード数がヒープ内の行数と一致しない場合の理由を説明する注を追加しました。 forwarded_record_count の定義を改善しました。
2006 年 7 月 17 日	新しい内容「断片化の検出」に、断片化の計算アルゴリズムに関する情報を追加。最初のパラメータで DB_ID() を使用するときの、データベースの互換性レベルの要件を明記。
2006 年 4 月 14 日	変更内容 : database_id、object_id,、および partition_number の構文の入力値として、0 および DEFAULT を追加。 index_id の構文の入力値として、-1 および DEFAULT を追加。例 D を修正。
2005 年 12 月 5 日	新しい内容 : 「システム関数によるパラメータ値の指定」を追加。変更内容 : 「権限」の説明を修正。論理的な断片化の説明を修正。

sys.dm_db_index_physical_stats

構文

引数

返されるテーブル

解説

スキャンモード

システム関数によるパラメータ値の指定

推奨事項

断片化の検出

インデックスの断片化の解消

ヒープの断片化の解消

ラージオブジェクトデータの圧縮

ディスク領域の使用状況の評価

インデックスのフラグメントの評価

権限

例

A. 指定したテーブルの情報を返す

B. ヒープに関する情報を返す

C. すべてのデータベースに関する情報を返す

D. sys.dm_db_index_physical_stats を使用してインデックスを再構築または再構成する

参照

関連項目

その他の技術情報

ヘルプおよび情報

変更履歴

その他のリソース

sys.dm_db_index_physical_stats

構文

引数

返されるテーブル

解説

スキャン モード

システム関数によるパラメータ値の指定

推奨事項

断片化の検出

インデックスの断片化の解消

ヒープの断片化の解消

ラージ オブジェクト データの圧縮

ディスク領域の使用状況の評価

インデックスのフラグメントの評価

権限

例

A. 指定したテーブルの情報を返す

B. ヒープに関する情報を返す

C. すべてのデータベースに関する情報を返す

D. sys.dm_db_index_physical_stats を使用してインデックスを再構築または再構成する

参照

関連項目

その他の技術情報

ヘルプおよび情報

変更履歴

その他のリソース

スキャンモード

ラージオブジェクトデータの圧縮