sys.dm_db_index_physical_stats (TRANSACT-SQL)sys.dm_db_index_physical_stats (Transact-SQL)

適用対象: ○SQL Server ○Azure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server指定されたテーブルまたはビューのデータとインデックスのサイズと断片化に関する情報を返します。Returns size and fragmentation information for the data and indexes of the specified table or view in SQL ServerSQL Server. インデックスの場合、各パーティションの B ツリーのレベルごとに 1 行のデータが返されます。For an index, one row is returned for each level of the B-tree in each partition. ヒープの場合、各パーティションの IN_ROW_DATA アロケーション ユニットごとに 1 行のデータが返されます。For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. ラージオブジェクト (LOB) データの場合、各パーティションの LOB_DATA アロケーションユニットに対して1行が返されます。For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. テーブルに行オーバーフロー データが存在する場合、各パーティションの ROW_OVERFLOW_DATA アロケーション ユニットごとに 1 行のデータが返されます。If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition. XVelocity メモリ最適化列ストアインデックスに関する情報は返されません。Does not return information about xVelocity memory optimized columnstore indexes.

重要

読み取り可能なセカンダリレプリカAlways On をホストしているサーバーインスタンスで、このクエリを実行すると、再実行のブロックの問題が発生する可能性があります。If you query sys.dm_db_index_physical_stats on a server instance that is hosting an Always On readable secondary replica, you might encounter a REDO blocking issue. これは、この動的管理ビューが、指定したユーザー テーブルまたはビューで IS ロックを獲得することが原因です。IS ロックは、そのユーザー テーブルまたはビューの X ロックに関して REDO スレッドの要求をブロックする可能性があります。This is because this dynamic management view acquires an IS lock on the specified user table or view that can block requests by a REDO thread for an X lock on that user table or view.

sys.dm_db_index_physical_statsは、メモリ最適化インデックスに関する情報を返しません。sys.dm_db_index_physical_stats does not return information about memory-optimized indexes. メモリ最適化インデックスの使用方法については、sys.dm_db_xtp_index_stats (Transact-SQL) を参照してください。For information about memory-optimized index use, see sys.dm_db_xtp_index_stats (Transact-SQL).

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

  
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 }  
)  

引数Arguments

database_id | NULL | 0 | DEFAULTdatabase_id | NULL | 0 | DEFAULT
データベースの ID を示します。Is the ID of the database. database_idsmallintです。database_id is smallint. 有効な入力値は、データベースの ID 番号、NULL、0、または DEFAULT です。Valid inputs are the ID number of a database, NULL, 0, or DEFAULT. 既定値は 0 です。The default is 0. このコンテキストでは、NULL、0、および DEFAULT は同じ値になります。NULL, 0, and DEFAULT are equivalent values in this context.

SQL ServerSQL Server のインスタンスのすべてのデータベースに関する情報を返すには NULL を指定します。Specify NULL to return information for all databases in the instance of SQL ServerSQL Server. Database_idに null を指定する場合は、 object_idindex_id、およびpartition_numberにも null を指定する必要があります。If you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number.

組み込み関数DB_IDを指定できます。The built-in function DB_ID can be specified. データベース名を指定しないで DB_ID を使用する場合、現在のデータベースの互換性レベルが 90 以上である必要があります。When using DB_ID without specifying a database name, the compatibility level of the current database must be 90 or greater.

object_id | NULL | 0 | DEFAULTobject_id | NULL | 0 | DEFAULT
インデックスがあるテーブルまたはビューのオブジェクト ID を示します。Is the object ID of the table or view the index is on. object_IDintです。object_id is int.

有効な入力値は、テーブルおよびビューの ID 番号、NULL、0、または DEFAULT です。Valid inputs are the ID number of a table and view, NULL, 0, or DEFAULT. 既定値は 0 です。The default is 0. このコンテキストでは、NULL、0、および DEFAULT は同じ値になります。NULL, 0, and DEFAULT are equivalent values in this context. SQL Server 2016 (13.x)SQL Server 2016 (13.x)以降、有効な入力には、service broker のキュー名またはキューの内部テーブル名も含まれます。As of SQL Server 2016 (13.x)SQL Server 2016 (13.x), valid inputs also include the service broker queue name or the queue internal table name. 既定のパラメーター (つまり、すべてのオブジェクト、すべてのインデックスなど) が適用されると、すべてのキューの断片化情報が結果セットに含まれます。When default parameters are applied (i.e. all objects, all indexes, etc), fragmentation information for all queues are included in the result set.

NULL を指定すると、指定されたデータベース内にあるすべてのテーブルとビューに関する情報が返されます。Specify NULL to return information for all tables and views in the specified database. Object_idに null を指定する場合は、 index_idpartition_numberにも null を指定する必要があります。If you specify NULL for object_id, you must also specify NULL for index_id and partition_number.

index_id | 0 | NULL | -1 | DEFAULTindex_id | 0 | NULL | -1 | DEFAULT
インデックスの ID です。Is the ID of the index. index_idintです。有効な入力値は、インデックスの ID 番号です。 object_idがヒープ、NULL、-1、または DEFAULT の場合は0です。index_id is int. Valid inputs are the ID number of an index, 0 if object_id is a heap, NULL, -1, or DEFAULT. 既定値は-1 です。The default is -1. このコンテキストでは、NULL、-1、および DEFAULT は同じ値です。NULL, -1, and DEFAULT are equivalent values in this context.

NULL を指定すると、ベーステーブルまたはビューのすべてのインデックスに関する情報が返されます。Specify NULL to return information for all indexes for a base table or view. Index_idに null を指定する場合は、 PARTITION_NUMBERにも null を指定する必要があります。If you specify NULL for index_id, you must also specify NULL for partition_number.

