Glenn Berry

Running SQL Server 2014 on an Azure Virtual Machine

SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Paul Randal, CEO of SQLskills, writes about knee-jerk performance tuning, DBCC, and SQL Server internals.

Paul’s Posts

Microsoft is making it increasingly easy to run SQL Server 2014 on an Azure virtual machine in one of Microsoft's seventeen Azure data centers. You can run a preconfigured virtual machine with a preconfigured SQL Server 2014 instance from the Azure gallery on your choice of any size Azure virtual machine. One of the choices from the gallery is "SQL Server 2014 Enterprise Optimized for Transactional Workloads" running on Windows Server 2012 R2. One nice thing about using a preconfigured gallery image is that you do not have to pay for any SQL Server 2014 licenses. You simply pay the hourly cost for the edition of SQL Server and virtual machine size that you choose.

SQL Server 2014 Configuration Options

Microsoft explains that "This Enterprise Edition image is optimized for OLTP workloads and is intended for VM sizes including A4, A7, A8 and A9. Once deployed, the VM comes with Windows Storage Spaces pre-configured." Microsoft also does some instance-level configuration work on SQL Server 2014, although they do not go far enough with what I would consider to be standard best practices.

They create eight tempdb data files that are all 25600MB in size, with an autogrow increment of 1024MB, which is a good default choice. They also enable TF1117 and TF1118 as start-up trace flags, which are also good choices for SQL Server. Finally, Microsoft also enables instant file initialization and lock pages in memory in the operating system, which I also agree with.

I would prefer that Microsoft also made some changes to these instance-level configuration options:

  1. backup compression default
  2. cost threshold for parallelism
  3. max degree of parallelism
  4. max server memory (MB)
  5. optimize for ad hoc workloads

Backup compression should be enabled by default in most cases. Cost threshold for parallelism often should be raised to a higher value than the default of 5, depending on your workload. Max degree of parallelism usually should be changed to a non-default value based on the number of cores in a NUMA node. This setting also depends on your workload. Max server memory should be set to a non-default value based on the amount of RAM in the virtual machine and what you are running (besides the SQL Server database engine) on the VM. Finally, I think optimize for ad hoc workloads should be enabled, pretty much in all cases.

In Microsoft's defense, it would be difficult to make a satisfactory configuration choice for some of these items without knowing (in advance) the details of your VM size and expected database server workload. That leaves the task up to you, just like with an on-premises SQL Server instance.

Azure Virtual Machine Sizing

Even though you can choose anything from an A0 Basic to an A9 Standard machine, Microsoft recommends that you choose either an A4 Standard, A7 Standard, A8 Standard, or A9 Standard size virtual machine for production usage. Pricing details for SQL Server virtual machines are listed here.

Looking at the comparative specifications for these recommendations in Table 1, it is hard to understand why you would want to choose an A4 Standard machine, since it costs the same amount per hour as the larger A7 or A8 Standard machines. Looking at the online documentation, it is not initially very clear what the actual difference is between an A7 and an A8 Standard machine. Digging a little deeper, the A8 Standard machine is considered a Compute Intensive instance, which is supposed to use a faster 2.6GHz Intel Xeon E5-2670 processor, along with two network adapters (one 10Gbps and one 32Gbps RDMA capable).

The A7 Standard virtual machine uses a somewhat slower 2.2GHz Intel Xeon E5-2660 processor, while the network connectivity appears to be standard 1Gbps Ethernet. While this sounds like a significant difference in processor and network performance, it is not really the main issue with the A-series virtual machines for SQL Server usage.

VM Size SQL Standard Rate SQL Enterprise Rate Core Count RAM Amount
A4 Standard $0.80/hr $3.00/hr 8 14GB
A7 Standard $0.80/hr $3.00/hr 8 56GB
A8 Standard $0.80/hr $3.00/hr 8 56GB
A9 Standard $1.60/hr $6.00/hr 16 112GB

Table 1: A-Series SQL Server Virtual Machine Information

The main problem with all of the A-series virtual machines is the pretty miserable I/O subsystem performance, even though Microsoft has pre-configured the disk subsystem with Windows Storage Spaces to get best performance possible given the inherent performance limitations of the A-series virtual machines and hosts. Figure 1 shows the CrystalDiskMark results for the E: drive from an A4 Standard machine from the East US Azure data center, which is meant for transaction log files.

Figure 1: A4 Standard CrystalDiskMark ResultsFigure 1: A4 Standard CrystalDiskMark Results

A much better alternative for SQL Server are the D-series virtual machines. These virtual machines cost the same per hour as the comparably sized A-series virtual machines, and they have local SSD storage that should only be used for tempdb and/or for buffer pool extensions (BPE) files, since they are not persistent. Some relevant specifications for D-series virtual machines are shown in Table 2.

VM Size SQL Standard Rate SQL Enterprise Rate Core Count RAM Amount
D4 Standard $0.80/hr $3.00/hr 8 28GB
D13 Standard $0.80/hr $3.00/hr 8 56GB
D14 Standard $1.60/hr $6.00/hr 16 112GB

Table 2: D-Series SQL Server Virtual Machine Information

The D4 Standard machine costs the same as an A4 Standard machine, but it has twice the RAM and some local SSD storage. The D13 Standard machine costs the same as an A7 or A8 Standard machine, but with the benefit of local SDD storage. The D14 Standard machine costs the same as an A9 Standard machine, but also has the benefit of local SSD storage. Given this information, it makes little sense to use a A-series virtual machine for SQL Server.

Unfortunately, the permanent drives for your SQL Server data and log files also have pretty substandard I/O performance in CrystalDiskMark, as shown in Figures 2 and 3.

Figure 2: D14 Standard CrystalDiskMark ResultsFigure 2: D14 Standard CrystalDiskMark Results Figure 3: D14 Standard CrystalDiskMark ResultsFigure 3: D14 Standard CrystalDiskMark Results

The local SSD performance is related to the size of the Azure virtual machine, with larger sizes getting better local SSD performance. The CrystalDiskMark performance results for a D14 Standard machine in the East US Azure data center are shown in Figure 4.

Figure 4: D14 Standard CrystalDiskMark Results for Local SSD StorageFigure 4: D14 Standard CrystalDiskMark Results for Local SSD Storage

The F: drive (for SQL Server data files) has slightly better results than the E: drive, but both drives have a very low level of performance for SQL Server.

Conclusion

It seems pretty clear that the D-series machines are better for SQL Server usage than the A-series machines. It also makes sense to pay close attention to the sizing and pricing of the virtual machine you decide to provision for SQL Server, since you can get more RAM at the same hourly cost. The two best choices from a performance perspective are the D13 or D14 Standard virtual machines.

The preconfigured SQL Server 2014 instances from the Azure gallery can save you a lot of money in SQL Server licensing costs, and they have much of the necessary configuration work already complete in the base image. You should still go in and make a few final configuration changes based on your preference and workload. Finally, you should take the time to run some performance benchmarks on your virtual machine so that you understand the level of performance that it can deliver.