SQL Server から長いディスク応答と大容量ディスクが報告されるキューの長さ

最後の更新
PDFとして保存

Views:: 1,179

Visibility:: Public

Votes:: 1

Category:: fas-systems

Specialty:: perf<a>パフォーマンス</a><a>SQL</a><a>エージェントジョブ</a><a>2008612490</a><a>ディスクキュー長</a>

Last Updated:

環境

Microsoft SQL Server
ONTAP 9
Eシリーズ

問題

Windowsリソースモニタの [Disk Activity]で、[Response Time]が高いディスクを確認できます。また、[Storage]セクションで[Disk Queue Length]が高いディスクを確認できます。
SQL Agentジョブ（バッチジョブ）が想定よりも時間がかかり、失敗している
全体的にSQLのパフォーマンスの問題が発生する可能性があり、Microsoftからストレージのチェックを推奨される場合がある
SQL Serverエラーログに、IOの問題を示すflushCacheメッセージが大量に表示されています。

2021-02-09 10:21:48.590 spid21s FlushCache: cleaned up 19485 bufs with 15249 writes in 488889 ms (avoided 987 new dirty bufs) for db 6:0 2021-02-09 10:21:48.590 spid21s average writes per second: 31.19 writes/sec average throughput: 0.31 MB/sec, I/O saturation: 19959, context switches 104381 2021-02-09 10:21:48.590 spid21s last target outstanding: 13100, avgWriteLatency 2 2021-02-09 10:29:58.510 spid21s FlushCache: cleaned up 2754 bufs with 2266 writes in 171682 ms (avoided 6298 new dirty bufs) for db 6:0 2021-02-09 10:29:58.510 spid21s average writes per second: 13.20 writes/sec average throughput: 0.12 MB/sec, I/O saturation: 6978, context switches 18314 2021-02-09 10:29:58.520 spid21s last target outstanding: 2, avgWriteLatency 113 2021-02-09 10:33:28.300 spid21s FlushCache: cleaned up 4360 bufs with 1554 writes in 148717 ms (avoided 1869 new dirty bufs) for db 6:0 2021-02-09 10:33:28.300 spid21s average writes per second: 10.45 writes/sec average throughput: 0.23 MB/sec, I/O saturation: 5536, context switches 15283 2021-02-09 10:33:28.300 spid21s last target outstanding: 2, avgWriteLatency 48 2021-02-09 10:37:56.710 spid21s FlushCache: cleaned up 3223 bufs with 1001 writes in 97386 ms (avoided 273 new dirty bufs) for db 6:0 2021-02-09 10:37:56.710 spid21s average writes per second: 10.28 writes/sec average throughput: 0.26 MB/sec, I/O saturation: 3598, context switches 9648 2021-02-09 10:37:56.710 spid21s last target outstanding: 8, avgWriteLatency 199 2021-02-09 10:39:24.690 spid21s FlushCache: cleaned up 1689 bufs with 475 writes in 77464 ms (avoided 308 new dirty bufs) for db 13:0 2021-02-09 10:39:24.690 spid21s average writes per second: 6.13 writes/sec average throughput: 0.17 MB/sec, I/O saturation: 2593, context switches 7651 2021-02-09 10:39:24.690 spid21s last target outstanding: 2, avgWriteLatency 247