跳转到主内容

SQL Server 报告磁盘响应时间长，磁盘大 队列长度

  1. 最后更新
  2. 另存为PDF
Views:
211
Visibility:
Public
Votes:
1
Category:
fas-systems
Specialty:
perf<a>性能</a><a>SQL</a><a>SQL 代理作业</a><a>2008612490</a><a>磁盘队列长度</a>
Last Updated:

适用场景

  • Microsoft SQL Server
  • ONTAP 9
  • E 系列

问题描述

  • 在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

 

Sign in to view the entire content of this KB article.

New to NetApp?

Learn more about our award-winning Support

NetApp provides no representations or warranties regarding the accuracy or reliability or serviceability of any information or recommendations provided in this publication or with respect to any results that may be obtained by the use of the information or observance of any recommendations provided herein. The information in this document is distributed AS IS and the use of this information or the implementation of any recommendations or techniques herein is a customer's responsibility and depends on the customer's ability to evaluate and integrate them into the customer's operational environment. This document and the information contained herein may be used solely in connection with the NetApp products discussed in this document.