partition_number | NULL | 0 | DEFAULTpartition_number | NULL | 0 | DEFAULT
オブジェクトのパーティション番号です。Is the partition number in the object. partition_numberintです。有効な入力値は、インデックスまたはヒープのpartion_number 、NULL、0、または DEFAULT です。partition_number is int. Valid inputs are the partion_number of an index or heap, NULL, 0, or DEFAULT. 既定値は 0 です。The default is 0. このコンテキストでは、NULL、0、および DEFAULT は同じ値になります。NULL, 0, and DEFAULT are equivalent values in this context.

NULL を指定すると、所有するオブジェクトのすべてのパーティションに関する情報が返されます。Specify NULL to return information for all partitions of the owning object.

partition_numberは1から始まるです。partition_number is 1-based. 非パーティションインデックスまたはヒープのpartition_numberが1に設定されています。A nonpartitioned index or heap has partition_number set to 1.

mode | NULL | DEFAULTmode | NULL | DEFAULT
モードの名前を指定します。Is the name of the mode. モード統計を取得するために使用するスキャンレベルを指定します。mode specifies the scan level that is used to obtain statistics. モードsysnameです。mode is sysname. 有効な入力値は DEFAULT、NULL、LIMITED、SAMPLED、DETAILED です。Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. 既定値 (NULL) は制限されています。The default (NULL) is LIMITED.

返されるテーブルTable Returned

列名Column name データ型Data type 説明Description
database_iddatabase_id smallintsmallint テーブルまたはビューのデータベース ID。Database ID of the table or view.
object_idobject_id intint インデックスがあるテーブルまたはビューのオブジェクト ID。Object ID of the table or view that the index is on.
index_idindex_id intint インデックスのインデックス ID。Index ID of an index.

0 = ヒープ。0 = Heap.
partition_numberpartition_number intint 所有しているオブジェクト内の1から始まるパーティション番号。テーブル、ビュー、またはインデックスです。1-based partition number within the owning object; a table, view, or index.

1 = パーティション分割されていないインデックスまたはヒープ。1 = Nonpartitioned index or heap.
index_type_descindex_type_desc nvarchar(60)nvarchar(60) インデックスの種類の説明。Description of the index type:

HEAPHEAP

クラスター化インデックスCLUSTERED INDEX

NONCLUSTERED INDEXNONCLUSTERED INDEX

プライマリ XML インデックスPRIMARY XML INDEX

EXTENDED INDEXEXTENDED INDEX

XML INDEXXML INDEX

列ストアマッピングインデックス (内部)COLUMNSTORE MAPPING INDEX (internal)

列ストア DELETEBUFFER インデックス (内部)COLUMNSTORE DELETEBUFFER INDEX (internal)

列ストア DELETEBITMAP インデックス (内部)COLUMNSTORE DELETEBITMAP INDEX (internal)
hobt_idhobt_id bigintbigint インデックスまたはパーティションのヒープまたは B-tree ID。Heap or B-Tree ID of the index or partition.

これは、ユーザー定義のインデックスの hobt_id を返すだけでなく、内部列ストアインデックスの hobt_id も返します。Besides returning the hobt_id of user-defined indexes, this also returns the hobt_id of the internal columnstore indexes.
alloc_unit_type_descalloc_unit_type_desc nvarchar(60)nvarchar(60) アロケーション ユニットの種類の説明。Description of the allocation unit type:

IN_ROW_DATAIN_ROW_DATA

LOB_DATALOB_DATA

ROW_OVERFLOW_DATAROW_OVERFLOW_DATA

LOB_DATA アロケーションユニットには、 textntextimagevarchar (max)nvarchar (max)varbinary (max) 、およびxml型の列に格納されているデータが含まれています。The LOB_DATA allocation unit contains the data that is stored in columns of type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml. 詳細については、「データ型 (Transact-SQL)」を参照してください。For more information, see Data Types (Transact-SQL).

ROW_OVERFLOW_DATA アロケーションユニットには、行外に移動されたvarchar (n)nvarchar (n)varbinary (n) 、およびsql_variant型の列に格納されているデータが含まれています。The ROW_OVERFLOW_DATA allocation unit contains the data that is stored in columns of type varchar(n), nvarchar(n), varbinary(n), and sql_variant that have been pushed off-row.
index_depthindex_depth tinyinttinyint インデックス レベルの数です。Number of index levels.

1 = ヒープ、LOB_DATA アロケーションユニット、または ROW_OVERFLOW_DATA アロケーションユニット。1 = Heap, or LOB_DATA or ROW_OVERFLOW_DATA allocation unit.
index_levelindex_level tinyinttinyint インデックスの現在のレベル。Current level of the index.

インデックスのリーフレベル、ヒープ、LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットの場合は0。0 for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

非リーフインデックスレベルの場合は0より大きい。Greater than 0 for nonleaf index levels. index_levelは、インデックスのルートレベルで最大になります。index_level will be the highest at the root level of an index.

インデックスの非リーフレベルは、 mode = DETAILED の場合にのみ処理されます。The nonleaf levels of indexes are only processed when mode = DETAILED.
avg_fragmentation_in_percentavg_fragmentation_in_percent floatfloat インデックスの論理的な断片化、または IN_ROW_DATA アロケーション ユニットでのヒープのエクステントの断片化。Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

値はパーセンテージとして測定され、複数のファイルを考慮に入れます。The value is measured as a percentage and takes into account multiple files. 論理的な断片化とエクステントの断片化の定義については、「解説」を参照してください。For definitions of logical and extent fragmentation, see Remarks.

LOB_DATA と ROW_OVERFLOW_DATA アロケーションユニットの場合は0です。0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

