SQL Server のデータファイル / ログファイルの書き込み状況を確認する方法などを少しまとめてみたいと思います。
■データファイルの書き込み
今回は一般的な OLTP におけるデータファイルへの書き込みを対象としてまとめてみたいと思います。
SQL Server のデータファイルへの書き込みですが、更新があったら直接データファイルに書き込みが行われるのではなく、変更はメモリ上のデータに対して適用され、チェックポイントが発生したタイミングでメモリからデータファイルへ書き込みが行われるのが一般的な動作となります。
そのため、データファイルへの書き込み状況を把握するためにはチェックポイント発生時のデータ書き込み状況を取得するとよいかと思います。
パフォーマンスモニターで確認する場合は、[Buffer ManagerCheckpoint pages/sec] を見るとよいかと思います。
SQL Server: Buffer Manager オブジェクト
このカウンタはチェックポイント (メモリ上のデータをディスク上のデータファイルに反映し整合点を保つ) が発生した場合にカウンターが上昇するため、データファイルへの書き込み状況を把握する際に使用することができます。
カウンターは pages/sec となっていますので、秒間の 8KB ページ数であらわされます。
そのため、カウンターの値に 8 KB を乗算したものが書き込まれているデータサイズになります。
このカウンターを確認することでデータファイルへの書き込み状況を確認することができます。
なお、チェックポイントの発生状況もこのカウンターで確認することができますね。
■ログファイルへの書き込み
SQL Server のログファイルへの書き込みはトランザクションを保証するために先行ログ書き込みによりログファイルに直接書き込みが行われているというイメージがあるかと思います。
実際には、ログバッファというメモリがログファイルまでの間にあり、ログファイルへの適用もメモリが間に入っています。
ロールバックなどはこのログバッファ内だけで完結することがあり、ログファイルに書き込みを行わずに変更をキャンセルすることができたりもします。
ログのフラッシュ状況も先ほどと同様で、パフォーマンスモニターから [DatabasesLog Bytes Flushed/sec] を確認をすることができます。
SQL Server、Databases オブジェクト
このカウンターはログがメモリからディスクにフラッシュされた際のバイト数を表すため、ログファイルの書き込み状況として利用することができます。
OLTP 系のシステムではログファイルへの書き込みは頻繁に行われていますので、ログファイルのディスクにどれくらいの負荷がかかっているかの指標としてみることができるかと思います。
■DMV からの取得
ここまでの内容はパフォーマンスもインターから取得していましたが、各ファイルへの I/O は動的管理ビューからも取得することができます。
sys.dm_io_virtual_file_stats (Transact-SQL)
# 私は、sys.fn_virtualfilestats で覚えてしまっているので、こちらをよく使っていますが。
この DMV では SQL Server のサービスが起動してからのデータベース内の各ファイルへのファイル I/O を取得することができます。
ファイルの I/O 状況としては書き込み / 読み取りの情報だけでなく待ちの情報 (io_stall) も取得することができます。
特定の期間内で取得したデータの差をとることで、どのファイルにどれだけの I/O がかかっていたのかを分析することができるようになります。
ほかにもいろいろと方法はあるかと思いますが、私がふだん主に使っているのはこれらの情報になります。
ディスク I/O の性能は近年向上していますので、この辺を意識しない構成というのが増えていくかもしれませんが知っていると便利かもしれないですね。