sys.dm_db_index_operational_stats (Transact-SQL)
データベース内のテーブルまたはインデックスの各パーティションに対して、現在の低レベルの I/O、ロック、ラッチ、およびアクセス メソッドの利用状況を返します。
.gif "トピック リンク アイコン")
構文
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 に設定されます。
返されるテーブル
列名 |
データ型 |
説明 |
|---|---|---|
database_id |
smallint |
データベース ID。 |
object_id |
int |
テーブルまたはビューの ID。 |
index_id |
int |
インデックスまたはヒープの ID。 0 = ヒープ |
partition_number |
int |
インデックスまたはヒープ内の、1 から始まるパーティション番号。 |
leaf_insert_count |
bigint |
リーフレベルの挿入の累積数。 |
leaf_delete_count |
bigint |
リーフレベルの削除の累積数。 |
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 |
リーフ レベルでページをマージした累積数。 |
nonleaf_page_merge_count |
bigint |
リーフ レベルより上でページをマージした累積数。 0 = ヒープ |
range_scan_count |
bigint |
インデックスまたはヒープで開始された範囲スキャンとテーブル スキャンの累積数。 |
singleton_lookup_count |
bigint |
インデックスまたはヒープから取得した単一行の累積数。 |
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 型の列に格納されているデータが含まれます。詳細については、「8 KB を超える場合の行オーバーフロー データ」を参照してください。アロケーション ユニットの詳細については、「テーブルとインデックスの編成」を参照してください。 |
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 アロケーション ユニットから IN_ROW_DATA アロケーション ユニットに 1 つ以上の行外値を取り込めるようになったときに発生します。アロケーション ユニットの詳細については、「テーブルとインデックスの編成」を参照してください。 |
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_compression_success_count |
bigint |
テーブル、インデックス、またはインデックス付きビューの特定のパーティションで、ページの圧縮を使用して圧縮されたデータ ページの数。 |
説明
この動的管理オブジェクトには、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 より大きくなります。詳細については、「8 KB を超える場合の行オーバーフロー データ」を参照してください。
メタデータ キャッシュ内のカウンタのリセット方法
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. 指定したテーブルについての情報を返す
次の例では、AdventureWorks データベース内にある Person.Address テーブルのすべてのインデックスとパーティションについて、情報を返します。このクエリを実行するには、少なくとも Person.Address テーブルに対する CONTROL 権限が必要です。
.gif "重要な注意事項") 重要 |
|---|
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'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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
関連項目