sys.dm_db_index_physical_stats (Transact-SQL)

[アーティクル]
03/03/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 行のデータが返されます。 xVelocity メモリが最適化された列ストアインデックスに関する情報は返されません。

Note

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

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

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

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 は同じ値になります。

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 です。

有効な入力値は、テーブルおよびビューの 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 CLUSTERED INDEX NONCLUSTERED INDEX PRIMARY XML INDEX EXTENDED INDEX XML INDEX COLUMNSTORE MAPPING INDEX (内部) COLUMNSTORE DELETEBUFFER INDEX (内部) COLUMNSTORE DELETEBITMAP INDEX (内部)
hobt_id	bigint	インデックスまたはパーティションのヒープまたは B ツリー ID。これは、ユーザー定義インデックスのhobt_idを返すだけでなく、内部列ストアインデックスのhobt_idも返します。
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_DATA アロケーションユニット、または ROW_OVERFLOW_DATA アロケーションユニット。
index_level	tinyint	インデックスの現在のレベル。インデックスのリーフレベル、ヒープ、LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットでは 0 になります。非リーフインデックスレベルの場合は 0 より大きい。 index_level インデックスのルートレベルで最も高くなります。インデックスの非リーフレベルは、 mode = DETAILED の場合にのみ処理されます。
avg_fragmentation_in_percent	float	インデックスの論理的な断片化、または IN_ROW_DATA アロケーションユニットでのヒープのエクステントの断片化。値はパーセンテージ (%) で表され、複数のファイルが考慮されます。論理的な断片化とエクステントの断片化の定義については、「解説」を参照してください。 LOB_DATA および ROW_OVERFLOW_DATA アロケーションユニットでは 0 になります。モードが SAMPLED の場合、ヒープの場合は NULL。
fragment_count	bigint	IN_ROW_DATA アロケーションユニットのリーフレベルでのフラグメントの数。フラグメントの詳細については、「解説」を参照してください。インデックスの非リーフレベル、および LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットでは NULL になります。モードが SAMPLED の場合、ヒープの場合は NULL。
avg_fragment_size_in_pages	float	IN_ROW_DATA アロケーションユニットのリーフレベルにおける、フラグメントあたりのページの平均数。インデックスの非リーフレベル、および LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットでは NULL になります。モードが SAMPLED の場合、ヒープの場合は NULL。
page_count	bigint	インデックスページまたはデータページの合計数。インデックスの場合、IN_ROW_DATA割り当て単位の B ツリーの現在のレベルのインデックスページの合計数。ヒープでは、IN_ROW_DATA アロケーションユニットに含まれるデータページの合計数になります。 LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットでは、アロケーションユニットに含まれる合計ページ数になります。
avg_page_space_used_in_percent	float	すべてのページで、使用可能なデータストレージ領域のうち使用されているパーセンテージ (%) の平均。インデックスの場合、average は、IN_ROW_DATA割り当て単位の B ツリーの現在のレベルに適用されます。ヒープでは、IN_ROW_DATA アロケーションユニットに含まれるすべてのデータページの平均になります。 LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットでは、アロケーションユニットに含まれるすべてのページの平均になります。モードが LIMITED の場合は NULL。
record_count	bigint	レコードの総数。インデックスの場合、レコードの合計数は、IN_ROW_DATA割り当て単位の B ツリーの現在のレベルに適用されます。ヒープでは、IN_ROW_DATA アロケーションユニットに含まれるレコードの合計数になります。メモ：ヒープの場合、この関数から返されるレコードの数が、SELECT COUNT() をヒープに対して実行して返される行数と一致しない可能性があります。これは、1 行に複数のレコードが含まれる場合があるためです。たとえば、更新の状況によっては、更新操作の結果として転送元レコードと転送先レコードが 1 つのヒープ行に含まれることがあります。また、大きな LOB 行のほとんどは、LOB_DATA ストレージ内で複数のレコードに分割されます。 LOB_DATA または ROW_OVERFLOW_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	最小レコードサイズ (バイト単位)。インデックスの場合、最小レコードサイズは、IN_ROW_DATA割り当て単位の B ツリーの現在のレベルに適用されます。ヒープでは、IN_ROW_DATA アロケーションユニットに含まれる最小レコードサイズになります。 LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットでは、アロケーションユニット全体での最小レコードサイズになります。モードが LIMITED の場合は NULL。
max_record_size_in_bytes	int	最大レコードサイズ (バイト単位)。インデックスの場合、最大レコードサイズは、IN_ROW_DATA割り当て単位の B ツリーの現在のレベルに適用されます。ヒープでは、IN_ROW_DATA アロケーションユニットに含まれる最大レコードサイズになります。 LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットでは、アロケーションユニット全体での最大レコードサイズになります。モードが LIMITED の場合は NULL。
avg_record_size_in_bytes	float	平均レコードサイズ (バイト単位)。インデックスの場合、平均レコードサイズは、IN_ROW_DATA割り当て単位の B ツリーの現在のレベルに適用されます。ヒープでは、IN_ROW_DATA アロケーションユニットに含まれる平均レコードサイズになります。 LOB_DATA または ROW_OVERFLOW_DATA アロケーションユニットでは、アロケーションユニット全体での平均レコードサイズになります。モードが LIMITED の場合は NULL。
forwarded_record_count	bigint	別のデータの場所への転送ポインターを持つ、ヒープ内の転送されたレコード数 (この状態は、更新中に、新しい行を格納できる十分なスペースが元の場所にない場合に発生します)。ヒープの IN_ROW_DATA アロケーションユニット以外のアロケーションユニットでは NULL になります。モードが LIMITED の場合、ヒープの場合は NULL。
compressed_page_count	bigint	圧縮されたページ数。ヒープの場合、新しく割り当てられたページはページ圧縮されません。ヒープは、2 つの特殊な条件、つまりデータを一括インポートする場合、またはヒープを再構築する場合に、ページ圧縮されます。ページ割り当ての原因となる通常の DML 操作ではページ圧縮されません。 compressed_page_count の値が目標のしきい値を超えた場合は、ヒープを再構築してください。クラスター化インデックスを含むテーブルの場合、compressed_page_count の値はページ圧縮の効果を示します。
hobt_id	bigint	列ストアインデックスの場合のみ、これはパーティションの内部列ストアデータを追跡する行セットの ID です。行セットは、データヒープまたは B ツリーとして格納されます。親列ストアインデックスと同じインデックス ID を持ちます。詳細については、「 sys.internal_partitions (Transact-SQL)」を参照してください。 NULL の場合適用対象: SQL Server 2016 以降、Azure SQL Database、Azure SQL Managed Instance
columnstore_delete_buffer_state	tinyint	0 = NOT_APPLICABLE 1 = OPEN 2 = ドレイン 3 = フラッシュ 4 = 廃止 5 = READY 適用対象: SQL Server 2016 以降、Azure SQL Database、Azure SQL Managed Instance
columnstore_delete_buffer_state_desc		NOT VALID -親インデックスは列ストアインデックスではありません。 OPEN - 削除者とスキャナーはこれを使用します。ドレイン - 削除ツールはドレインされていますが、スキャナーでは引き続き使用されます。 FLUSHING - バッファーが閉じられ、バッファー内の行が削除ビットマップに書き込まれます。 RETIRING - 閉じた削除バッファー内の行は削除ビットマップに書き込まれていますが、スキャナーがまだ使用しているため、バッファーは切り捨てられません。新しいスキャナーでは、開いているバッファーで十分であるため、廃止バッファーを使用する必要はありません。 READY - この削除バッファーは使用できる状態です。適用対象: SQL Server 2016 以降、Azure SQL Database、Azure SQL Managed Instance
version_record_count	bigint	これは、このインデックスで保持されている行バージョンレコードの数です。これらの行バージョンは、高速データベース復旧機能によって管理されます。適用対象:SQL Server 2019 (15.x)、Azure SQL Database
inrow_version_record_count	bigint	高速取得のためにデータ行に保持されている ADR バージョンレコードの数。適用対象:SQL Server 2019 (15.x)、Azure SQL Databasee
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 ステートメントの代わりに使用できます。

