意外に重要・・・ディスクパフォーマンス

とある保守環境でレガシーなサーバーを利用しており、 CPU, メモリに問題ありません、ハードウェアに問題ありませんと「協力会社」の方が報告してきたなかで誰も気にしていなかった(何か遅いと言えばCPU, メモリばかり考えますよね？)ディスクパフォーマンスを調査した事例。OSレベルのパフォーマンスカウンターでもわかるが最終のSQL Server再起動時から（再起動でリセットはかかる）SQLで取れるログがいくつかあり下記がSQLの例。影響しているデータファイル（file_idで）とファイル読み書きの良し悪しがわかる。

読み書き応答速度チェック

SELECT DB_NAME(database_id) AS [ﾃﾞｰﾀﾍﾞｰｽ名] , file_id , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [平均読書ストールミリ秒] , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [平均読取ストールミリ秒] , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [平均書込ストールミリ秒] ,　io_stall_read_ms , num_of_reads , io_stall_write_ms , num_of_writes , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] FROM sys.dm_io_virtual_file_stats(NULL, NULL) ORDER BY [平均読書ストールミリ秒] DESC ;

file_id特定後ファイル名を特定

SELECT FILE_NAME(xxxx) AS 'File Name xxxx',FILE_NAME(yyyy) AS 'File Name yyyy';

肌感覚で大した仕組みでないデータベースで30〜50ミリ秒を上回っていたらかなり注意。最近はSSDやクラウドで速いことが多いがオンプレで特に速度が遅いSATAやSASでもRAID 10になっていないようなRAIDの組み方ではパフォーマンスが出ずシステムレスポンスが遅いという話になる。下図は実例だが、書き込みで3,000 ms 近い遅延や読み込みで200ms近い状態もある。書き込みが遅い場合、データ検証チェックをDBレイヤーで行わずアプリロジックに入れてしまう方が良い（というかそうしておいた方が拡張性、保守性が高い）。インデックスが多いと遅延につながる。同様に読み込みが遅い場合はインデックスが適切でないケースがある。ロジック見直しでなんとかなるのであれば良いが、サーバーに格納されているデータファイル全体的に天文学的に遅い数値（特に下図のようなパターン）が出ている場合はもはやインデックス云々というよりハード的な問題とほぼ断定できる。