Picking a Good AWS EC2 Instance Type for SQL Server

Introduction

I recently helped a customer that was having performance issues running SQL Server 2012 Standard Edition on an AWS EC2 instance. They had a relatively large EC2 i3.16xlarge instance with 64 vCPUs in two sockets, and 488GB of RAM. The AWS EC2 instance type had Intel Xeon E5-2686 v4 “Broadwell” processors. Despite this, they were seeing lower performance than they needed. This case illustrates the importance of picking a good AWS EC2 instance type for SQL Server.

What Were the Main Problems?

There were several problems with this combination of components. First was the mismatch between SQL Server 2012 Standard Edition and the size of the EC2 instance. Second was the age and relatively low performance of the EC2 I3 instance type host processor.

SQL Server 2012 Standard Edition Core License Limits

SQL Server 2012 Standard Edition can only use 16C/32T (32 vCPUs when virtualized). The VM had 64 vCPUs, but SQL Server could only use 32 vCPUs.

Newer versions of SQL Server will have a message like this in the SQL Server error log:

SQL Server detected 2 sockets with 16 cores per socket and 32 logical processors per socket, 64 total logical processors; using 32 logical processors based on SQL Server licensing.

To make matters worse, if you install SQL Server 2012 Standard Edition on a VM with 64 vCPUs in two sockets, all of your 32 available license limit vCPUs will be running on the first NUMA node. No schedulers will be running on the second NUMA node. You can correct the NUMA node imbalance with an ALTER SERVER CONFIGURATION command, as I discuss here.

Unfortunately, most cloud providers (not just AWS) often force you to use more vCPUs than you want to get a sufficient amount of RAM for SQL Server. That may have been a factor when this VM was first provisioned. The i3 EC instance type was probably the best choice available when this VM was provisioned.

SQL Server 2012 Standard Edition Memory License Limits

SQL Server 2012 Standard Edition can only use 64GB of RAM per instance for the SQL Server buffer pool. Other components besides the buffer pool will use some memory, so ideally your VM would have at least 96GB or 128GB of RAM.

Having a VM with 488GB of RAM when you only have a single instance of SQL Server 2012 Standard Edition (or even SQL Server 2019 Standard Edition) is unnecessary and expensive. SQL Server 2012 fell out of Extended Support on July 12, 2022, so it won’t be getting any more security updates.

Old and Slow Host Processors

The existing AWS EC2 I3 instance type had Intel Xeon E5-2686 v4 “Broadwell” processors. This particular SKU is a “bespoke” custom model made for Amazon, so it is not in the Intel ARK database. Amazon (like most cloud providers) is not very forthcoming about the exact specifications of the bespoke SKUs that they use. This makes it more difficult to make a good choice for SQL Server.

One external source states that the Intel Xeon E5-2686 v4 has 18C/36T with a base clock of 2.3 GHz and a max turbo clock of 3.0 GHz, with a 45MB L3 cache and a TDP of 145 watts. That matches pretty well with what CPU-Z shows.

The Intel Xeon E5 v4 Broadwell family was released on Q1 2016, so it is getting pretty old now. They are widely available on eBay for less than $200.

This SKU was not a very good CPU for SQL Server when it was released in 2016, and it certainly is not a good choice in late 2022. A much better SKU from that family (if you were choosing instead of Amazon) would be the Xeon E5-2697A v4. It has 16C/32T with a base clock of 2.6 GHz and a max turbo clock of 3.6 GHz, with a 40MB L3 cache and a TDP of 145 watts.

Remember, for licensing purposes Microsoft only cares about the number of cores that you have, not how fast they are. Having higher base and turbo clock speeds (in the same family and generation) makes a big difference for performance. It may also let you run the same workload with fewer cores.

Even in the cloud, your CPU choice matters a lot for SQL Server. I talk a lot more about that subject in this post:

• Is the CPU Important for SQL Server?

Rather than speculate, it is better to run some benchmarks on your cloud VM. Here is the CPU-Z benchmark for the Amazon VM in question. The CPU-Z single-threaded score of the old VM is 310.5, which is not very good compared to other current Amazon EC2 instance type offerings. The CPU-Z multi-threaded score of the old VM is 12,888.2, which is for the entire VM.

Remember, SQL Server 2012 Standard Edition can only use half of the vCPUs for this VM, so SQL Server only has 50% of the total VM CPU capacity available. This means that this VM would only have the equivalent CPU-Z multi-thread score of 6,444.1 for SQL Server 2012 Standard Edition.

Picking a Good AWS EC2 Instance Type for SQL Server

Amazon has a multitude of EC2 instance types, that are organized into five main categories.

• General Purpose
• Compute Optimized
• Memory Optimized
• Accelerated Computing
• Storage Optimized

For SQL Server, you probably want a storage optimized instance type for most workloads, since this gives you excellent local storage performance and a decent amount of RAM per core. Amazon describes storage optimized EC instance types as:

Storage optimized instances are designed for workloads that require high, sequential read and write access to very large data sets on local storage. They are optimized to deliver tens of thousands of low-latency, random I/O operations per second (IOPS) to applications.

Amazon EC2 Instance Types – Amazon Web Services

The relatively new i4i instance type has Intel Xeon Platinum 8375C “Ice Lake” processors with 32C/64T with a base clock of 2.90 GHz and a turbo clock of 3.50 GHz, with a 54 MB L3 cache. This is another bespoke SKU from the 10nm 3rd Generation Intel Xeon Scalable Processor family that was released in Q2 2021. Surprisingly, this custom SKU has better specifications than the regular 32C SKUs from that family.

This processor performs significantly better in CPU-Z than the older Intel Xeon E5-2686 V4. The CPU-Z single-threaded test actually understates the real difference for SQL Server, since it is not measuring memory performance or bandwidth.

Another huge advantage for the I4i instance type is the custom NVMe AWS Nitro SSDs that have 60% lower storage I/O latency, and 75% lower storage I/O latency variability compared to EC2 I3 instances. You also get nearly PCIe 3.0 x4 level sequential performance, which is pretty amazing for cloud storage. Here is an example CrystalDiskMark result.

Final Words

SQL Server 2019 also has a lot of performance improvements compared to SQL Server 2012 and being on Enterprise Edition also unlocks more performance improvements compared to Standard Edition.

This is a recap of the infrastructure improvements from moving to a new VM with SQL Server 2019 Enterprise Edition.

• 47% Single-threaded CPU improvement
• 186% Multi-threaded CPU improvement
• 687% RAM increase
• About a 5X storage performance increase

The new instance should “feel” much faster as you do daily DBA tasks. For example, creating an index on a large table will be much faster, and won’t require an outage. More importantly, you have a lot more performance, capacity, and scalability for the applications.

If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!