データファイルとログファイルのI/O統計に関する情報を出力する：SQL Server動的管理ビューレファレンス（123）

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、データファイルとログファイルのI/O統計に関する情報の出力について解説します。

[椎名武史，＠IT]

SQL Server動的管理ビュー一覧

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は動的管理関数「sys.dm_io_virtual_file_stats」における、データファイルとログファイルのI/O統計に関する情報の出力について解説します。対応バージョンは、SQL Server（サポートされている全てのバージョン）、「Azure SQL Database」「Azure SQL Managed Instance」「Azure Synapse Analytics」「Analytics Platform System」（PDW）です。

概要

　SQL Serverでは、ファイルグループを使用することでテーブルなどのオブジェクトを配置するファイルを決めることができます。負荷の少ないファイルに配置するために既存ファイルのI/O量を確認したい場合がありますが、パフォーマンスカウンターでは論理ディスク単位のI/O状況しか確認できません。

　「sys.dm_io_virtual_file_stats」では、データファイルとログファイルのI/O統計に関する情報を出力します。

• sys.dm_io_virtual_file_stats（Transact-SQL）

構文と引数

構文　sys.dm_io_virtual_file_stats (database_id, file_id)

列名	データ型	説明
database_id	int	データベースのID
file_id	int	ファイルのID

出力内容

列名	データ型	説明
database_id	smallint	データベースのID
file_id	smallint	ファイルのID
sample_ms	bigint	コンピュータの起動後から経過した時間（ミリ秒単位）
num_of_reads	bigint	ファイルに対して発行された読み取りの数
num_of_bytes_read	bigint	ファイルで読み込まれた総バイト数
io_stall_read_ms	bigint	ファイルの読み取りをユーザーが待機した合計時間（ミリ秒単位）
io_stall_queued_read_ms	bigint	リソースガバナーによる読み取りを待機した合計時間（ミリ秒単位）
num_of_writes	bigint	このファイルに対して行われた書き込みの数
num_of_bytes_written	bigint	ファイルに書き込まれた総バイト数
io_stall_write_ms	bigint	ファイルの書き込みをユーザーが待機した合計時間（ミリ秒単位）
io_stall_queued_write_ms	bigint	リソースガバナーによる書き込みを待機した合計時間（ミリ秒単位）
io_stall	bigint	ファイルでのI/Oの完了をユーザーが待機した合計時間（ミリ秒単位）
size_on_disk_bytes	bigint	ファイルサイズ
file_handle	varbinary	このファイルのWindowsのファイルハンドル

動作例

　SQL Serverを起動してしばらくしてから「sys.dm_io_virtual_file_stats」を実行すると、データファイルとログファイルのI/O統計に関する情報が出力されました（図1）。

図1　I/O統計に関する情報が出力された

　「database_id」列と「file_id」列から対象のファイルを特定できますが、Microsoftが提供している「Handle」というツールを使用することで、「file_handle」列の情報から対象のファイルを特定することもできます（図2）。

図2　「handle.exe」でファイルハンドルと対象ファイルのひも付けが確認できた

　「handle.exe」をダウンロードして、引数としてファイルパスに「MSSQL\DATA」が含まれているファイルハンドルだけを出力させました。

　「sys.dm_io_virtual_file_stats」で「database_id」列が「1」、「faile_id」列が「1」である「master.mdf」ファイルの「file_handle」列は「0x000000000000099C」と出力されており、「handle.exe」の出力結果の一番上も、ファイルハンドル「99C」がKドライブ上の「master.mdf」であることが確認できます。

• Handle

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

筆者紹介

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

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

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

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