Unless you have been making a concerted effort to ignore it, you may have heard that Microsoft would really like for you to move much of your SQL Server database infrastructure into a Microsoft data center, whether you go to an Azure SQL Database (which I recently discussed here), or whether you host it on a Windows Azure Virtual Machine. Microsoft calls these persistent virtual machines compute instances, and they have two main tiers to choose from, which include the Basic Compute Tier and the Standard Compute Tier. They describe these two tiers as:
Basic Compute Tier: This new tier of compute instances is similar in configuration to the Standard tier with lower prices. These instances do not include load balancer and auto-scaling. They are well-suited for single instance production applications, development workloads, test servers and batch processing applications that might not require these features. The basic compute tier is currently available only for the General Purpose Instances. These instances range from Basic A0 to Basic A4.
Standard Compute Tier: This tier of compute instances provides an optimal set of compute, memory and I/O resources for running a wide array of applications. These instances include both auto-scaling and load balancing capabilities at no additional cost. The standard compute tier is available across General Purpose, Memory Intensive and Compute Intensive instances. These instances range from Standard A0 to Standard A7.
There are several important advantages to hosting your SQL Server infrastructure on a Windows Azure Virtual Machine. First, you have no capital costs for storage or hardware, along with no ongoing maintenance of the storage or hardware. Second, you have no OS or SQL Server license costs (when you use a SQL image from the standard Azure VM gallery). Third, you can create a new Azure VM that already has SQL Server installed in a few minutes (even though it may take a little longer to completely configure the OS and the SQL Server instance to your exact requirements). Going forward, it will still be up to you to install Windows and SQL Server updates, but you won’t have to worry about things like firmware, BIOS, or driver updates.
If you want to use Windows Azure Virtual Machines to host all or part of your SQL Server infrastructure, you should be aware of the current pricing details that are available for the specific Azure data center that you want to host your virtual machines, since pricing can vary across different Microsoft data centers. Microsoft currently has 13 different Windows Azure virtual machine sizes, as detailed in their Virtual Machine and Cloud Service Sizes for Azure page. Microsoft reduced the hourly pricing for the memory intensive instances (Standard A5, Standard A6, and Standard A7) by 18% in most of their data centers on May 1, 2014, and the pricing shown in Table 1 reflects those new, lower prices.
The Single-Core Score and the Multi-Core Score in the two right-hand columns of Table 1 are the average scores that I observed using the 32-bit version of the Geekbench 3.05 processor and memory benchmark on a sample Windows Server 2012 R2 Datacenter VM in the East U.S. Data Center. These scores may or may not be representative of what you will see.
| VM Size | CPU Cores | Memory (GB) | Hourly Cost | Monthly Cost | Single-Core Score | Multi-Core Score |
|---|---|---|---|---|---|---|
| Basic A0 | 1 (shared) | 0.75 | $0.018 | ~$14 | 507 | 498 |
| Basic A1 | 1 | 1.75 | $0.074 | ~$56 | 679 | 670 |
| Basic A2 | 2 | 3.50 | $0.148 | ~$111 | 709 | 1,358 |
| Basic A3 | 4 | 7.00 | $0.296 | ~$221 | 717 | 2,472 |
| Basic A4 | 8 | 14.00 | $0.592 | ~$441 | 724 | 4,042 |
| Standard A0 | 1 (shared) | 0.75 | $0.020 | ~$15 | 492 | 502 |
| Standard A1 | 1 | 1.75 | $0.090 | ~$67 | 1,068 | 1,083 |
| Standard A2 | 2 | 3.50 | $0.180 | ~$134 | 1,069 | 2,002 |
| Standard A3 | 4 | 7.00 | $0.360 | ~$268 | 1,070 | 3,593 |
| Standard A4 | 8 | 14.00 | $0.720 | ~$536 | 1,094 | 6,446 |
| Standard A5 | 2 | 14.00 | $0.330 | ~$246 | 1,080 | 2,026 |
| Standard A6 | 4 | 28.00 | $0.660 | ~$492 | 1,080 | 3,686 |
| Standard A7 | 8 | 56.00 | $1.320 | ~$983 | 1,056 | 6,185 |
Table 1: Selected Virtual Machine Specifications for Windows Azure in the East U.S. Data Center
Currently, Microsoft has nine data centers that can host new persistent virtual machines, which include East U.S., West U.S., Brazil South (Preview), North Europe, West Europe, East Asia, Southeast Asia, Japan West, and Japan East. According to Microsoft, "A1 is the smallest size recommended for production workloads," and you should select "a virtual machine with 4 or 8 CPU cores when using SQL Server Enterprise Edition." One useful, if somewhat dated reference for running SQL Server on a Windows Azure Virtual Machine is the Performance Guidance for SQL Server in Windows Azure Virtual Machines that was published in June, 2013.
Windows Azure Virtual Machine Characteristics
When you look at the CPU properties on the Performance tab in Windows Server 2012 R2 Task Manager (in Figure 1 and Figure 2), you will notice that it reports that it is using a relatively old, 45nm AMD Opteron 4171 HE processor, running at a speed of 2.1GHz. This particular six-core processor was introduced in June of 2010, as part of the two-socket Lisbon family. The HE suffix means that it is a "low-powered" energy efficient model which is not a good choice for SQL Server usage, since it gives up a significant amount of performance for a relatively small amount of reduced energy usage. After doing some digging, I have been told that this processor is a special OEM processor for Microsoft data centers.
Figure 1: Basic A0 Virtual Machine in East U.S. Data Center
Figure 2: Standard A7 Virtual Machine in East U.S. Data Center
The other big issue with this processor besides its age and relatively poor single-threaded performance is the fact that it only has six physical cores. This is a problem with the Basic A4, Standard A4, and Standard A7 VM sizes, which have two NUMA nodes and eight total physical cores. This would mean that a VM of that size would cross a NUMA node on the underlying physical host, which is not a good idea for memory performance. I have a hard time believing that Microsoft would do this on purpose. I also have a hard time believing that every single Azure VM in every single data center that I have tried so far is using the exact same elderly AMD processor. It is fairly common knowledge that Microsoft has at least three different generations of hardware (Gen 1, Gen 2, and Gen 3) that they have used so far in their Azure data centers, offering different VM performance. After some more inquiries, I have discovered that this AMD Opteron 4171 HE processor is an Azure Gen 2 processor.
You can browse the Geekbench 3 online database of uploaded benchmark results, looking for systems using the AMD Opteron 4171 HE processor here. You may notice that every single result for this processor seems to be for a Microsoft Virtual Machine, which is also quite curious. Windows Server 2012 R2 Task Manager is reporting the L1 cache as “N/A” and not even listing the L2 and L3 cache sizes on these Azure VMs. Another curious piece of evidence is the fact that the Standard Instances have about 50% higher Geekbench 3 scores than the equivalent Basic Instances when they have the exact same total processor core counts and memory sizes, for both the Single-Core score and Multi-Core score. This much of a variance does not make any sense if the underlying host machine is actually using the same processor.
Summary
All of this evidence initially led me to the conclusion that Microsoft was probably obscuring the actual processor in the host machine. I thought they might be doing this to try to prevent people from purposely provisioning multiple VMs until they happen to get a VM is running on newer, faster, host hardware. It turns out that Microsoft is not quite that clever. I have been assured that Microsoft does not alter the identity of the CPU in an Azure VM. There are newer Azure Gen 3 processors that you may get in an Azure VM, as you provision new VMs in the future. Another possible reason for my results was that they are likely using some sort of governance to limit VM performance to a reliable, uniform level, regardless of the underlying host hardware, so that they can host more VMs on less hardware over time. This would be a smart course of action for an IaaS hoster.
The relatively low Geekbench 3.05 scores (see Figure 3) for even the largest Azure VMs means that you are giving up a significant amount of processor and memory performance compared to an equivalent physical two-socket server with the same number of processor cores and memory.
Figure 3: 32-bit Geekbench 3.05 Results for Standard A4 Virtual Machine in East U.S. Data Center
Many SQL Server workloads will run perfectly well with this level of VM performance, albeit a little slower than you may be used to. If you factor in the SQL Server 2014 Enterprise Edition license savings from an eight-core machine, plus the capex for a modest, two-socket server and its associated storage, you could afford to run a Standard A7 virtual machine 24×7 for about five to six years. Given that sort of ROI, I can see many organizations making the economic decision to move at least a portion of their SQL Server infrastructure to Azure Virtual Machines. As long as your workload can run on a 56GB or smaller VM, and as long as having less CPU and memory performance than a typical recent vintage laptop is also acceptable, this is a rational course of action. Microsoft recently announced the availability of larger, much faster A8 and A9 VM Compute Intensive Instances, that use Intel Xeon E5-2670 processors. This will be a huge improvement in performance over the Azure Gen 2 processors.
I’ll be taking a look at I/O performance in Azure Virtual Machines in an upcoming article.
My 2 year old desktop has 2.8x the single-core throughput of the Standard A1+ instances. This means that the CPU-bound portion of queries takes 2.8x longer on Azure. I find this to be a staggering loss of performance. (The new CPU's on http://browser.primatelabs.com/processor-benchmarks have a 3.7x better ratio).
I'd be very curious to see the price/performance comparison of these options. Are there some instances that are generally better deals? (This assumes the workload will scale up/down to the instance size)
Thank you, excellent work.
As an Azure customer, I am not surprised to learn the performance downgrade, as an ex cloud infrastructure supplier I known how the game works, most customers decision makers don't understand performance, lifecycle, cost of change enough, they simply see VM with 4 cores for $x per month, never even questioning that 4 cores running very slowly is worst than 1 core running normally.
Most decision makers don't understand that a system running at 5% cpu today will be running at 75% in 5 years time, provided the system deployed on it is successful, so to get to my point you are forced to change your Azure hosted system 50% earlier, and that has a direct manpower cost and risk.
Have you looked a the disk subsystem performance on Azure? it may be me but for SQL server I could not get better than 50Mbytes/sec off a two disk stripe on a Memory intensive A5 vm in a reliable manor – SQLIO kept crashing, so I didn't trust the 2014 storage pool. After all the CPU running out of cycles may or may not cause a system to go slower, but everyone's data grows over 5 years.