SQL Server / Azure SQL Database (SQL DB) で、データベースのデータファイル (mdf/ndf) に対しての書き込みの発生状況を確認するための方法として「SQL Server: Buffer Manager オブジェクト」の「Page writes/sec」の情報を調べるという手法があります。
Page writes/sec の説明は次のようになっています。
物理的なデータベース ページ書き込みが実行される 1 秒あたりの回数を示します。
SQL Server では、物理的なデータベース ページへの書き込みが発生する一般的な要因としては、次のような内容があります。
- チェックポイントの発生による、ダーティーページのデータファイルへの書き込み
- Checkpoint pages/sec / Background writer pages/sec の情報と組み合わせての確認
- レイジー ライターによる、ダーティーページのデータファイルへの書き込み
- Lazy writes/sec の情報と組み合わせて確認
- クエリストアの情報をデータファイルに永続化するための書き込み
クエリストアは少し例外的なパターンですが、基本的には、メモリ上では変更されているが、データファイルには書き込みが行われていないデータを書き込み、ディスクに永続化する際に Page writes/sec が発生します。
ただし、特定の状況下では、非効率な SELECT によっても Page writes/sec が発生するケースがあります。
SELECT で Page writes が発生するケースとしては「SELECT で tempdb のデータファイルに対して書き込み」が発生した場合です。
これは、Sort Warning / Hash Warning のような、SELECT 内で、ソートやハッシュ結合が実行されている場合に、メモリだけでは処理を完了することができず、tempdb のデータファイルを一時領域として使用しているケースです。
実行プランを確認すると、次の画像のように、実行プラン内の処理に「警告マーク」が記載され、詳細を確認すると警告が出力されているケースが、tempdb のデータファイルが使用されている状態となります。
TPC-H の LINEITEM を使用した次のようなクエリを実行してみます。
SELECT * FROM LINEITEM ORDER BY L_COMMENT ASC, L_SHIPMODE ASC
このクエリは、検索のクエリです。
通常、このクエリが実行された場合、データファイルの書き込みの状態を表す「Page writes/sec」は発生しないように思いますが、tempdb を使用したソートが行われた場合は、次の画像のように「Page writes/sec」が発生します。
tpch のデータベースと tempdb のデータファイルの書き込み状態を、次のクエリを連続で実行して情報を取得してみます。
SELECT GETDATE() AS collect_date, * FROM ( SELECT DB_NAME(fs.database_id) AS database_name, SUM(fs.num_of_bytes_written) AS num_of_bytes_written FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND mf.file_id = fs.file_id WHERE fs.database_id IN(DB_ID(), DB_ID('tempdb')) AND mf.type = 0 GROUP BY fs.database_id ) AS T PIVOT( MAX(num_of_bytes_written) FOR database_name IN([tempdb], [tpch]) ) AS PVT
実際に取得した情報が次の画像となります。
パフォーマンスモニターの「Page writes/sec」が上昇したタイミング (23:30:20 前後) で tempdb のデータファイルの書き込みが発生していることが確認できますね。
Page writes/sec の情報を確認する際には、次のような項目と組み合わせて確認を行います。
- Checkpoint pages/sec / Background writer pages/sec / Lazy writes/sec が発生しているタイミングと組み合わせて、データファイルへのデータ永続化時のディスク負荷の確認
- Log Bytes Flushed/sec と組み合わせて、トランザクションログへの書き込み状況と、データファイルへの書き込み状況の状態の確認
一般的な確認としては「Log Bytes Flushed/sec」等の、トランザクションログの書き込みを表す情報と Page writes/sec の情報を組み合わせるケースが多いです。
具体的な確認の方法としては、「前日の Log Bytes Flushed/sec のトランザクションログの書き込み状態」と比較して Log Bytes Flushed/sec が増加しているのであれば、トランザクション数が増えたため、Page writes/sec も増加していると考えることができます。
「Log Bytes Flused/sec が増加しておらず、Page writes/sec が増加」している場合には、今回確認したように、「非効率な実行プランが使用されていることによる tempdb のデータファイルへの書き込みの増加に伴う Page writes/sec の増加」というケースが発生している可能性が考えられます。
- Checkpoint pages/sec
- Background writer pages/sec
- Log Bytes Flused/sec
この 3 種類の項目が増加しておらず、Page writes/sec が増加している場合には、何らかの要因により、tempdb のデータファイルが使用されている可能性についても検討すると、問題解決の一助となるのではないでしょうか。