sys.dm_db_index_physical_stats (Transact-SQL)

[アーティクル]
10/04/2024

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

SQL Server データベースエンジンの指定したテーブルまたはビューのデータとインデックスのサイズと断片化に関する情報を返します。インデックスの場合、各パーティションの B ツリーのレベルごとに 1 行のデータが返されます。ヒープの場合、各パーティションの IN_ROW_DATA アロケーションユニットごとに 1 行のデータが返されます。ラージオブジェクト (LOB) データの場合、各パーティションの LOB_DATA 割り当て単位に対して 1 行が返されます。テーブルに行オーバーフローデータが存在する場合は、各パーティションの ROW_OVERFLOW_DATA 割り当て単位に対して 1 つの行が返されます。

Note

ドキュメントでは、一般的にインデックスに関して B ツリーという用語が使用されます。行ストアインデックスで、データベースエンジンによって B+ ツリーが実装されます。これは、列ストアインデックスやメモリ最適化テーブルのインデックスには適用されません。詳細については、「SQL Server と Azure SQL のインデックスのアーキテクチャとデザインガイド」を参照してください。

sys.dm_db_index_physical_stats では、メモリ最適化インデックスに関する情報は返されません。メモリ最適化インデックスの使用については、 sys.dm_db_xtp_index_statsを参照してください。

可用性グループ読み取り可能なセカンダリレプリカをホストしているサーバーインスタンスでsys.dm_db_index_physical_statsクエリを実行するとREDOブロックの問題が発生する可能性があります。これは、この動的管理ビューが、指定したユーザーテーブルまたはビューに対する Intent-Shared (IS) ロックを取得するためです。このロックは、そのユーザーテーブルまたはビューに対する排他 (X) ロックに対する REDO スレッドによる要求をブロックできます。

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 |デフォルト

データベースの ID です。 database_id は smallint です。有効な入力は、データベース、 NULL、 0、または DEFAULTの ID です。既定値は、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 |デフォルト

インデックスがオンになっているテーブルまたはビューのオブジェクト ID。 object_id は int です。有効な入力は、テーブルとビュー、 NULL、 0、または DEFAULTの ID です。既定値は、0 です。 NULL、 0、および DEFAULT は、このコンテキストで同等の値です。

SQL Server 2016 (13.x) 以降のバージョンでは、有効な入力には、Service Broker キュー名またはキューの内部テーブル名も含まれます。既定のパラメーター (つまり、すべてのオブジェクト、すべてのインデックスなど) が適用されると、すべてのキューの断片化情報が結果セットに含まれます。

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

index_id | 0 |NULL |-1 |デフォルト

インデックスの ID です。 index_idは int です。有効な入力はインデックスの ID であり、object_idがヒープ、NULL、-1、またはDEFAULTの場合に0されます。既定値は、-1 です。 NULL、 -1、および DEFAULT は、このコンテキストで同等の値です。

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

partition_number |NULL |0 |デフォルト

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

所有オブジェクトのすべてのパーティションの情報を返す NULL を指定します。

partition_number は 1 から始まります。パーティション分割されていないインデックスまたはヒープpartition_number1に設定されています。

mode |NULL |デフォルト

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

返されるテーブル

