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

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

[伊東敏章，＠IT]

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（Transact-SQL）

構文と引数

構文　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」

動作例

　まずは、全ての引数に「NULL」を指定して「sys.dm_db_index_operational_stats」動的管理関数を出力します（図1）。

図1　全てのデータベースを対象に出力したところ

　システムデータベースを含めた、全てのデータベースを対象とした情報が出力されました。

　「sys.dm_db_index_operational_stats」動的管理関数ではデータベースやテーブル、インデックスは名前ではなくIDが出力されますので、名前が必要な場合にはシステム関数やシステムビューを使用する必要があります。

　次に、ユーザーデータベースに非クラスタ化インデックスが作成されたヒープテーブルを作成して、このテーブルを対象に「sys.dm_db_index_operational_stats」動的管理関数を出力します（図2）。

図2　ヒープと非クラスタ化インデックスの情報が出力された

　作成したヒープとインデックスのINSERT回数や範囲スキャンの回数、ロックの要求回数などが出力されました。

　今回は発生していませんが、ページIOラッチの回数や待ち時間の情報もあるため、インデックスが保存されたファイルグループのパフォーマンスの確認などにも使用できるかもしれません。

　次に、SQL Serverサービスを再起動して、もう一度「sys.dm_db_index_operational_stats」動的管理関数を出力します。「sys.dm_db_index_operational_stats」動的管理関数はメタデータキャッシュに記録された情報を出力しているため、サービスの再起動によりキャッシュがクリアされ、結果が出力されなくなりました（図3）。

図3　SQL Serverサービスの再起動により結果がクリアされたところ

※本Tipsは、「Windows Server 2019」上に「SQL Server 2019」をインストールした環境を想定して解説しています。

筆者紹介

椎名 武史（しいな たけし）

BIPROGY株式会社（ビプロジー）所属。Microsoft MVP for Data Platform（2017〜）。入社以来 SQL Serverの評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。

伊東 敏章（いとう としあき）

BIPROGY株式会社（ビプロジー）所属。入社以来SQL Server一筋で評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。社内のプログラミングコンテストで4回の優勝経験も持つ。趣味は輪行で週末は自転車を持っての旅行。目標は色々な日本百選を制覇すること。