SQL Server 报告磁盘响应时间长，磁盘大 队列长度
适用场景
- Microsoft SQL Server
- ONTAP 9
问题描述
- 在 Windows 资源监控器中的 " 磁盘活动 " 下，我们可以看到磁盘报告 " 响应时间 " 较长，也可以在 " 存储 " 部分下看到较长的 " 磁盘队列长度 " 。
- SQL 代理作业（批处理作业）所用时间比预期要长，然后出现故障
- 整体来看，可能会出现 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