Choosing a Processor for SQL Server

Introduction

Since SQL Server uses core-based licensing, it is very important that you do some careful analysis as part of choosing a processor for SQL Server usage. SQL Server core licenses are relatively expensive, and it is very common to spend a lot more money on SQL Server license costs than on your hardware and storage. If you make a wise processor choice, you can get the best performance and scalability possible for a given licensing cost.

On the other hand, a poor processor choice can cause a huge loss in performance and scalability and/or a much larger licensing expense than necessary. Don’t let your server admin just pick the processor for SQL Server usage without any input or vetting from you.

Modern Server Hardware is Affordable

Current one and two-socket servers with the latest generation processors from AMD and Intel are much less expensive than two and four-socket servers from a few years ago. If you are getting ready to deploy a new version of SQL Server, it is usually a bad idea to reuse existing hardware that is several years old. This is a common mistake that I think is foolish, false economy in most cases. Since SQL Server is hardware intensive and relatively expensive to license, economizing on hardware is usually a bad idea.

Keep in mind that good hardware is not a panacea! Bad workloads will still win. Poorly designed applications and databases can easily overwhelm the best hardware and storage. Having fast hardware and storage does give you more margin for error. It also gives you better performance and more capacity headroom. The details of your hardware are actually very important for SQL Server.

Think About Your Workload

As you are thinking about processor selection for SQL Server, think about what type of workload you have. Is it an OLTP workload, a DW/Reporting workload, or some sort of mixed workload? This will affect your hardware and storage choices. You also should consider whether you will be running bare metal or virtualized. If possible, you should try to avoid general-purpose, high core count, low clock speed processors for SQL Server virtualization hosts.

You should also know whether you will be using SQL Server Standard Edition or SQL Server Enterprise Edition before you choose your processor. SQL Server 2019 Standard Edition is restricted to the lesser of four sockets or 24 cores per instance. For bare-metal servers, this count is based on physical cores. With virtualized instances, this count is based on virtual cores. Virtual cores might map to physical or logical cores depending on whether SMT (aka Hyperthreading) is enabled on the host.

So, if you know you will be using SQL Server 2019 Standard Edition on a bare metal server, do not select processors that will cause you to have more than 24 total physical cores in the system.

SQL Server 2019 License Costs

SQL Server 2019 Standard Edition is $1,793.00 per core, while Enterprise Edition is $6,874.00 per core. Core licenses are sold in two packs, and you must purchase a minimum of four core licenses per instance. This is true whether the instance is bare metal or virtualized. One exception to this is if you have a virtualized instance, and you have purchased enough core licenses for the entire virtualization host (and you have Microsoft Software Assurance). If you have done all of this, then you don’t have to worry about counting vCPUs for all the VMs on the host.

Choosing a Processor for SQL Server
SQL Server 2019 License Costs

Choosing a Processor for SQL Server

Since we have covered the necessary background information, it is time to dive a little deeper into the process of choosing a processor for SQL Server.

Choosing Between AMD and Intel Processors

Over the past 10-12 years, Intel processors have usually been a much better choice for most SQL Server usage compared to AMD processors. This was because of the extremely poor single-threaded performance of older AMD Opteron server processors.

Over the past four years, AMD has released three generations of AMD EPYC server processors that has absolutely changed this old guidance. The latest 7nm AMD EPYC 7003 (Milan) series processors compare very favorably to the latest 14nm 2nd Generation Intel Xeon Scalable family (Cascade Lake-SP) processors in most respects. AMD EPYC 7003 Series processors actually have faster single-threaded CPU performance than Intel Cascade Lake with most SKUs.

With most SQL Server workloads, the latest AMD EPYC 7003 processors will perform better than current Intel Xeon Cascade Lake Refresh processors. This is especially true for DW/Reporting style workloads. One of AMD’s main advantages here is in memory capacity and bandwidth, along with storage bandwidth. Both of these are reasons why AMD processors do very well with DW workloads. AMD EPYC 7003 processors will also perfom very well with OLTP workloads.

AMD EPYC 7003 Series Major Changes

AMD has a more modern, modular architecture, higher memory density and bandwidth, and PCIe 4.0 support. AMD also seems to have fewer security vulnerabilities than Intel.

The fastest Intel Xeon processors used to have a slight single-threaded performance advantage, which made them good choices for OLTP workloads. On March 15th, 2021, AMD released the AMD EPYC 7003 Series, that I wrote about here. Now, with the AMD EPYC 7003 Series, AMD has closed one of the final old gaps with Intel.

How Do You Actually Compare Processors?

