検索
連載

インデックスに対して実行されたアクティビティー情報の一覧を出力するSQL Server動的管理ビューレファレンス(114)

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、インデックスに対して実行されたアクティビティー情報の一覧を出力する方法について解説します。

PC用表示 関連情報
Share
Tweet
LINE
Hatena

SQL Server動的管理ビュー一覧

 本連載では、「Microsoft SQL Server(以下、SQL Server)」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は動的管理ビュー「sys.dm_db_index_operational_stats」における、インデックスに対して実行されたアクティビティー情報の一覧を出力する方法について解説します。対応バージョンは、SQL Server(サポートされている全てのバージョン)と「Azure SQL Database」「Azure SQL Managed Instance」です。

概要

 SQL Serverでは、テーブルやビューにインデックスを作成することで、効率的にデータ検索やテーブル間の結合を実行できます。データの検索やテーブル間の結合に適したインデックスが作成されていない場合には、データの検索のためにテーブルのデータ全体を読み込む必要があり、クエリの実行は非効率なものとなります。

 「sys.dm_db_index_operational_stats」動的管理関数では、メタデータキャッシュに保存されているインデックスの更新回数や参照回数、ロック要求、ページIOラッチなど、インデックスに対して実行された操作の回数や待機時間などのアクティビティー情報を確認できます。出力される情報はメタデータキャッシュに保存されているため、インスタンスの再起動などにより削除されます。

 「sys.dm_db_index_operational_stats」動的管理関数を使用することで、インデックスの使用状況やロック競合の発生状況、IO待機状況などを分析できるため、クエリのパフォーマンス改善に役立てることが可能です。

構文と引数

構文 sys.dm_db_index_operational_stats (database_id, object_id, index_id, partition_number)

列名 データ型 既定値 説明
database_id smallint 0 対象とするデータベースのID
有効な入力値はデータベースのID、「NULL」「0」「DEFAULT」
「NULL」「0」「DEFAULT」を指定した場合、全てのデータベースに関する情報を返す
全てのデータベースを対象とする場合、ほかの引数も「NULL」を指定
object_id int 0 対象とするインデックスがあるテーブルまたはビューのオブジェクトID
有効な入力値は、テーブルおよびビューのID、「NULL」「0」「DEFAULT」
「NULL」「0」「DEFAULT」を指定した場合、データベース内にある全てのテーブルとビューが対象
「NULL」「0」「DEFAULT」を指定した場合は、「index_id」「partition_number」も「NULL」を指定
index_id int -1 対象とするインデックスのID
有効な入力値はインデックスのID、「0」(ヒープの場合)、「NULL」「-1」「DEFAULT」
「NULL」「-1」「DEFAULT」を指定した場合、対象テーブルまたはビュー内の全インデックスが対象
「NULL」「-1」「DEFAULT」を指定した場合は、「partition_number」も「NULL」を指定
partition_number int 0 対象とするオブジェクト内のパーティション番号
有効な入力値は、インデックスまたはヒープのパーティション番号、「NULL」「0」「DEFAULT」
「NULL」「0」「DEFAULT」を指定した場合、インデックスまたはヒープの全パーティションが対象
非パーティションインデックスまたはヒープ場合は「1」

出力内容

列名 データ型 説明
database_id smallint データベースID
object_id int テーブルまたはビューのID
index_id int インデックスまたはヒープのID
 「0」=ヒープ
partition_number int インデックスまたはヒープ内の「1」から始まるパーティション番号
hobt_id bigint 列ストアインデックスの内部データを追跡するためのデータヒープまたはBツリー行セットのID
「NULL」=内部列ストア行セットではない
対象:SQL Server 2016以降、Azure 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 インデックスまたはヒープから単一行を取得した累積数
forwarded_fetch_count bigint 前方向レコードを介してフェッチされた行の数
 「0」=インデックス
lob_fetch_in_pages bigint LOB_DATAアロケーションユニットから取得したラージオブジェクト(LOB)ページの累積数
これらのページには、text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml型の列に格納されているデータが含まれる
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アロケーションユニットから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 テーブル、インデックス、またはインデックス付きビューの特定のパーティションで、ページレベルの圧縮が評価されたページの数
節減を実現できないため圧縮されなかったページも含む
列ストアインデックスでは、常に「0」
page_compression_success_count bigint テーブル、インデックス、またはインデックス付きビューの特定のパーティションで、ページの圧縮を使用して圧縮されたデータページの数
列ストアインデックスでは、常に「0」

動作例

Copyright © ITmedia, Inc. All Rights Reserved.

ページトップに戻る