Glenn Berry

Azure Virtual Machine Developments for SQL Server Usage

SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download

Featured Author

Jonathan Kehayias is a Principal Consultant with SQLskills and the youngest MCM ever.

Jonathan’s Posts

Microsoft has a large number of different Azure virtual machine series and sizes available if you are interested in using their infrastructure as a service (IaaS) option for hosting SQL Server instances. One initial decision you will need to make is what series and size of Azure VM you want to use for your particular SQL Server workload.

For on-premises SQL Server usage, you want to do some careful analysis of your workload and the available server models and processor models in order to get a server with an appropriate processor choice to minimize your SQL Server license costs and get the best performance and capacity possible at a given SQL Server license cost. The cost and performance difference between a good processor choice and a bad processor choice can be quite dramatic.

With an Azure VM, you need to do a similar analysis, but for somewhat different reasons. The licensing cost of SQL Server on an Azure VM is directly related to the number of cores in the VM. You want to choose a VM size that matches your expected needs for CPU performance and capacity, memory capacity, and storage performance.

Unlike an on-premise scenario, you are going to have much less control over the actual processor choice in the underlying host machine. With an Azure VM, you pick a particular machine series (such as a GS series) and size in a particular Azure Data Center, and then you get whatever processor Microsoft is using for that series and size in that Azure Data Center. Microsoft currently has 42 different regions that have Azure Data Centers, so you can use the matrix shown on this page to discover what VM series are offered in each region.

Azure Compute Unit (ACU)

One very important concept to understand when comparing and selecting an Azure VM SKU is the Azure Compute Unit (ACU), which lets you compare the compute performance (per core vCPU performance) across different Azure VM SKUs. This ACU measurement is currently standardized on a Small (Standard_A1) VM with a score of 100 and all other Azure SKUs then have ACU scores that represent approximately how much faster that particular Azure SKU can run a standard CPU benchmark. So, for example, a Standard_A1 has an ACU/vCPU score of 100 while a Standard_GS5 has an ACU/vCPU score of 240.

Looking at the ACU score for an Azure VM SKU gives you a decent idea of the single-threaded CPU performance of the processor used in the underlying host machine. It is also important to understand whether that particular processor supports Intel Turbo Boost and/or Intel Hyper-Threading, since not all processors used in the various Azure VM SKUs support these technologies.

You can do this by identifying the actual host CPU in your VM in the CPU page of Performance tab of Windows Task Manager or by using a utility like CPU-Z. Once you have identified the exact processor model in the host, you can use the online Intel ARK Database to get the specific details about that processor.

New Azure VM Sizes for SQL Server

A common issue with Azure VM sizing for SQL Server has been the fact that you were often forced to select a VM size that had far more virtual CPU cores than you needed or wanted in order to have enough memory and storage performance to support your workload, which increased your monthly licensing cost.

Luckily, Microsoft has recently made the decision process a little easier for SQL Server with a new series of Azure VMs that use some particular VM sizes (DS, ES, GS, and MS), but reduce the vCPU count to one quarter or one half of the original VM size, while maintaining the same memory, storage and I/O bandwidth. These these new VM sizes have a suffix that specifies the number of active vCPUs to make them easier to identify.

For example, a Standard_DS14v2 Azure VM would have 16 vCPUs, 112GB of RAM, and support up to 51,200 IOPS or 768MB/sec of sequential throughput (according to Microsoft). A new Standard_DS14-8v2 Azure VM would only have 8 vCPUs, with the same memory capacity and disk performance as the Standard_DS14v2, which would reduce your SQL Server licensing cost per year by 50%. Both of these Azure VM SKUs would have the same ACU score of 160.

One somewhat confusing issue with these new Azure VM SKUs is that the monthly Compute cost (which includes OS licensing) for both of these SKUs would be the same ($989.52/month for this example). Where you save is the reduced SQL Server monthly licensing costs.

Essentially what Microsoft is doing here is disabling cores in the VM to reduce your SQL Server licensing costs, which is something you are not allowed to do with on-premises SQL Server. With on-premises SQL Server, you are not allowed to disable processor cores in your UEFI/BIOS to reduce your SQL Server license costs. Even if you disable cores in the UEFI/BIOS, you are still required to license those cores for SQL Server.

Benchmarking Your Azure VM with CPU-Z

One very quick CPU benchmark you can run any machine (physical or virtual, Azure VM or not) is the built-in CPU-Z benchmark, which only takes about 20 seconds to complete. I created a Standard DS14-8_v2 in the South Central US Data Center. This VM was using the Intel Xeon E5-2673 v3 Haswell-EP processor, which is a 12-core processor with a base clock speed of 2.4GHz, and a Turbo clock speed of 3.2 GHz that appears to be a special bespoke processor SKU that is not in the Intel Ark Database. This processor family was introduced in Q3 of 2014, so it has been available for over three years. On this particular VM, I did not see Turbo Boost being used during my testing.

Looking at Figures 1 through 3, you can see the CPU-Z benchmark results for this Azure VM, with Figure 4 being the results for my admittedly fast Dell Precision 5520 laptop. My laptop has the same number of virtual cores as the Standard DS14-8_v2 Azure VM, but I am using a much newer Intel Xeon E3-1505M v6 Kaby Lake processor with a higher base clock speed and better single-threaded performance.

Figure 1: Task Manager from Standard DS14-8_v2 in South Central US

Figure 2: CPU-Z CPU Tab from Standard DS14-8_v2 in South Central US

Figure 3: CPU-Z Bench Tab from Standard DS14-8_v2 in South Central US

Figure 4: CPU-Z Bench Tab from Glenn’s Laptop