10

We currently have a VM that is very underpowered and are proposing to move to an Azure VM with better specs. The problem is, the Azure VM is much slower than the original VM even though it is of a higher spec.

The original server is a 2 core VM with 2GB of memory that is also a web server. It's running Microsoft SQL Server Web Edition 2008 R2 and because this server is used for other things, we have had to limit the maximum server memory in SQL Server to 512MB.

The new server is a 4 core VM with 7GB of memory that is only a database server. It's running Microsoft SQL Server Standard Edition 2008 R2 and we haven't limited the amount of memory that SQL Server can use.

This is one of two servers set up in a mirrored environment but the database that I am running tests on is not mirrored. The other databases on this server aren't getting much traffic at the moment (in fact, Activity Monitor shows no activity on the other DBs whilst I was running these tests).

I do realise that an issue with Azure VMs is that the hard drives are a network resource so that would be the source of slow down but it is still slower even when there are 0 physical reads shown in the IO statistics.

I have followed the tuning advice on this page on the Azure VM including striping the disks (two disks per drive) and putting the log and data files on separate drives.

The only things that I haven't done are enable page compression, limit auto-grow on the database and move the SQL server error log and trace file directories to data disks. I haven't done that on the older server either.

The old server has none of this tuning done and the log and data files are on the same drive which is not striped.

The database on the current server is 65 GB (45 data and 20 log) which was a bit too large to transfer to the new server so I'm testing on a smaller DB (6 data and 13.5 log)

The results on the old server are CPU time = 1311 ms, elapsed time = 1057 ms. and on the new server are CPU time = 1281 ms, elapsed time = 2525 ms. That's just one run but the results are representative of what I am normally seeing.

The new server seems to constantly have an elapsed time significantly longer than the CPU time. Is that an issue and is there something that I can do to track down what is causing it?

What other steps can I take to find out why this server is going so slowly when it looks like it should be faster than the old server?

|improve this question
  • 1
    Maxdop 1? Did you compare the execution plans, take before and after wait statistics, and check for blocking? – Aaron Bertrand♦ Jun 23 '15 at 11:02
  • 1
    How many data drives? With standard page blob storage, each drive is limited to the lesser of 300 IOPS in the basic tier or 500 in standard, which is much less than an on-prem spinning disk. It is imperative to have as many drives (vhd) as possible to maximize IOPS and bandwidth. You can use Windows Storage Spaces to avoid the need to create a separate data file on each drive. – Dan Guzman Jun 23 '15 at 11:11
  • The new server has 4 VHDs striped into two drives so I have 1000 IOPS for logs and 1000 IOPS for data. – Steve Kaye Jun 23 '15 at 13:22
  • A standard page blog storage VHD only provides about 30MB/sec bandwidth due to throttling so that's only 60MB/sec aggregate. Consider creating 14 data VHDs for up to 420MB/sec and striping among all. – Dan Guzman Jun 23 '15 at 22:48
  • I can only have 8 disks as it's an A3 instance so I'd have to upgrade to A4 to do that which is twice the price. I don't think that this is a drive issue though as the tests above all had 0 physical reads listed on the IO stats. – Steve Kaye Jun 24 '15 at 7:31
2

For what is worth, I ended changing the VM in Azure from type A to type D and then attaching another disk and moving the TEMPDB to that disk. So my final VM now is a D2 Standard with 7 GB RAM and three data disks, one for the MDF files, another one for LDF files and the new TEMPDB disk.

I gave up trying to understand with the A3 some things you mentioned and simply upgrade the virtual machine. I even went from A2 to A3 and even though I did find some improvements, I ended up changing to a D2 VM.

In the document that you stated, Microsoft recommends a D3 for Enterprise Edition or D2 for Web or Standard Edition, and use of Premium Storage, among other things in the check list at the beginning of the document.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.