Mode = サンプリングされた場合のヒープの場合は NULL です。NULL for heaps when mode = SAMPLED.
fragment_countfragment_count bigintbigint IN_ROW_DATA アロケーションユニットのリーフレベルのフラグメントの数。Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. フラグメントの詳細については、「解説」を参照してください。For more information about fragments, see Remarks.

インデックスの非リーフ レベル、および LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは NULL になります。NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

Mode = サンプリングされた場合のヒープの場合は NULL です。NULL for heaps when mode = SAMPLED.
avg_fragment_size_in_pagesavg_fragment_size_in_pages floatfloat IN_ROW_DATA アロケーション ユニットのリーフ レベルにおける、フラグメントあたりのページの平均数。Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit.

インデックスの非リーフ レベル、および LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは NULL になります。NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

Mode = サンプリングされた場合のヒープの場合は NULL です。NULL for heaps when mode = SAMPLED.
page_countpage_count bigintbigint インデックスまたはデータページの合計数。Total number of index or data pages.

インデックスの場合、IN_ROW_DATA アロケーションユニットにおける b-tree の現在のレベルにおけるインデックスページの合計数。For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

ヒープの場合は、IN_ROW_DATA アロケーションユニット内のデータページの合計数。For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットの場合、アロケーションユニット内のページの合計数。For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.
avg_page_space_used_in_percentavg_page_space_used_in_percent floatfloat すべてのページで使用可能なデータストレージ領域の平均割合。Average percentage of available data storage space used in all pages.

インデックスの場合、平均は、IN_ROW_DATA アロケーションユニットの b-tree の現在のレベルに適用されます。For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

ヒープでは、IN_ROW_DATA アロケーション ユニットに含まれるすべてのデータ ページの平均になります。For a heap, the average of all data pages in the IN_ROW_DATA allocation unit.

LOB_DATA または ROW_OVERFLOW のデータアロケーションユニットの場合、アロケーションユニット内のすべてのページの平均です。For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit.

Mode = が制限されている場合は NULL です。NULL when mode = LIMITED.
record_countrecord_count bigintbigint レコードの合計数。Total number of records.

インデックスの場合、レコードの合計数は、IN_ROW_DATA アロケーションユニットの b ツリーの現在のレベルに適用されます。For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

ヒープの場合は、IN_ROW_DATA アロケーションユニット内のレコードの合計数。For a heap, the total number of records in the IN_ROW_DATA allocation unit.

注: ヒープの場合、この関数から返されるレコードの数が、ヒープに対して SELECT COUNT (*) を実行することによって返される行数と一致しない可能性があります。Note: For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. これは、1 行に複数のレコードが含まれる場合があるためです。This is because a row may contain multiple records. たとえば、更新の状況によっては、更新操作の結果として転送元レコードと転送先レコードが 1 つのヒープ行に含まれることがあります。For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. また、大きな LOB 行のほとんどは、LOB_DATA ストレージ内で複数のレコードに分割されます。Also, most large LOB rows are split into multiple records in LOB_DATA storage.

LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは、アロケーション ユニット全体でのレコードの合計数になります。For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit.

Mode = が制限されている場合は NULL です。NULL when mode = LIMITED.
ghost_record_countghost_record_count bigintbigint アロケーション ユニットで、非実体クリーンアップ タスクによる削除の準備ができているゴースト レコードの数。Number of ghost records ready for removal by the ghost cleanup task in the allocation unit.

IN_ROW_DATA アロケーションユニット内のインデックスの非リーフレベルの場合は0です。0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

Mode = が制限されている場合は NULL です。NULL when mode = LIMITED.
version_ghost_record_countversion_ghost_record_count bigintbigint アロケーションユニット内の未処理のスナップショット分離トランザクションによって保持されているゴーストレコードの数。Number of ghost records retained by an outstanding snapshot isolation transaction in an allocation unit.

IN_ROW_DATA アロケーションユニット内のインデックスの非リーフレベルの場合は0です。0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

Mode = が制限されている場合は NULL です。NULL when mode = LIMITED.
min_record_size_in_bytesmin_record_size_in_bytes intint 最小レコードサイズ (バイト単位)。Minimum record size in bytes.

インデックスの場合、最小レコードサイズは、IN_ROW_DATA アロケーションユニットの b ツリーの現在のレベルに適用されます。For an index, minimum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

ヒープでは、IN_ROW_DATA アロケーション ユニットに含まれる最小レコード サイズになります。For a heap, the minimum record size in the IN_ROW_DATA allocation unit.

LOB_DATA または ROW_OVERFLOW_DATA アロケーション ユニットでは、アロケーション ユニット全体での最小レコード サイズになります。For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the minimum record size in the complete allocation unit.

Mode = が制限されている場合は NULL です。NULL when mode = LIMITED.
max_record_size_in_bytesmax_record_size_in_bytes intint 最大レコードサイズ (バイト単位)。Maximum record size in bytes.

インデックスでは、IN_ROW_DATA アロケーション ユニットに含まれる B ツリーの現在のレベルでの最大レコード サイズになります。For an index, the maximum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

ヒープの場合は、IN_ROW_DATA アロケーションユニットの最大レコードサイズ。For a heap, the maximum record size in the IN_ROW_DATA allocation unit.

LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットでは、アロケーションユニット全体の最大レコードサイズになります。For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit.

Mode = が制限されている場合は NULL です。NULL when mode = LIMITED.
avg_record_size_in_bytesavg_record_size_in_bytes floatfloat 平均レコード サイズ (バイト単位)。Average record size in bytes.

インデックスでは、IN_ROW_DATA アロケーション ユニットに含まれる B ツリーの現在のレベルでの平均レコード サイズになります。For an index, the average record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

ヒープの場合は、IN_ROW_DATA アロケーションユニットの平均レコードサイズ。For a heap, the average record size in the IN_ROW_DATA allocation unit.

LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットでは、アロケーションユニット全体の平均レコードサイズになります。For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the average record size in the complete allocation unit.

Mode = が制限されている場合は NULL です。NULL when mode = LIMITED.
forwarded_record_countforwarded_record_count bigintbigint 別のデータの場所への転送ポインターを持つ、ヒープ内の転送されたレコード数Number of records in a heap that have forward pointers to another data location. (この状態は、更新中に、新しい行を格納できる十分なスペースが元の場所にない場合に発生します)。(This state occurs during an update, when there is not enough room to store the new row in the original location.)

ヒープの IN_ROW_DATA アロケーション ユニット以外のアロケーション ユニットでは NULL になります。NULL for any allocation unit other than the IN_ROW_DATA allocation units for a heap.

Modeが制限されている場合のヒープの場合は NULL です。NULL for heaps when mode = LIMITED.
compressed_page_countcompressed_page_count bigintbigint 圧縮されたページの数。The number of compressed pages.

ヒープの場合、新しく割り当てられたページはページ圧縮されません。For heaps, newly allocated pages are not PAGE compressed. ヒープは、2 つの特殊な条件、つまりデータを一括インポートする場合、またはヒープを再構築する場合に、ページ圧縮されます。A heap is PAGE compressed under two special conditions: when data is bulk imported or when a heap is rebuilt. ページ割り当ての原因となる一般的な DML 操作は、ページ圧縮されません。Typical DML operations that cause page allocations will not be PAGE compressed. compressed_page_count の値が目標のしきい値を超えた場合は、ヒープを再構築してください。Rebuild a heap when the compressed_page_count value grows larger than the threshold you want.

クラスター化インデックスを含むテーブルの場合、compressed_page_count の値はページ圧縮の効果を示します。For tables that have a clustered index, the compressed_page_count value indicates the effectiveness of PAGE compression.
hobt_idhobt_id BIGINTbigint 適用対象: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) から 現在のバージョンまで)、 Azure SQL データベースAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Azure SQL データベースAzure SQL Database.

列ストアインデックスの場合にのみ、これはパーティションの内部列ストアデータを追跡する行セットの ID です。For columnstore indexes only, this is the ID for a rowset that tracks internal columnstore data for a partition. 行セットは、データヒープまたはバイナリツリーとして格納されます。The rowsets are stored as data heaps or binary trees. 親列ストアインデックスと同じインデックス ID を持っています。They have the same index ID as the parent columnstore index. 詳細については、「 internal_partitions (transact-sql)」を参照してください。For more information, see sys.internal_partitions (Transact-SQL).

NULL の場合NULL if
column_store_delete_buffer_statecolumn_store_delete_buffer_state TINYINTtinyint 適用対象: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) から 現在のバージョンまで)、 Azure SQL データベースAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Azure SQL データベースAzure SQL Database.

0 = NOT_APPLICABLE0 = NOT_APPLICABLE

1 = OPEN1 = OPEN

2 = ドレイン中2 = DRAINING

3 = フラッシュ3 = FLUSHING

4 = インベントリからの削除4 = RETIRING

5 = 準備完了5 = READY
column_store_delete_buff_state_desccolumn_store_delete_buff_state_desc 適用対象: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) から 現在のバージョンまで)、 Azure SQL データベースAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Azure SQL データベースAzure SQL Database.

無効-親インデックスが列ストアインデックスではありません。NOT VALID -the parent index is not a columnstore index.

OPEN-deleters とスキャナーはこれを使用します。OPEN - deleters and scanners use this.

ドレイン中-deleters はドレインされますが、スキャナーでは引き続き使用されます。DRAINING - deleters are draining out but scanners still use it.

フラッシュ-バッファーが閉じられ、バッファー内の行が削除ビットマップに書き込まれています。FLUSHING - buffer is closed and rows in the buffer are being written to the delete bitmap.

削除-閉じた削除バッファーの行が delete ビットマップに書き込まれましたが、スキャナーがまだ使用しているため、バッファーが切り捨てられていません。RETIRING - rows in the closed delete buffer have been written to the delete bitmap, but the buffer has not been truncated because scanners are still using it. 開いているバッファーが十分であるため、新しいスキャナーでは、インベントリから削除するバッファーを使用する必要はありません。New scanners don't need to use the retiring buffer because the open buffer is enough.

READY-この削除バッファーは使用する準備ができています。READY - This delete buffer is ready for use.

コメントRemarks

sys.dm_db_index_physical_stats 動的管理関数は、DBCC SHOWCONTIG ステートメントの代わりに使用できます。The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

スキャン モードScanning Modes

関数が実行されるモードによって、関数で使用する統計データを取得するためのスキャンのレベルが決まります。The mode in which the function is executed determines the level of scanning performed to obtain the statistical data that is used by the function. モードは、制限、サンプリング、または詳細として指定されます。mode is specified as LIMITED, SAMPLED, or DETAILED. 関数は、テーブルまたはインデックスの指定されたパーティションを構成するアロケーションユニットのページチェーンを走査します。The function traverses the page chains for the allocation units that make up the specified partitions of the table or index. 実行されているモードに関係なく、インテント共有 (IS) テーブルロックのみが必要になります。 (_c)sys.dm_db_index_physical_stats requires only an Intent-Shared (IS) table lock, regardless of the mode that it runs in.

LIMITED モードは最も高速なモードで、スキャンするページ数は最小です。The LIMITED mode is the fastest mode and scans the smallest number of pages. インデックスでは、B ツリーの親レベルのページ (リーフ レベルより上のページ) だけがスキャンされます。For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. ヒープでは、関連付けられた PFS ページおよび IAM ページが調べられます。LIMITED モードではヒープのデータ ページがスキャンされます。For a heap, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode.

