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?