スキャンモード

関数が実行されるモードによって、関数で使用する統計データを取得するためのスキャンのレベルが決まります。 モード は、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、SAMPLED、DETAILED の順に増加し、それに応じて処理時間は遅くなります。テーブルまたはインデックスの断片化レベルのサイズをすばやく計測するには、LIMITED モードを使用します。 LIMITED モードは最も高速で、インデックスの IN_ROW_DATA アロケーションユニットにおける各非リーフレベルの行は返されません。

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

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

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

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

推奨事項

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

断片化の検出

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

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

論理的な断片化

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

エクステントの断片化

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

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

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

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

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

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

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

注意

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 つのインデックスには少なくとも 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'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. ヒープに関する情報を返す

次の例では、AdventureWorks2019 データベース内のヒープ 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'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. すべてのデータベースの情報を返す

次の例では、すべてのパラメーターにワイルドカード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  
        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  
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 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. インデックスの断片化に関する Service Broker キューのクエリ


適用対象: SQL Server 2016 (13.x) から SQL Server。

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

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

参照

動的管理ビューと動的管理関数 (Transact-SQL)
インデックス関連の動的管理ビューと関数 (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
システムビュー (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

構文

引数

返されるテーブル

注釈

スキャンモード

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

推奨事項

断片化の検出

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

ヒープの断片化の解消

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

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

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

制限事項と制約事項

アクセス許可

例

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)

構文

引数

返されるテーブル

注釈

スキャン モード

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

推奨事項

断片化の検出

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

ヒープの断片化の解消

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

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

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

制限事項と制約事項

アクセス許可

例

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 キューのクエリ

参照

その他のリソース

その他のリソース

スキャンモード

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