LIMITED モードでは、データベース エンジンDatabase Engineは B ツリーの非リーフ ページとヒープの IAM および PFS ページのみをスキャンするので、compressed_page_count は NULL です。With LIMITED mode, compressed_page_count is NULL because the データベース エンジンDatabase Engine only scans non-leaf pages of the B-tree and the IAM and PFS pages of the heap. サンプリングモードを使用して compressed_page_count の推定値を取得し、詳細モードを使用して compressed_page_count の実際の値を取得します。Use SAMPLED mode to get an estimated value for compressed_page_count, and use DETAILED mode to get the actual value for compressed_page_count. サンプリングモードでは、インデックスまたはヒープ内のすべてのページの 1% のサンプルに基づいて統計が返されます。The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. SAMPLED モードの結果は近似と見なす必要があります。Results in SAMPLED mode should be regarded as approximate. インデックスまたはヒープのページが1万未満の場合は、サンプリングされるのではなく詳細モードが使用されます。If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.

DETAILED モードではすべてのページがスキャンされ、すべての統計が返されます。The DETAILED mode scans all pages and returns all statistics.

各モードでは、より多くの作業が実行されるため、モードは徐々に [詳細] になります。The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. テーブルまたはインデックスの断片化レベルのサイズをすばやく計測するには、LIMITED モードを使用します。To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. これは最速であり、インデックスの IN_ROW_DATA アロケーションユニットに含まれる各非リーフレベルの行を返しません。It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA allocation unit of the index.

システム関数によるパラメーター値の指定Using System Functions to Specify Parameter Values

Transact-SQLTransact-SQL関数DB_IDおよびobject_idを使用して、 database_idパラメーターとobject_idパラメーターの値を指定できます。You can use the Transact-SQLTransact-SQL functions DB_ID and OBJECT_ID to specify a value for the database_id and object_id parameters. ただし、これらの関数に無効な値を渡すと、意図しない結果が生じる可能性があります。However, passing values that are not valid to these functions may cause unintended results. たとえば、データベース名またはオブジェクト名が存在しないか、スペルが間違っているために見つからない場合、両方の関数は NULL を返します。For example, if the database or object name cannot be found because they do not exist or are spelled incorrectly, both functions will return NULL. sys.dm_db_index_physical_stats 関数では、NULL 値はすべてのデータベースまたはすべてのオブジェクトを指定するワイルドカード値として解釈されます。The sys.dm_db_index_physical_stats function interprets NULL as a wildcard value specifying all databases or all objects.

また、OBJECT_ID 関数は、 database_idで指定されたデータベースではなく、現在のデータベースのコンテキストで評価される前に処理され、その結果、現在のデータベースのコンテキストで評価されます。Additionally, the OBJECT_ID function is processed before the sys.dm_db_index_physical_stats function is called and is therefore evaluated in the context of the current database, not the database specified in database_id. この動作により、OBJECT_ID 関数で NULL 値が返される場合があります。または、オブジェクト名が現在のデータベースのコンテキストと指定したデータベースの両方に存在する場合は、エラー メッセージが返されることがあります。This behavior may cause the OBJECT_ID function to return a NULL value; or, if the object name exists in both the current database context and the specified database, an error message may be returned. 次の例は、こうした意図しない結果を示すものです。The following examples demonstrate these unintended results.

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 AdventureWorks2012;  
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  

推奨事項Best Practice

DB_ID または OBJECT_ID を使用する場合は、必ず有効な ID が返されるようにしてください。Always make sure that a valid ID is returned when you use DB_ID or OBJECT_ID. たとえば、OBJECT_ID を使用する場合は、のようなOBJECT_ID(N'AdventureWorks2012.Person.Address')3 部構成の名前を指定するか、関数によって返された値をテストしてから、それらを使用してください。For example, when you use OBJECT_ID, specify a three-part name such as OBJECT_ID(N'AdventureWorks2012.Person.Address'), or test the value returned by the functions before you use them in the sys.dm_db_index_physical_stats function. 次の例 A と B は、データベース Id とオブジェクト Id を安全に指定する方法を示しています。Examples A and B that follow demonstrate a safe way to specify database and object IDs.

断片化の検出Detecting Fragmentation

断片化は、テーブルとテーブルに定義されたインデックスに対して、INSERT、UPDATE、DELETE ステートメントによるデータ変更が行われる過程で発生します。Fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and, therefore, to the indexes defined on the table. これらの変更は、テーブルとインデックスの行間で均等に分散されるとは限りません。そのため、各ページのゆとりは時間の経過と共に変化することがあります。Because these modifications are not ordinarily distributed equally among the rows of the table and indexes, the fullness of each page can vary over time. このような断片化があるときに、クエリでテーブルのインデックスの一部または全部をスキャンしようとすると、余分なページの読み取りが必要になり、For queries that scan part or all of the indexes of a table, this kind of fragmentation can cause additional page reads. データの並列スキャンの妨げになります。This hinders parallel scanning of data.

インデックスまたはヒープの断片化レベルは、avg_fragmentation_in_percent 列で確認できます。The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. ヒープの場合、値はヒープのエクステントの断片化を表します。For heaps, the value represents the extent fragmentation of the heap. インデックスの場合、この値はインデックスの論理的な断片化を表します。For indexes, the value represents the logical fragmentation of the index. DBCC SHOWCONTIG とは異なり、どちらの場合の断片化計算アルゴリズムでも複数のファイルにまたがるストレージが考慮されるため、正確です。Unlike DBCC SHOWCONTIG, the fragmentation calculation algorithms in both cases consider storage that spans multiple files and, therefore, are accurate.

論理的な断片化Logical Fragmentation

