sys.dm_db_index_operational_stats (Transact-SQL)

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

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

組み込み関数 DB_ID を指定できます。

object_id |NULL |0 |デフォルト

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

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

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

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

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

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

partition_number |NULL |0 |デフォルト

オブジェクト内のパーティション番号を指定します。 partition_numberint です。有効な入力は、インデックスまたはヒープ、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) ページの累積数。 これらのページには、 textntextimagevarchar(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_countpage_latch_wait_in_ms

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

  • row_lock_countpage_lock_count

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

  • row_lock_wait_in_mspage_lock_wait_in_ms

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

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

  • page_io_latch_wait_countpage_io_latch_wait_in_ms

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

列の解説

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