列名	データ型	説明
`database_id`	smallint	テーブルまたはビューのデータベース ID。 Azure SQL Database では、値は 1 つのデータベースまたは Elastic Pool 内で一意ですが、論理サーバー内では一意ではありません。
`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` - `EXTENDED INDEX` - `XML INDEX` - `COLUMNSTORE MAPPING INDEX` (内部) - `COLUMNSTORE DELETEBUFFER INDEX` (内部) - `COLUMNSTORE DELETEBITMAP 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 の列に格納されるデータが含まれます。詳細については、データ型を参照してください。 `ROW_OVERFLOW_DATA`アロケーションユニットには、varchar(n)、nvarchar(n)、varbinary(n))、および行外にプッシュされるsql_variantの列に格納されているデータが含まれます。
`index_depth`	tinyint	インデックスレベルの数です。 `1` = ヒープ、または `LOB_DATA` または `ROW_OVERFLOW_DATA` アロケーションユニット。
`index_level`	tinyint	インデックスの現在のレベル。 `0` インデックスリーフレベル、ヒープ、および `LOB_DATA` または `ROW_OVERFLOW_DATA` 割り当て単位の場合。非リーフインデックスレベルの `0` より大きい。 index_level は、インデックスのルートレベルで最も高くなります。インデックスの非リーフレベルは、 mode が `DETAILED`されている場合にのみ処理されます。
`avg_fragmentation_in_percent`	float	インデックスの論理的な断片化、または `IN_ROW_DATA` 割り当てユニット内のヒープのエクステントの断片化。この値はパーセンテージとして測定され、複数のファイルが考慮されます。論理断片化とエクステント断片化の定義については、「 Remarks」を参照してください。 `0` 割り当て単位の `LOB_DATA` と `ROW_OVERFLOW_DATA` 。 `NULL`modeが`SAMPLED`されている場合はヒープの場合。
`fragment_count`	bigint	`IN_ROW_DATA`アロケーションユニットのリーフレベル内のフラグメントの数。フラグメントの詳細については、「 Remarks」を参照してください。 `NULL` インデックスの非リーフレベル、および割り当て単位の `LOB_DATA` または `ROW_OVERFLOW_DATA` 。 `NULL`modeが`SAMPLED`されている場合はヒープの場合。
`avg_fragment_size_in_pages`	float	`IN_ROW_DATA`アロケーションユニットのリーフレベル内の 1 つのフラグメント内の平均ページ数。 `NULL` インデックスの非リーフレベル、および割り当て単位の `LOB_DATA` または `ROW_OVERFLOW_DATA` 。 `NULL`modeが`SAMPLED`されている場合はヒープの場合。
`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`アロケーションユニットの場合、アロケーションユニット内のすべてのページの平均。 `NULL`modeが`LIMITED`されている場合。
`record_count`	bigint	レコードの合計数。インデックスの場合、レコードの合計数は、 `IN_ROW_DATA` アロケーションユニットの B ツリーの現在のレベルに適用されます。ヒープの場合、 `IN_ROW_DATA` アロケーションユニット内のレコードの合計数。注: ヒープの場合、この関数から返されるレコードの数が、ヒープに対して `SELECT COUNT()` を実行して返される行数と一致しない可能性があります。これは、行に複数のレコードを含めることができるためです。たとえば、一部の更新状況では、1 つのヒープ行に、更新操作の結果として転送レコードと転送レコードが含まれる場合があります。また、ほとんどの大きな LOB 行は、 `LOB_DATA` ストレージ内の複数のレコードに分割されます。 `LOB_DATA`または`ROW_OVERFLOW_DATA`割り当て単位の場合は、割り当て単位全体のレコードの合計数。 `NULL`mode*が`LIMITED`されている場合。
`ghost_record_count`	bigint	アロケーションユニットで、非実体クリーンアップタスクによる削除の準備ができているゴーストレコードの数。 `0IN_ROW_DATA`アロケーションユニット内のインデックスの非リーフレベルの場合。 `NULL`modeが`LIMITED`されている場合。
`version_ghost_record_count`	bigint	割り当て単位で未処理のスナップショット分離トランザクションによって保持されるゴーストレコードの数。 `0IN_ROW_DATA`アロケーションユニット内のインデックスの非リーフレベルの場合。 `NULL`modeが`LIMITED`されている場合。
`min_record_size_in_bytes`	int	最小レコードサイズ (バイト単位)。インデックスの場合、最小レコードサイズは、 `IN_ROW_DATA` アロケーションユニットの B ツリーの現在のレベルに適用されます。ヒープの場合、 `IN_ROW_DATA` 割り当て単位の最小レコードサイズ。 `LOB_DATA`または`ROW_OVERFLOW_DATA`割り当て単位の場合、割り当て単位全体の最小レコードサイズ。 `NULL`modeが`LIMITED`されている場合。
`max_record_size_in_bytes`	int	最大レコードサイズ (バイト単位)。インデックスの場合、最大レコードサイズは、 `IN_ROW_DATA` アロケーションユニットの B ツリーの現在のレベルに適用されます。ヒープの場合、 `IN_ROW_DATA` アロケーションユニットの最大レコードサイズ。 `LOB_DATA`または`ROW_OVERFLOW_DATA`割り当て単位の場合、割り当て単位全体の最大レコードサイズ。 `NULL`modeが`LIMITED`されている場合。
`avg_record_size_in_bytes`	float	平均レコードサイズ (バイト単位)。インデックスの場合、平均レコードサイズは、 `IN_ROW_DATA` アロケーションユニットの B ツリーの現在のレベルに適用されます。ヒープの場合、 `IN_ROW_DATA` アロケーションユニットの平均レコードサイズ。 `LOB_DATA`または`ROW_OVERFLOW_DATA`割り当て単位の場合は、割り当て単位全体の平均レコードサイズ。 `NULL`modeが`LIMITED`されている場合。
`forwarded_record_count`	bigint	別のデータの場所への転送ポインターを持つ、ヒープ内の転送されたレコード数 (この状態は、更新中に、元の場所に新しい行を格納するのに十分な空きがない場合に発生します)。 `NULL` ヒープの `IN_ROW_DATA` 割り当てユニット以外の割り当て単位の場合は >。 `NULL`modeが`LIMITED`されている場合はヒープの場合。
`compressed_page_count`	bigint	圧縮されたページの数。ヒープの場合、新しく割り当てられたページは圧縮 `PAGE` されません。ヒープは、データを一括インポートするとき、またはヒープを再構築するときという 2 つの特殊な条件下で `PAGE` 圧縮されます。ページ割り当てを引き起こす一般的な DML 操作は `PAGE` 圧縮されません。 `compressed_page_count`の値が目的のしきい値を超えたときにヒープを再構築します。クラスター化インデックスを持つテーブルの場合、 `compressed_page_count` 値は `PAGE` 圧縮の有効性を示します。
`hobt_id`	bigint	インデックスまたはパーティションのヒープまたは B ツリー ID。列ストアインデックスの場合、これはパーティションの内部列ストアデータを追跡する行セットの ID です。行セットは、データヒープまたは B ツリーとして格納されます。親列ストアインデックスと同じインデックス ID を持ちます。詳細については、「 sys.internal_partitions」を参照してください。
`columnstore_delete_buffer_state`	tinyint	`0` = `NOT_APPLICABLE` `1` = `OPEN` `2` = `DRAINING` `3` = `FLUSHING` `4` = `RETIRING` `5` = `READY` 適用対象: SQL Server 2016 (13.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance
`columnstore_delete_buffer_state_desc`	nvarchar(60)	`NOT VALID` - 親インデックスは列ストアインデックスではありません。 `OPEN` - 削除者とスキャナーはこれを使用します。 `DRAINING` - 削除プログラムはドレインされていますが、スキャナーでは引き続き使用されます。 `FLUSHING` - バッファーが閉じられ、バッファー内の行が削除ビットマップに書き込まれます。 `RETIRING` - 閉じた削除バッファー内の行が削除ビットマップに書き込まれたが、スキャナーがまだそれを使用しているため、バッファーは切り捨てられません。オープンバッファーで十分であるため、新しいスキャナーではリタイアバッファーを使用する必要はありません。 `READY` - この削除バッファーは使用できる状態です。適用対象: SQL Server 2016 (13.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance
`version_record_count`	bigint	これは、このインデックスに保持されている行バージョンレコードの数です。これらの行バージョンは、 Accelerated データベース復旧機能によって管理されます。適用対象: SQL Server 2019 (15.x) 以降のバージョンと Azure SQL Database
`inrow_version_record_count`	bigint	高速取得のためにデータ行に保持されている ADR バージョンレコードの数。適用対象: SQL Server 2019 (15.x) 以降のバージョンと Azure SQL Database
`inrow_diff_version_record_count`	bigint	ベースバージョンとの違いの形式で保持される ADR バージョンレコードの数。適用対象: SQL Server 2019 (15.x) 以降のバージョンと Azure SQL Database
`total_inrow_version_payload_size_in_bytes`	bigint	このインデックスの行内バージョンレコードの合計サイズ (バイト単位)。適用対象: SQL Server 2019 (15.x) 以降のバージョンと Azure SQL Database
`offrow_regular_version_record_count`	bigint	元のデータ行の外側に保持されているバージョンレコードの数。適用対象: SQL Server 2019 (15.x) 以降のバージョンと Azure SQL Database
`offrow_long_term_version_record_count`	bigint	長期的と見なされるバージョンレコードの数。適用対象: SQL Server 2019 (15.x) 以降のバージョンと Azure SQL Database

Note

解説

sys.dm_db_index_physical_stats 動的管理機能は DBCC SHOWCONTIG ステートメントを置き換えます。

スキャンモード

関数が実行されるモードによって、関数で使用する統計データを取得するためのスキャンのレベルが決まります。 mode は、 LIMITED、 SAMPLED、または DETAILEDとして指定されます。この関数は、テーブルまたはインデックスの指定されたパーティションを構成する割り当て単位のページチェーンを走査します。 sys.dm_db_index_physical_stats では、実行モードに関係なく、Intent-Shared (IS) テーブルロックのみが必要です。

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

LIMITED モードでは、データベースエンジンは B ツリーの非リーフページとヒープの IAM ページと PFS ページのみをスキャンするため、compressed_page_countはNULLされます。 SAMPLED モードを使用してcompressed_page_countの推定値を取得し、DETAILED モードを使用してcompressed_page_countの実際の値を取得します。 SAMPLED モードでは、インデックスまたはヒープ内のすべてのページの 1% サンプルに基づいて統計が返されます。 SAMPLED モードの結果は概算と見なす必要があります。インデックスまたはヒープのページ数が 10,000 ページ未満の場合は、SAMPLEDではなくDETAILED モードが使用されます。

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

各モードでより多くの作業が実行されるため、モードは LIMITED から DETAILEDに徐々に遅くなります。テーブルまたはインデックスのサイズまたは断片化レベルをすばやく測定するには、 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'AdventureWorks2022'), 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 AdventureWorks2022;
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'AdventureWorks2022.Person.Address')などの 3 部構成の名前を指定するか、 sys.dm_db_index_physical_stats 関数で使用する前に関数によって返される値をテストします。次の例 A と B は、データベース ID とオブジェクト ID を指定する安全な方法を示しています。

断片化の検出

断片化は、テーブルに対して行われたデータ変更 (INSERT、 UPDATE、および DELETE ステートメント) のプロセスによって発生するため、テーブルで定義されているインデックスに対して行われます。これらの変更は通常、テーブルとインデックスの行間で均等に分散されないため、各ページのフルネスは時間の経過と同時に変化する可能性があります。テーブルのインデックスの一部または全部をスキャンするクエリの場合、この種の断片化によりページ読み取りが増え、データの並列スキャンが妨げられる可能性があります。

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

論理的な断片化

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

エクステントの断片化

これは、ヒープのリーフページ内の順序が正しくありません。順序が正しくないエクステントとは、ヒープの現在のページを含むエクステントが、前のページを含むエクステントの後に物理的に次のエクステントではないエクステントです。

パフォーマンスを最大限に高めるために、 avg_fragmentation_in_percent の値は可能な限り 0 に近い値にする必要があります。ただし、0 % から 10% までの値を許容できます。断片化を減らすすべての方法 (再構築、再構成、再作成など) を使用して、これらの値を減らすことができます。インデックスの断片化の程度を分析する方法の詳細については、「インデックスのメンテナンスを最適化してクエリのパフォーマンスを向上させ、リソースの消費量を削減するを参照してください。

インデックスの断片化を減らす

断片化がクエリのパフォーマンスに影響を与える方法でインデックスが断片化されている場合は、断片化を減らすための 3 つの選択肢があります。

クラスター化インデックスを削除して再作成します。

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

断片化だけでは、インデックスを再構成または再構築するための十分な理由ではありません。断片化の主な効果は、インデックススキャン中にページ先行読み取りスループットが低下することです。これは、応答時間の遅れの原因となります。フラグメント化されたテーブルまたはインデックスに対するクエリワークロードにスキャンが含まれていない場合、ワークロードは主にシングルトン参照であるため、断片化を削除しても効果はありません。

Note

DBCC SHRINKFILEまたはDBCC SHRINKDATABASEを実行すると、圧縮操作中にインデックスが部分的または完全に移動された場合に断片化が発生する可能性があります。そのため、圧縮操作を実行する必要がある場合は、断片化が削除される前に行う必要があります。

ヒープ内の断片化を減らす

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

注意事項

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

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

既定では、 ALTER INDEX REORGANIZE ステートメントは、ラージオブジェクト (LOB) データを含むページを圧縮します。 LOB ページは空の場合は割り当てが解除されないため、このデータを圧縮すると、大量の LOB データが削除されたり、LOB 列が削除されたりした場合に、ディスク領域の使用が向上する可能性があります。

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

ディスク領域の使用を評価する

avg_page_space_used_in_percent列は、ページのフルネスを示します。最適なディスク領域の使用を実現するには、ランダムな挿入が多くないインデックスの場合、この値は 100% に近い必要があります。ただし、多数のランダム挿入があり、ページ全体が非常に多いインデックスでは、ページ分割の数が増えます。断片化が大きくなります。したがって、ページ分割を減らすには、値を 100% 未満にする必要があります。 FILLFACTOR オプションを指定してインデックスを再構築すると、インデックスのクエリパターンに合わせてページのフルネスを変更できます。塗りつぶし係数の詳細については、「インデックスの塗りつぶし係数を指定するを参照してください。また、 ALTER INDEX REORGANIZE は、最後に指定した FILLFACTOR にページを埋めることでインデックスを圧縮します。これにより、avg_space_used_in_percent の値は増加します。 ALTER INDEX REORGANIZE では、ページの満杯を減らすことはできません。代わりに、インデックスの再構築を実行する必要があります。

インデックスフラグメントを評価する

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

制限事項

クラスター化列ストアインデックスのデータは返されません。

アクセス許可

次のアクセス許可が必要です。

CONTROL データベース内の指定されたオブジェクトに対するアクセス許可。
VIEW DATABASE STATE または VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) アクセス許可。オブジェクトワイルドカード @object_id = NULLを使用して、指定したデータベース内のすべてのオブジェクトに関する情報を返します。
VIEW SERVER STATEまたは、データベースワイルドカード @database_id = NULLを使用して、すべてのデータベースに関する情報を返すVIEW SERVER PERFORMANCE STATE (SQL Server 2022) アクセス許可。

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

VIEW DATABASE STATEを拒否すると、特定のオブジェクトに対して付与されたCONTROL権限に関係なく、データベース内のすべてのオブジェクトが返されなくなります。また、データベースワイルドカード @database_id = NULL を指定すると、データベースは省略されます。

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

例

この記事の Transact-SQL コードサンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプルデータベースを使用します。このサンプルデータベースは、Microsoft SQL Server サンプルとコミュニティプロジェクトのホームページからダウンロードできます。

A. 指定したテーブルに関する情報を返します

次の例では、Person.Address テーブルのすべてのインデックスとパーティションについて、サイズと断片化の統計を返します。スキャンモードは、最適なパフォーマンスを得るために LIMITED に設定され、返される統計情報を制限します。このクエリを実行するには、少なくとも、Person.Address テーブルに対するCONTROLアクセス許可が必要です。

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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. ヒープに関する情報を返す

次の例では、AdventureWorks2022 データベースのヒープ 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'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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 番目のパラメーターとして指定しています。

-- 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
    INNER 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. sys.dm_db_index_physical_statsを使用してページ圧縮ページの数を表示する

次の例は、ページの合計数を、行とページが圧縮されたページと比較する方法を示しています。この情報は、圧縮によってインデックスまたはテーブルに対して提供される利点を判断するために使用できます。

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
INNER JOIN sys.objects o
    ON o.object_id = ips.object_id
ORDER BY record_count DESC;

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

次の例は、 SAMPLED モードが DETAILED モードの結果とは異なる近似値を返す方法を示しています。

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

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. インデックスの断片化のために Service Broker キューにクエリを実行する

適用対象: SQL Server 2016 (13.x) 以降のバージョン

次の例は、サーバーブローカーキューに対して断片化のクエリを実行する方法を示しています。

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

次の方法で共有

sys.dm_db_index_physical_stats (Transact-SQL)

構文

引数

database_id |NULL |0 |デフォルト

object_id |NULL |0 |デフォルト

index_id | 0 |NULL |-1 |デフォルト

partition_number |NULL |0 |デフォルト

mode |NULL |デフォルト

返されるテーブル

解説

スキャンモード

システム関数を使用してパラメーター値を指定する

ベストプラクティス

断片化の検出

論理的な断片化

エクステントの断片化

インデックスの断片化を減らす

ヒープ内の断片化を減らす

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

ディスク領域の使用を評価する

インデックスフラグメントを評価する

制限事項

アクセス許可

例

A. 指定したテーブルに関する情報を返します

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

C: すべてのデータベースの情報を返す

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

E. sys.dm_db_index_physical_statsを使用してページ圧縮ページの数を表示する

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

G. インデックスの断片化のために Service Broker キューにクエリを実行する

フィードバック

その他のリソース

次の方法で共有

sys.dm_db_index_physical_stats (Transact-SQL)

構文

引数

database_id |NULL |0 |デフォルト

object_id |NULL |0 |デフォルト

index_id | 0 |NULL |-1 |デフォルト

partition_number |NULL |0 |デフォルト

mode |NULL |デフォルト

返されるテーブル

解説

スキャン モード

システム関数を使用してパラメーター値を指定する

ベスト プラクティス

断片化の検出

論理的な断片化

エクステントの断片化

インデックスの断片化を減らす

ヒープ内の断片化を減らす

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

ディスク領域の使用を評価する

インデックス フラグメントを評価する

制限事項

アクセス許可

例

A. 指定したテーブルに関する情報を返します

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

C: すべてのデータベースの情報を返す

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

E. sys.dm_db_index_physical_statsを使用してページ圧縮ページの数を表示する

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

G. インデックスの断片化のために Service Broker キューにクエリを実行する

関連するコンテンツ

フィードバック

その他のリソース

スキャンモード

ベストプラクティス

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

インデックスフラグメントを評価する