none
SQL SERVER-DISK QUEUE LENGTH

    Question

  • Hi,

    We are facing issue with SQL server running on VMware as disk queue length on SQL server is continuously high.

    Due to this performance of backup is very slow.

    Please let me know the troubleshooting steps in order fix this issue or any recommendation in order to avoid such issue in future.


    Hakim.B Sr.System Administrator

    Tuesday, July 10, 2018 3:23 PM

All replies

  • Something to check for regarding this issue is the presence of snapshots. The more snapshots you have, the more disk activity you will find as a result. HTH

    Tuesday, July 10, 2018 3:52 PM
  • you should probably report the MB/s, IO/s and ms/Rd (on the data file volume) and ms/Wr (on the backup location) values. It do to know what the underlying hardware is. Also, check to see if throttling was implemented either on the VM host or the VM

    jchang

    couple of more items for achieving absolutely horrible IO performance. Single volume - data and backup location. If storage is on SSD, particularly a consumer grade device (very low spare banks) and the SSD is near full, horrendous IO is expected? 
    • Edited by jchang61 Tuesday, July 10, 2018 9:26 PM
    Tuesday, July 10, 2018 4:21 PM
  • I have checked and there is no snapshot.

    Hakim.B Sr.System Administrator

    Tuesday, July 10, 2018 7:15 PM
  • No there is no throttling has been configured.

    Hakim.B Sr.System Administrator

    Tuesday, July 10, 2018 7:16 PM
  • We are facing issue with SQL server running on VMware as disk queue length on SQL server is continuously high.

    Due to this performance of backup is very slow.

    Please let me know the troubleshooting steps in order fix this issue or any recommendation in order to avoid such issue in future.

    Are disks local to the VM host, or are they on a SAN?

    In any case, it is your I/O subsystem that is underperforming, so you need to hot it up a bit.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 10, 2018 9:21 PM
  • I suggest you peruse the Architecting Microsoft SQL Server on VMware vSphere best practices guide: https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, July 11, 2018 2:27 AM
    Moderator
  • To use the Disk Queue Length counter, you must know on how many spindles your disk is using.  In case of a SAN, it's really hard to know this.

    What i would suggest you is to use the counters
    \logicaldisk(*)\Avg. Disk sec/Read
    \logicaldisk(*)\Avg. Disk sec/Write

    Those counters will give you the time it takes to complete a read or a write on disk.

    The thresholds are
    Less than 15ms --> Ok
    More than 15ms --> you may have performance issues
    More than 25ms --> You have performance issues

    hth


    This posting is provided AS IS without warranty of any kind

    Wednesday, July 11, 2018 4:05 AM
  • Hi Hakim Bhungra,

     

    According to your description, my understanding is that the disk queue length on your SQL server is continuously high and the condition affect the backup. If anything is misunderstand, please tell me.

     

    Firstly, you need to know about the performance of your disk . I suggested you using the Physical disk counters in Performance Monitor. You could consider comprehensive values of counters to know the condition of the disk. You can pay more attention on the %Disk Time, %IdleTime, Disk Bytes/sec, Avg. disk sec/write.

     

    %Disk Time is the percentage that disk is in reading and writing state. In common the value is less than 100%.

    %IdleTime is the percentage that disk in the idle state. If the disk is in the idle state, the value is 100%. When the disk is operating at full load, the value is 0%.

    Disk Bytes/sec means the number of disk reads and writes per second. In common, the value is between 10MB and 40MB.

    Avg. disk sec/write means average time spent on a disk for each write operation. The value is more than 20ms means the speed of the disk is slow.

     

    Besides, the Recovery Internal of SQL Server, the autogrowth and autoshrink may be related to I/O. Reduce the fragments in data files, put all data files that are in the same filegroup to different disks, put the data file and log file into different disks are also efficient approaches to reduce the I/O. Finally, you need to find out the reason of high disk queue, then we could optimizate the operation related to the issue.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 13, 2018 2:17 AM