これは、インデックスのリーフページでの順序のないページの割合です。This is the percentage of out-of-order pages in the leaf pages of an index. 順序が不正なページとは、インデックスに割り当てられている次の物理的なページと、現在のリーフ ページの次ページ ポインターが示すページが異なるページのことです。An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.

エクステントの断片化Extent Fragmentation

これは、ヒープのリーフページでの順序が不足しているエクステントの割合です。This is the percentage of out-of-order extents in the leaf pages of a heap. 順序が無効なエクステントとは、ヒープの現在のページを含むエクステントの物理的な位置が、前のページを含むエクステントの直後でない状態のエクステントを指します。An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

最大のパフォーマンスを得るには、avg_fragmentation_in_percent の値をできるだけ 0 に近くする必要があります。The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. ただし、0 ~ 10% の値は許容される可能性があります。However, values from 0 percent through 10 percent may be acceptable. 再構築、再構成、再作成など、断片化を解消するためのさまざまな手段を使用することによって、この値を下げることができます。All methods of reducing fragmentation, such as rebuilding, reorganizing, or re-creating, can be used to reduce these values. インデックスの断片化の程度を分析する方法の詳細については、「インデックスの再編成と再構築」を参照してください。For more information about how to analyze the degree of fragmentation in an index, see Reorganize and Rebuild Indexes.

インデックスの断片化の解消Reducing Fragmentation in an Index

断片化がクエリのパフォーマンスに影響するような方法でインデックスがフラグメント化されている場合、断片化を減らすには次の3つの選択肢があります。When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:

  • クラスター化インデックスを削除し、再作成する。Drop and re-create the clustered index.

    クラスター化インデックスを再作成すると、データが再分配され、データ ページにデータが満たされます。Re-creating a clustered index redistributes the data and results in full data pages. ページのゆとりのレベルは、CREATE INDEX の FILLFACTOR オプションを使用して構成できます。The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. この方法の欠点は、削除と再作成のサイクル中にインデックスがオフラインになり、操作がアトミックであることです。The drawbacks in this method are that the index is offline during the drop and re-create cycle, and that the operation is atomic. インデックスの作成が中断されると、インデックスは再作成されません。If the index creation is interrupted, the index is not re-created. 詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。For more information, see CREATE INDEX (Transact-SQL).

  • DBCC INDEXDEFRAG の代わりとして ALTER INDEX 再構成を使用して、インデックスのリーフレベルページを論理的な順序で並べ替えます。Use ALTER INDEX REORGANIZE, the replacement for DBCC INDEXDEFRAG, to reorder the leaf level pages of the index in a logical order. これはオンライン操作なので、ステートメントの実行中にインデックスを使用できます。Because this is an online operation, the index is available while the statement is running. 操作を中断しても、それまでに完了した作業は失われません。The operation can also be interrupted without losing work already completed. この方法の欠点は、インデックスの再構築操作としてデータを再編成するのは適切ではなく、統計を更新しないことです。The drawback in this method is that it does not do as good a job of reorganizing the data as an index rebuild operation, and it does not update statistics.

  • DBCC DBREINDEX の代わりとして ALTER INDEX REBUILD を使用して、インデックスをオンラインまたはオフラインで再構築します。Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline. 詳細については、「ALTER INDEX (Transact-SQL)」を参照してください。For more information, see ALTER INDEX (Transact-SQL).

断片化だけでは、インデックスを再構成または再構築するための十分な理由ではありません。Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. 断片化の主な効果は、インデックススキャン中にページの先行読み取りスループットが低下することです。The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. これは、応答時間の遅れの原因となります。This causes slower response times. 断片化したテーブルまたはインデックスでのクエリのワークロードにスキャンが関係していない場合、ワークロードは主に単一参照のため、断片化を解消しても効果はありません。If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect.

注意

圧縮操作中にインデックスが部分的または完全に移動した場合、DBCC SHRINKFILE または DBCC SHRINKDATABASE を実行すると、断片化が発生する可能性があります。Running DBCC SHRINKFILE or DBCC SHRINKDATABASE may introduce fragmentation if an index is partly or completely moved during the shrink operation. このため、圧縮操作を実行する必要がある場合は、断片化が解除される前に実行する必要があります。Therefore, if a shrink operation must be performed, you should do it before fragmentation is removed.

ヒープの断片化の解消Reducing Fragmentation in a Heap

ヒープのエクステントの断片化を軽減するには、テーブルにクラスター化インデックスを作成してから、インデックスを削除します。To reduce the extent fragmentation of a heap, create a clustered index on the table and then drop the index. これによって、クラスター化インデックスの作成中にデータが再分配されます。This redistributes the data while the clustered index is created. この操作では、データベースの空き領域の分布を考慮に入れて、可能な限り最適化も行われます。This also makes it as optimal as possible, considering the distribution of free space available in the database. その後、ヒープを再作成するためにクラスター化インデックスが削除されると、データは移動されず、最適な位置に維持されます。When the clustered index is then dropped to re-create the heap, the data is not moved and remains optimally in position. これらの操作を実行する方法については、「 CREATE INDEX 」および「 DROP INDEX」を参照してください。For information about how to perform these operations, see CREATE INDEX and DROP INDEX.

注意事項

テーブルのクラスター化インデックスを作成および削除すると、そのテーブルのすべての非クラスター化インデックスが2回再構築されます。Creating and dropping a clustered index on a table, rebuilds all nonclustered indexes on that table twice.

ラージ オブジェクト データの圧縮Compacting Large Object Data

既定では、ALTER INDEX 再構成ステートメントは、ラージオブジェクト (LOB) データを含むページを圧縮します。By default, the ALTER INDEX REORGANIZE statement compacts pages that contain large object (LOB) data. LOB ページは空のときに割り当て解除されないので、LOB データの多くが削除された場合や LOB 列が削除された場合は、このデータを圧縮するとディスク領域の使用が向上します。Because LOB pages are not deallocated when empty, compacting this data can improve disk space use if lots of LOB data have been deleted, or a LOB column is dropped.

指定されたクラスター化インデックスを再構成すると、クラスター化インデックスに含まれるすべての LOB 列が圧縮されます。Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. 非クラスター化インデックスを再構成すると、そのインデックス内で非キー列 (付加列) となっているすべての LOB 列が圧縮されます。Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index. ステートメントで ALL を指定した場合は、指定したテーブルまたはビューに関連付けられているすべてのインデックスが再構成されます。When ALL is specified in the statement, all indexes that are associated with the specified table or view are reorganized. さらに、クラスター化インデックス、基になるテーブル、または付加列を含む非クラスター化インデックスに関連付けられているすべての LOB 列が圧縮されます。Additionally, all LOB columns that are associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.

ディスク領域の使用量の評価Evaluating Disk Space Use

avg_page_space_used_in_percent 列には、ページのゆとりが示されます。The avg_page_space_used_in_percent column indicates page fullness. ディスク領域の使用を最適にするには、ランダムな挿入があまり行われないインデックスの場合はこの値を 100% に近づけます。To achieve optimal disk space use, this value should be close to 100 percent for an index that will not have many random inserts. ただし、ランダムな挿入が多数あり、ページが非常に多いインデックスの場合、ページ分割の数が増加します。However, an index that has many random inserts and has very full pages will have an increased number of page splits. 断片化が大きくなります。This causes more fragmentation. したがって、ページ分割を減らすために、値は 100% 未満にする必要があります。Therefore, in order to reduce page splits, the value should be less than 100 percent. FILLFACTOR オプションを指定してインデックスを再構築すると、ページのゆとりをインデックスのクエリ パターンに合わせて変更できます。Rebuilding an index with the FILLFACTOR option specified allows the page fullness to be changed to fit the query pattern on the index. FILL FACTOR の詳細については、「インデックスの Fill factor の指定」を参照してください。For more information about fill factor, see Specify Fill Factor for an Index. また、ALTER INDEX REORGANIZE は最後に指定された FILLFACTOR までページが埋まるよう、インデックスを圧縮します。Also, ALTER INDEX REORGANIZE will compact an index by trying to fill pages to the FILLFACTOR that was last specified. これにより、avg_space_used_in_percent の値は増加します。This increases the value in avg_space_used_in_percent. ALTER INDEX REORGANIZE はページのゆとりを調整できないことに注意してください。Note that ALTER INDEX REORGANIZE cannot reduce page fullness. 代わりに、インデックスの再構築を実行する必要があります。Instead, an index rebuild must be performed.

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

フラグメントは、1つのアロケーションユニットについて、同じファイル内の物理的に連続するリーフページで構成されます。A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. 1 つのインデックスには少なくとも 1 つのフラグメントが含まれます。An index has at least one fragment. インデックスが持つことのできるフラグメントの最大数は、インデックスのリーフ レベルのページ数と同じです。The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. フラグメントが大きいほど、同じ数のページの読み取りに必要なディスクの I/O が少なくなります。Larger fragments mean that less disk I/O is required to read the same number of pages. したがって、avg_fragment_size_in_pages 値が大きいほど、範囲スキャンのパフォーマンスは向上します。Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance. avg_fragment_size_in_pages と avg_fragmentation_in_percent の値は、互いに反比例します。The avg_fragment_size_in_pages and avg_fragmentation_in_percent values are inversely proportional to each other. そのため、インデックスの再構築または再構成を行うと、断片化の量が減り、フラグメントのサイズが大きくなります。Therefore, rebuilding or reorganizing an index should reduce the amount of fragmentation and increase the fragment size.

制限事項と制約事項Limitations and Restrictions

では、クラスター化列ストアインデックスのデータは返されません。Does not return data for clustered columnstore indexes.

アクセス許可Permissions

次の権限が必要です。Requires the following permissions:

  • データベース内の指定されたオブジェクトに対する CONTROL 権限。CONTROL permission on the specified object within the database.

  • オブジェクトのワイルドカード @object_id= NULL を使用して、指定したデータベース内のすべてのオブジェクトに関する情報を返す VIEW DATABASE STATE 権限。VIEW DATABASE STATE permission to return information about all objects within the specified database, by using the object wildcard @object_id=NULL.

  • データベースのワイルドカード @database_id = NULL を使用して、すべてのデータベースに関する情報を返す VIEW SERVER STATE 権限。VIEW SERVER STATE permission to return information about all databases, by using the database wildcard @database_id = NULL.

VIEW DATABASE STATE 権限を許可すると、特定のオブジェクトに対して CONTROL 権限が拒否されていたとしても、データベース内のすべてのオブジェクトを取得することができます。Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.

VIEW DATABASE STATE 権限を拒否すると、特定のオブジェクトに対する CONTROL 権限が許可されていたとしても、そのデータベース内のどのオブジェクトも取得できません。Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. また、データベースのワイルドカード @database_id= NULL が指定されている場合、データベースは省略されます。Also, when the database wildcard @database_id=NULL is specified, the database is omitted.

詳細については、「動的管理ビュー (と関数 transact-sql)」を参照してください。For more information, see Dynamic Management Views and Functions (Transact-SQL).

使用例Examples

A.A. 指定されたテーブルに関する情報を返すReturning information about a specified table

次の例では、Person.Address テーブルのすべてのインデックスとパーティションについて、サイズと断片化の統計を返します。The following example returns size and fragmentation statistics for all indexes and partitions of the Person.Address table. 最適なパフォーマンスを得るため'LIMITED' 、および返される統計を制限するために、スキャンモードがに設定されています。The scan mode is set to 'LIMITED' for best performance and to limit the statistics that are returned. このクエリを実行するには、少なくとも Person.Address テーブルに対する CONTROL 権限が必要です。Executing this query requires, at a minimum, CONTROL permission on the Person.Address table.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  
  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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.B. ヒープに関する情報を返すReturning information about a heap

次の例では、AdventureWorks2012AdventureWorks2012 データベースのヒープ dbo.DatabaseLog に関するすべての統計を返します。The following example returns all statistics for the heap dbo.DatabaseLog in the AdventureWorks2012AdventureWorks2012 database. テーブルには LOB データが含まれているため、ヒープLOB_DATAのデータページを格納してIN_ROW_ALLOCATION_UNITいるに対して返される行に加えて、アロケーションユニットの行が返されます。Because the table contains LOB data, a row is returned for the LOB_DATA allocation unit in addition to the row returned for the IN_ROW_ALLOCATION_UNIT that is storing the data pages of the heap. このクエリを実行するには、少なくとも dbo.DatabaseLog テーブルに対する CONTROL 権限が必要です。Executing this query requires, at a minimum, CONTROL permission on the dbo.DatabaseLog table.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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.C. すべてのデータベースに関する情報を返すReturning information for all databases

次の例では、すべてのパラメーターに対してワイルドカードSQL ServerSQL Server NULLを指定することによって、のインスタンス内のすべてのテーブルとインデックスに関するすべての統計を返します。The following example returns all statistics for all tables and indexes within the instance of SQL ServerSQL Server by specifying the wildcard NULL for all parameters. このクエリを実行するには、VIEW SERVER STATE 権限が必要です。Executing this query requires the VIEW SERVER STATE permission.

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

D.D. インデックスの再構築または再構成のためのスクリプトでの使用 (_d)Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

次の例では、平均断片化が 10% を超えるデータベース内のすべてのパーティションを自動的に再編成または再構築します。The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. このクエリを実行するには、VIEW DATABASE STATE 権限が必要です。Executing this query requires the VIEW DATABASE STATE permission. この例では、データベース名を指定せずに DB_ID を 1 番目のパラメーターとして指定しています。This example specifies DB_ID as the first parameter without specifying a database name. 現在のデータベースの互換性レベルが80以下の場合は、エラーが生成されます。An error will be generated if the current database has a compatibility level of 80 or lower. このエラーを解決するには、DB_ID() を有効なデータベース名で置き換えます。To resolve the error, replace DB_ID() with a valid database name. データベース互換性レベルの詳細については、「 ALTER DATABASE (Transact-SQL) 互換性レベル」を参照してください。For more information about database compatibility levels, see ALTER DATABASE Compatibility Level (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  
  

E.E. sys.dm_db_index_physical_stats を使用して、ページ圧縮されたページの数を表示するUsing sys.dm_db_index_physical_stats to show the number of page-compressed pages

次の例では、行とページが圧縮されているページとの合計ページ数を表示および比較する方法を示します。The following example shows how to display and compare the total number of pages against the pages that are row and page compressed. この情報を使用して、インデックスまたはテーブルに対して圧縮が提供する特典を判断できます。This information can be used to determine the benefit that compression is providing for an index or table.

SELECT o.name,  
    ips.partition_number,  
    ips.index_type_desc,  
    ips.record_count, ips.avg_record_size_in_bytes,  
    ips.min_record_size_in_bytes,  
    ips.max_record_size_in_bytes,  
    ips.page_count, ips.compressed_page_count  
FROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips  
JOIN sys.objects o on o.object_id = ips.object_id  
ORDER BY record_count DESC;  

F.F. SAMPLED モードで sys.dm_db_index_physical_stats を使用するUsing sys.dm_db_index_physical_stats in SAMPLED mode

次の例は、サンプリングモードで、詳細モードの結果とは異なる概数を返す方法を示しています。The following example shows how SAMPLED mode returns an approximate that is different than the DETAILED mode results.

CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);  
GO  
BEGIN TRAN  
DECLARE @idx int = 0;  
WHILE @idx < 1000000  
BEGIN  
    INSERT INTO t3 (col1, col2)   
    VALUES (@idx,   
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))  
    SET @idx = @idx + 1  
END  
COMMIT;  
GO  
SELECT page_count, compressed_page_count, forwarded_record_count, *   
FROM sys.dm_db_index_physical_stats (db_id(),   
    object_id ('t3'), null, null, 'SAMPLED');  
SELECT page_count, compressed_page_count, forwarded_record_count, *   
FROM sys.dm_db_index_physical_stats (db_id(),   
    object_id ('t3'), null, null, 'DETAILED');  

G.G. インデックスの断片化のために service broker キューを照会していますQuerying service broker queues for index fragmentation

適用対象: SQL Server 2016 (13.x)SQL Server 2016 (13.x) から SQL ServerSQL ServerApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL ServerSQL Server.

次の例では、サーバーブローカーキューの断片化をクエリする方法を示します。The following examples shows how to query server broker queues for fragmentation.

--Using queue internal table name   
select * from sys.dm_db_index_physical_stats (db_id(), object_id ('sys.queue_messages_549576996'), default, default, default)   
  
--Using queue name directly  
select * from sys.dm_db_index_physical_stats (db_id(), object_id ('ExpenseQueue'), default, default, default)  
  

参照See Also

動的管理ビューと動的管理関数 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
インデックス関連の動的管理ビューおよび関数 (Transact-SQL) Index Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL) sys.dm_db_index_operational_stats (Transact-SQL)
SQL server の統計(情報 (_d): transact-sql) sys.dm_db_index_usage_stats (Transact-SQL)
システムの(transact-sql (_d) (_d)) sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL) sys.allocation_units (Transact-SQL)
システムビュー (Transact-sql)System Views (Transact-SQL)