One method that I have used for years to look at actual TPC-E benchmark scores. This is an OLTP database benchmark that has been around since 2007, that has nothing but SQL Server results. Whenever a new generation server processor is released, server vendors will submit official TPC-E benchmark results for the highest core count “flagship” processor. You can use these official scores as a starting point to make some simple calculations to come up with an estimated TPC-E score for other processors from that same family. This lets you compare the performance and capacity of all of the processor SKUs in that family.

You use the core count and base clock speed of each processor (compared to the actual flagship processor) to make these calculations and come up with estimated scores for each processor SKU in the same family. This is only valid for processors that the exact same family.

With these calculations, you will get an estimated total TPC-E score and an estimated TPC-E score/core. The total score is a measure of the total CPU capacity of the processor(s) in that system. Looking at the estimated score/core will let you compare the single-threaded performance of that processor. This helps you understand how “fast” that processor is for single-threaded performance.

I have done these calculations for the “best” AMD and Intel server processors. The results are shown in the two tables below.

Recommended AMD Processors

AMD has nineteen SKUs in the current EPYC 7003 Series, ranging from 8 physical cores to 64 physical cores. There are some cases where they also have multiple SKUs available for a given core count. Among these, there are also specific processor SKUs that are the “best” for SQL Server usage for each core count. These “best” processors will give you the best performance at that core count. Unlike Intel, there is generally less variation in the base clock speeds of the different core count processors.

The table below shows these “best” processors at each core count. These numbers are for a one-socket system. The five SKUs that are in bold green are actually the best choices.

Again, if you are thinking about getting an AMD EPYC 7003 series processor that is not in the table below, you are making a mistake!

AMD EPYC 7003 System Metrics

Just for reference, here are the best choices from the previous generation AMD EPYC 7002 Series. You can see a substantial generational increase with the new processors.

AMD EPYC 7002 System Metrics

Recommended Intel Processors

Intel has a large number of SKUs in the current Cascade Lake-SP family, ranging from 4 physical cores to 28 physical cores. Often, there are multiple SKUs available for a given core count. Among these, there are specific processor SKUs that are the “best” for SQL Server usage for each core count. These “best” processors will give you the best performance at that core count. Lower core count processors from the same family usually will have higher base clock speeds than the higher core count models.

The table below shows these “best” processors at each core count. These numbers are for a two-socket system with both sockets populated. Basically, if you are thinking about getting an Intel Cascade Lake-SP processor that is not in the table below, you are making a mistake!

Intel Cascade Lake-SP System Metrics
Intel Cascade Lake-SP System Metrics

Upcoming Releases

Intel plans to release their 10nm Ice Lake-SP server processors sometime in early 2021. They claim it will be in Q1 of 2021, but their track record for on time releases is not very good lately. Ice Lake-SP will require new model servers. The top SKU for Ice Lake-SP is rumored to be the 40C/80T Intel Xeon Platinum XCC 8380.

Final Words

If you pick one of the processors from the three tables above, you are on the right track. If someone in your organization is thinking about choosing a processor for SQL Server usage that is not in those three tables, they are probably making an expensive mistake. It is pretty common for well-meaning server administrators to pick a lower-priced (and slower) SKU at the same core count in order to save some money on the cost of the processor. This small savings from the less expensive processor often causes a huge reduction in performance and capacity. This just doesn’t make sense given the licensing costs for SQL Server.

This method for comparing and selecting a processor for SQL Server usage is valid regardless of the hosting type. It works for bare metal servers, virtualized servers, cloud VMs (IaaS) and even cloud (PaaS). I hope this discussion about choosing a processor for SQL Server has been useful for you!

If you have any thoughts or questions about this post, please ask me here in the comments or on Twitter. You can also follow me on Twitter, where I am @GlennAlanBerry. Thank you for reading!

SQL Server

5 thoughts on “Choosing a Processor for SQL Server

  1. Hi Glenn, very interesting article. What about the Ryzen CPUs (not TR) and Intel Coffee Lake CPUs. Are they are any good for SQL? Specifically Information Services SQL?

    1. AMD and Intel client CPUs would be fine for development or test usage, on smaller SQL Server workloads. I would not recommend them for production usage, since most of them don’t officially support ECC RAM. Most machines that would use those processors also don’t have redundant, hot-swappable components. Finally, current mainstream desktop CPUs only support 128GB of RAM and they don’t have enough PCIe lanes to support a large SQL Server production workload.

  2. Hi Glenn, thanks for your effort for the CPU benchmark. Regarding to the Intel Processors, would you recommend a single socket Xeon Gold 6250 over two Xeon Gold 5222? Both would be 8 cores in total but I do not have to worry about NUMA tuning.

    1. Yes, a one-socket machine with a Gold 6250 would be better than a two-socket machine with two Gold 5222 processors, at least from a CPU perspective. You would have less memory capacity and fewer total PCIe lanes though.

Leave a Reply

%d bloggers like this: