3

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.

MichaelD
  • 515
  • 5
  • 7
  • Azure disk performance is nottoriously bad, as other answers have pointed out; I've also discussed it here: http://serverfault.com/questions/592560/azure-io-performance/708205#708205. – Massimo Aug 07 '15 at 15:21

2 Answers2

4

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.

Community
  • 1
john
  • 1,995
  • 1
  • 17
  • 30
  • +1, keep in mind though the differences in the types of SSD's, azure offers a temporary ssd drive that is whiped upon server restart. And should only be used for tempdb. – Reaces Jul 08 '15 at 14:47
  • @Reaces - Thanks, I meant to specify the DS tier in my answer. Updated. – john Jul 08 '15 at 15:00
2

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.

Reaces
  • 5,547
  • 4
  • 36
  • 46
  • The admin uses 6 disks and stripes them so he's not referring to the underlying azure hardware. Avg Disk sec/Read E is never higher than 0.1 . I don't have access to the other perf metrics but inserting data in the database goes slow – MichaelD Jul 08 '15 at 15:10
  • @MichaelD What do you mean by _goes slow_? If you can't measure it, you can't fix it. _Goes slow_ could mean anything, and you can't adequately prove something is fixed, if you didn't put a number on the problem. – Reaces Jul 08 '15 at 20:23