sys.dm_db_index_physical_stats (Transact-SQL)

[アーティクル]
05/25/2023

適用対象: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

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

sys.dm_db_index_physical_stats は、メモリ最適化列ストアインデックスに関する情報を返しません。メモリ最適化インデックスの使用については、「 sys.dm_db_xtp_index_stats (Transact-SQL)」を参照してください。

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

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 です。有効な入力は、データベースの ID、NULL、0、または DEFAULT です。既定値は 0 です。 NULL、0、DEFAULT は、このコンテキストで同等の値です。

null を指定すると、SQL Serverのインスタンス内のすべてのデータベースの情報が返されます。 database_idに NULL を指定する場合は、object_id、index_id、partition_numberにも NULL を指定する必要があります。

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

object_id |NULL |0 |既定

インデックスがオンになっているテーブルまたはビューのオブジェクト ID。 object_id は int です。

有効な入力は、テーブルとビューの ID、NULL、0、または DEFAULT です。既定値は 0 です。 NULL、0、DEFAULT は、このコンテキストで同等の値です。 2016 (13.x) SQL Server時点で、有効な入力には、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_number 1 に設定されています。

mode |NULL |既定

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

Note

注釈

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

スキャンモード

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

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

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

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

各モードで実行される処理は LIMITED、SAMPLED、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

ベストプラクティス

または OBJECT_IDを使用DB_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 (Transact-SQL)」を参照してください。
の置換である ALTER INDEX REORGANIZE を使用して、インデックスのリーフレベルページを論理的な順序で並べ替えます DBCC INDEXDEFRAG。これはオンライン操作のため、ステートメントの実行中もインデックスを使用できます。操作を中断しても、それまでに完了した作業は失われません。この方法の欠点は、データをインデックス再構築操作として再構成する場合ほど適切なジョブを実行せず、統計を更新しない点です。
の代わりに DBCC DBREINDEXALTER INDEX REBUILD を使用して、インデックスをオンラインまたはオフラインで再構築します。詳細については、「ALTER INDEX (Transact-SQL)」を参照してください。

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

注意

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

ヒープの断片化を減らす

ヒープのエクステントの断片化を解消するには、テーブルにクラスター化インデックスを作成し、インデックスを削除します。これによって、クラスター化インデックスの作成中にデータが再分配されます。この操作では、データベースの空き領域の分布を考慮に入れて、可能な限り最適化も行われます。その後、クラスター化インデックスを削除してヒープを再作成すると、データは移動せず、最適な位置に残ります。この操作の実行方法については、「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 つのインデックスには少なくとも 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 PERFORMANCE STATE (SQL Server 2022) アクセス許可。
データベースワイルドカード @database_id = NULL を使用して、すべてのデータベースに関する情報を返す VIEW SERVER STATE または VIEW SERVER PERFORMANCE STATE (SQL Server 2022) アクセス許可。

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

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

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

例

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 データが含まれているため、ヒープのデータページを格納している LOB_DATA アロケーションユニットの行だけではなく、IN_ROW_ALLOCATION_UNIT アロケーションユニットの行も返されます。このクエリを実行するには、少なくとも 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 番目のパラメーターとして指定しています。現在のデータベースの互換性レベルが 80 以下の場合は、エラーが生成されます。このエラーを解決するには、DB_ID() を有効なデータベース名で置き換えます。データベース互換性レベルの詳細については、「 ALTER DATABASE 互換性レベル (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
    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. インデックスの断片化のためにサービスブローカーキューにクエリを実行する

適用対象: 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. インデックスの断片化のためにサービスブローカーキューにクエリを実行する

関連項目

その他のリソース

その他のリソース

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

関連項目

その他のリソース

その他のリソース

スキャンモード

ベストプラクティス

ラージオブジェクトデータを圧縮する

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

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

E. ページ圧縮ページの数を表示するには、を使用 `sys.dm_db_index_physical_stats` します。

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

G. インデックスの断片化のためにサービスブローカーキューにクエリを実行する