sys.dm_db_index_operational_stats (Transact-SQL)

[アーティクル]
07/05/2024

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

データベース内のテーブルまたはインデックスの各パーティションについて、現在の下位レベルの I/O、ロック、ラッチ、およびアクセス方法のアクティビティを返します。

メモリ最適化インデックスは、この DMV には表示されません。

Note

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

Transact-SQL 構文表記規則

構文

sys.dm_db_index_operational_stats (    
    { database_id | NULL | 0 | DEFAULT }    
  , { object_id | NULL | 0 | DEFAULT }    
  , { index_id | 0 | NULL | -1 | DEFAULT }    
  , { partition_number | NULL | 0 | 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 を指定できます。

object_id |NULL |0 |デフォルト

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

有効な入力は、テーブルとビューの ID 番号、NULL、0、または DEFAULT です。既定値は 0 です。 NULL、0、DEFAULT は、このコンテキストで同等の値です。

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

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

インデックスの 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 に設定されています。

返されるテーブル

列名	データ型	説明
database_id	smallint	データベース ID。 Azure SQL Database では、値は 1 つのデータベースまたは Elastic Pool 内で一意ですが、論理サーバー内では一意ではありません。
object_id	int	テーブルまたはビューの ID。
index_id	int	インデックスまたはヒープの ID。 0 = ヒープ
partition_number	int	インデックスまたはヒープ内の、1 から始まるパーティション番号。
hobt_id	bigint	適用対象: SQL Server 2016 (13.x) 以降のバージョンの Azure SQL Database。列ストアインデックスの内部データを追跡するデータヒープまたは B ツリー行セットの ID。 NULL - これは内部列ストア行セットではありません。詳細については、「 sys.internal_partitions (Transact-SQL)」を参照してください。
leaf_insert_count	bigint	リーフレベルの挿入の累積数。
leaf_delete_count	bigint	リーフレベルの削除の累積数。 leaf_delete_countは、最初にゴーストとしてマークされていない削除されたレコードに対してのみインクリメントされます。最初にゴースト化された削除済みレコードの場合、代わりに leaf_ghost_count がインクリメントされます。
leaf_update_count	bigint	リーフレベルの更新の累積数。
leaf_ghost_count	bigint	削除済みとしてマークされているが、まだ削除されていないリーフレベルの行の累積数。このカウントには、ゴーストとしてマークされずにすぐに削除されるレコードは含まれません。このような行は、設定された間隔でクリーンアップスレッドにより削除されます。この値には、未処理のスナップショット分離トランザクションが原因で保持されている行の数は含まれません。
nonleaf_insert_count	bigint	リーフレベルより上の挿入の累積数。 0 = ヒープまたは列ストア
nonleaf_delete_count	bigint	リーフレベルより上の削除の累積数。 0 = ヒープまたは列ストア
nonleaf_update_count	bigint	リーフレベルより上の更新の累積数。 0 = ヒープまたは列ストア
leaf_allocation_count	bigint	インデックスまたはヒープ内のリーフレベルのページ割り当ての累積カウント。インデックスの場合、ページ割り当てとページ分割は対応しています。
nonleaf_allocation_count	bigint	リーフレベルを超えるページ分割によって発生するページ割り当ての累積カウント。 0 = ヒープまたは列ストア
leaf_page_merge_count	bigint	リーフレベルでのページマージの累積カウント。列ストアインデックスの場合は常に 0。
nonleaf_page_merge_count	bigint	リーフレベルを超えるページマージの累積カウント。 0 = ヒープまたは列ストア
range_scan_count	bigint	インデックスまたはヒープで開始された範囲スキャンとテーブルスキャンの累積カウント。
singleton_lookup_count	bigint	インデックスまたはヒープからの 1 行の取得の累積カウント。
forwarded_fetch_count	bigint	前方向レコードを介してフェッチされた行の数。 0 = インデックス
lob_fetch_in_pages	bigint	LOB_DATA アロケーションユニットから取得したラージオブジェクト (LOB) ページの累積数。これらのページには、 text、 ntext、 image、 varchar(max)、 nvarchar(max)、 varbinary(max)、および xml の列に格納されるデータが含まれています。詳細については、「データ型 (Transact-SQL)」を参照してください。
lob_fetch_in_bytes	bigint	取得された LOB データバイトの累積カウント。
lob_orphan_create_count	bigint	一括操作用に作成された、孤立した LOB 値の累積数。 0 = 非クラスター化インデックス
lob_orphan_insert_count	bigint	一括操作中に挿入された孤立 LOB 値の累積カウント。 0 = 非クラスター化インデックス
row_overflow_fetch_in_pages	bigint	ROW_OVERFLOW_DATAアロケーションユニットから取得された行オーバーフローデータページの累積カウント。これらのページには、 varchar(n)、 nvarchar(n)、 varbinary(n)、および行外にプッシュされた sql_variant の列に格納されているデータが含まれています。
row_overflow_fetch_in_bytes	bigint	取得された行オーバーフローデータバイトの累積カウント。
column_value_push_off_row_count	bigint	挿入または更新された行をページ内に収めるため、行外に出された LOB データおよび行オーバーフローデータに対する列値の累積数。
column_value_pull_in_row_count	bigint	行内でプルされる LOB データと行オーバーフローデータの列値の累積カウント。これは、更新操作によってレコード内の領域が解放され、LOB_DATAまたはROW_OVERFLOW_DATA割り当て単位から 1 つ以上の行外の値をIN_ROW_DATA割り当て単位にプルする機会が提供される場合に発生します。
row_lock_count	bigint	要求された行ロックの累積数。
row_lock_wait_count	bigint	データベースエンジンが行ロックを待機した累積回数。
row_lock_wait_in_ms	bigint	データベースエンジンが行ロックで待機した合計時間 (ミリ秒)。
page_lock_count	bigint	要求されたページロックの累積数。
page_lock_wait_count	bigint	データベースエンジンがページロックを待機した累積回数。
page_lock_wait_in_ms	bigint	データベースエンジンがページロックで待機した合計ミリ秒数。
index_lock_promotion_attempt_count	bigint	データベースエンジンがロックをエスカレートしようとした累積回数。
index_lock_promotion_count	bigint	データベースエンジンがロックをエスカレートした累積回数。
page_latch_wait_count	bigint	ラッチの競合により、データベースエンジンが待機した累積回数。
page_latch_wait_in_ms	bigint	ラッチの競合により、データベースエンジンが待機した累積ミリ秒数。
page_io_latch_wait_count	bigint	データベースエンジンが I/O ページラッチで待機した累積回数。
page_io_latch_wait_in_ms	bigint	ページ I/O ラッチでデータベースエンジンが待機した累積ミリ秒数。
tree_page_latch_wait_count	bigint	上位レベルの B ツリーページのみを含む page_latch_wait_count のサブセット。ヒープまたは列ストアインデックスの場合は常に 0。
tree_page_latch_wait_in_ms	bigint	上位レベルの B ツリーページのみを含む page_latch_wait_in_ms のサブセット。ヒープまたは列ストアインデックスの場合は常に 0。
tree_page_io_latch_wait_count	bigint	上位レベルの B ツリーページのみを含む page_io_latch_wait_count のサブセット。ヒープまたは列ストアインデックスの場合は常に 0。
tree_page_io_latch_wait_in_ms	bigint	上位レベルの B ツリーページのみを含む page_io_latch_wait_in_ms のサブセット。ヒープまたは列ストアインデックスの場合は常に 0。
page_compression_attempt_count	bigint	テーブル、インデックス、またはインデックス付きビューの特定のパーティションに対して PAGE レベルの圧縮が評価されたページの数。大幅な節約が実現できなかったため、圧縮されなかったページが含まれます。列ストアインデックスの場合は常に 0。
page_compression_success_count	bigint	テーブル、インデックス、またはインデックス付きビューの特定のパーティションで、ページの圧縮を使用して圧縮されたデータページの数。列ストアインデックスの場合は常に 0。

Note

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

解説

この動的管理オブジェクトは、 CROSS APPLY および OUTER APPLYからの相関パラメーターを受け入れません。

sys.dm_db_index_operational_statsを使用して、ユーザーがテーブル、インデックス、またはパーティションの読み取りまたは書き込みを待機する必要がある時間の長さを追跡し、重要な I/O アクティビティまたはホットスポットが発生しているテーブルまたはインデックスを識別できます。

競合について確認するには、次の列を使用します。

テーブルまたはインデックスパーティションに対する一般的なアクセスパターンを分析するには次の列を使用します。

leaf_insert_count
leaf_delete_count
leaf_update_count
leaf_ghost_count
range_scan_count
singleton_lookup_count

ラッチおよびロックの競合を特定する場合、次の列を使用します。

page_latch_wait_count と page_latch_wait_in_ms

これらの列では、インデックスまたはヒープにラッチ競合があるかどうかが示されます。また競合の重大度も示されます。
row_lock_count と page_lock_count

これらの列は、データベースエンジンが行ロックとページロックを取得しようとした回数を示します。
row_lock_wait_in_ms と page_lock_wait_in_ms

これらの列は、インデックスまたはヒープにロック競合があるかどうか、および競合の重要性を示します。

インデックスまたはヒープパーティション上の物理 I/O の統計情報を分析するには

page_io_latch_wait_count と page_io_latch_wait_in_ms

これらの列では、インデックスまたはヒープページをメモリ内に移動するために、物理 I/O が発行されたかどうかが示されます。また I/O の発行回数も示されます。

列の解説

lob_orphan_create_countとlob_orphan_insert_countの値は常に等しい必要があります。

列 lob_fetch_in_pages および lob_fetch_in_bytes の値は、含まれる列として 1 つ以上の LOB 列を含む非クラスター化インデックスの場合、0 より大きい値にすることができます。詳細については、「付加列インデックスの作成」を参照してください。同様に、非クラスター化インデックスに行外にプッシュできる列が含まれている場合、 row_overflow_fetch_in_pages 列と row_overflow_fetch_in_bytes 列の値は 0 より大きくなる可能性があります。

メタデータキャッシュ内のカウンターのリセット方法

sys.dm_db_index_operational_statsによって返されるデータは、ヒープまたはインデックスを表すメタデータキャッシュオブジェクトが使用可能な限り存在します。このデータは持続性はなく、トランザクション上の一貫性もありません。つまり、これらのカウンターを使用して、インデックスが使用されたかどうか、またはインデックスが最後に使用された日時を判断することはできません。詳細については、「 sys.dm_db_index_usage_stats (Transact-SQL)」を参照してください。

ヒープまたはインデックスのメタデータがメタデータキャッシュに取り込まれ、キャッシュオブジェクトがメタデータキャッシュから削除されるまで統計が累積されるたびに、各列の値は 0 に設定されます。そのため、アクティブなヒープまたはインデックスは、キャッシュ内に常にそのメタデータを持つ可能性があり、累積カウントには、SQL Server のインスタンスが最後に開始されてからのアクティビティが反映される場合があります。あまりアクティブでないヒープまたはインデックスのメタデータは、キャッシュの使用中に入ったり、キャッシュの外に移動したりします。その結果、使用できる値が存在する場合と、存在しない場合が発生します。インデックスを削除すると、対応する統計はメモリから削除され、この関数ではレポートされなくなります。インデックスに対するその他の DDL 操作によって、統計の値がゼロにリセットされる場合もあります。

システム関数を使用したパラメーター値の指定

Transact-SQL 関数の DB_ID と OBJECT_ID を使用して、 database_id パラメーターと object_id パラメーターの値を指定できます。ただし、これらの関数に無効な値を渡すと、意図しない結果が発生する可能性があります。 DB_IDまたはOBJECT_IDを使用する場合は、必ず有効な ID が返されることを確認してください。詳細については、「 sys.dm_db_index_physical_stats (Transact-SQL)の「解説」セクションを参照してください。

アクセス許可

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

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

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

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

詳細については、「 Dynamic Management Views and Functions (Transact-SQL)」を参照してください。

例

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

次の例では、AdventureWorks2022 データベース内の Person.Address テーブルのすべてのインデックスとパーティションに関する情報を返します。このクエリを実行するには、少なくとも、 Person.Address テーブルに対する CONTROL アクセス許可が必要です。

重要

DB_ID Transact-SQL 関数を使用してパラメーター値を返OBJECT_ID場合は、常に有効な ID が返されることを確認してください。存在しない場合やスペルが正しくない場合など、データベースまたはオブジェクト名が見つからない場合、両方の関数が NULL を返します。 sys.dm_db_index_operational_stats 関数では、NULL 値はすべてのデータベースまたはすべてのオブジェクトを指定するワイルドカード値として解釈されます。これは、意図しない操作である可能性があるため、このセクションの例では、データベースおよびオブジェクト ID を確認する安全な方法を示します。

DECLARE @db_id int;    
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_operational_stats(@db_id, @object_id, NULL, NULL);    
  END;    
GO

B. すべてのテーブルとインデックスの情報を返す

次の例では、SQL Server のインスタンス内のすべてのテーブルとインデックスの情報を返します。このクエリを実行するには、VIEW SERVER STATE 権限が必要です。

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

参照

動的管理ビューと動的管理関数 (Transact-SQL)
インデックス関連の動的管理ビューと関数 (Transact-SQL)
パフォーマンスの監視とチューニング
 sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)

次の方法で共有

sys.dm_db_index_operational_stats (Transact-SQL)

構文

引数

返されるテーブル

解説

列の解説

メタデータキャッシュ内のカウンターのリセット方法

システム関数を使用したパラメーター値の指定

アクセス許可

例

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

B. すべてのテーブルとインデックスの情報を返す

参照

フィードバック

その他のリソース

次の方法で共有

sys.dm_db_index_operational_stats (Transact-SQL)

構文

引数

返されるテーブル

解説

列の解説

メタデータ キャッシュ内のカウンターのリセット方法

システム関数を使用したパラメーター値の指定

アクセス許可

例

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

B. すべてのテーブルとインデックスの情報を返す

参照

フィードバック

その他のリソース

メタデータキャッシュ内のカウンターのリセット方法