Choosing a Processor for SQL Server

Choosing a Processor for SQL Server is always a challenge and I often find DBAs and organizations struggling with the same. I have request SQL Server Expert Glenn Berry (b|t) to help us in selecting the right CPU. I am confident that this blog will help us select the right Processor (CPU) 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 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

The new 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 a foolish, the false economy in most cases. Since SQL Server is hardware intensive and relatively expensive to license, economizing on hardware is usually a bad idea.

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 gives 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 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. 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.

Table 1: SQL Server 2019 License Costs

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 couple of years, AMD has released two generations of AMD EPYC server processors that has changed this old guidance. The latest 7nm AMD EPYC 7002 (Rome) series processors compare very favorably to the latest 14nm 2^nd Generation Intel Xeon Scalable family (Cascade Lake-SP) processors in many respects. AMD EYPC 7002 series are also much less expensive than Intel processors.

Depending on your workload, current AMD EPYC processors can actually perform better than current Intel Xeon processors. This is especially true for DW/Reporting style workloads. The fastest Intel Xeon processors have a slight single-threaded performance advantage, which can make them good choices for OLTP workloads.

One of AMD’s main advantages is in memory capacity and bandwidth, along with storage bandwidth. Both of these are reasons why AMD processors do very well with DW workloads.

Table 2: AMD EYPC 7002 vs. Intel Cascade Lake SP

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.

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 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 Table 3, you are making a mistake!

Table 3: Cascade Lake-SP System Metrics (2P)

Intel is rumored to be releasing a few new SKUs as part of a “Cascade Lake-SP Refresh” as I have written about here. If this happens, there will be a few changes in Table 3.

Recommended AMD Processors

AMD has a smaller number of SKUs in the current EPYC 7002 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 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. Again, if you are thinking about getting an AMD EPYC processor that is not in the table below, you are making a mistake!

Table 4: AMD EPYC 7002 Series System Metrics (1P)

Final Words

If you pick one of the processors from Table 3 or Table 4, 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 two 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). If you have any thoughts or questions about this, please reach out to me on my blog. You can also follow me on Twitter, where I am @GlennAlanBerry. Thank you for reading!

Reference: Pinal Dave (https://blog.sqlauthority.com)