Home / server / Questions / 704309
Accepted
MichaelD
Asked: 2015-07-09 04:14:56 +0800 CST

Performance azure disks, high average queue length

  • 772

I'm having a classic developer-system admin battle where I am stating that there is an IO problem on the disk of the shared sql server while the system admin tells me that the capacity hasn't been reached at all.

1 thing I was able to point out is that the avg disk queue length on the SQL server is very high. The avg queue length has an average of 5 and regularly spikes to 10 to 15. It never reaches below 2.5

The system admin tells me that's not a problem because the disk is a stripe of 6 disks and the queue length is by definition bad if it is higher then the double amount of the spindles. So his formula is 6*2=12 which is lower then the average 5.

Is his reasoning correct ? Can we just look at an azure disk as a spindle? The constant avg queue length of min 5 is not an indication?

EDIT: As it turns out, the disks were a huge bottleneck. The application runs smoothly again after moving the databases to a seperate database server.

performance

2 Answers

  1. Fairly new to Azure, but here's my offering:

    The more Azure disks you span the better your performance will be, this is true. I'm not sure about the equation though. Either way, I think you're barking up the wrong tree...

    Azure imposes artificial limits on metrics such as IOPS and Max reads/writes per second, so the queue length is only one of a few metrics that you need to look at.

    This question is similar in nature to yours. It has a helpful reference to an Azure SQL performance article, too. Ensuring you follow Microsoft's best-practise could help you to improve performance.

    I'm not sure further increasing the number of disks you use will help. From what I've seen, the growth is not linear. You might expect the IOPS for 6 disks to be 3000 (6x500) but it's probably more like 2000.

    Moving to the DS (SSD) tier might be the only way to get the kind of performance you are looking for from a single VM. Here is a run-down of the the Azure instance limits: https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-size-specs/

    Lastly, the best way to improve perfomance of any application is to reduce the reliance on I/O. Cache wherever possible. You could try an instance with more memory.

    • 4
  2. Best Answer

    You're focusing a bit much on a single metric!

    Diagnosing a bottleneck to a single component rarely ends with one counter giving a full explanation.
    There are quite a few great guides for using perfmon to diagnose performance problems on SQL Server.

    And unfortunately your Admin could be right, the counter you choose does indeed depend on the underlying hardware. However I can't find any documentation stating that Azure is based on a 6 disk raid. So perhaps focus on other counters?

    1. Avg. Disk sec/Read
    2. Avg. Disk sec/Write
    3. Disk Read/sec
    4. Disk Writes/sec

    But as a final recommendation. If your only indication was the Avg. Disk Queue Length, your Sysadmin is probably right.
    If you're sure it's a disk issue, you can always try creating storage spaces.

    • 2