sys.dm_db_index_operational_stats (Transact-SQL)

データベース内のテーブルまたはインデックスの各パーティションに対して、現在の低レベルの I/O、ロック、ラッチ、およびアクセス メソッドの利用状況を返します。

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 | DEFAULT
  データベースの 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 | DEFAULT
  インデックスがあるテーブルまたはビューのオブジェクト 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 | DEFAULT
  インデックスの ID です。 index_id のデータ型は int です。 有効な入力値は、インデックスの ID 番号、NULL、-1、または DEFAULT です。object_id がヒープの場合は、0 も有効です。 既定値は -1 です。このコンテキストでは、NULL、-1、および DEFAULT は同じ値になります。

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

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

  NULL を指定すると、インデックスまたはヒープのすべてのパーティションについてキャッシュされた情報が返されます。

  partition_number は 1 から始まります。 非パーティション インデックスまたはヒープでは、partition_number が 1 に設定されます。

返されるテーブル

説明

この動的管理オブジェクトには、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 内の値は、常に同じになります。

付加列として 1 つ以上の LOB 列を含む非クラスター化インデックスの場合、lob_fetch_in_pages および lob_fetch_in_bytes 列内の値は 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)」を参照してください。

ヒープまたはインデックスに対するメタデータがメタデータ キャッシュに組み込まれるたび、各列の値はゼロに設定されます。統計値は、キャッシュ オブジェクトがメタデータ キャッシュから削除されるまで累積されます。 したがって、キャッシュにはアクティブなヒープまたはインデックスのメタデータが常に存在し、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 権限。

• オブジェクトのワイルドカード @object\_id = NULL を使用して、特定のデータベース内にあるすべてのオブジェクトに関する情報を返す場合は、VIEW DATABASE STATE 権限が必要です。

• データベースのワイルドカード @database\_id = NULL を使用して、すべてのデータベースに関する情報を返す場合は、VIEW SERVER STATE 権限が必要です。

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

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

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

使用例

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

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

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

DECLARE @db_id int;
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_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)

概念

パフォーマンスの監視とチューニング