SE の雑記

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 のデータファイルが使用されている可能性についても検討すると、問題解決の一助となるのではないでしょうか。