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
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 three 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 2nd Generation Intel Xeon Scalable family (Cascade Lake-SP) processors in many respects. AMD EYPC 7002 series processors are also usually much less expensive than Intel processors.
Depending on your workload, current AMD EPYC 7002 processors can actually perform better than current Intel Xeon 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 has a more modern, modular architecture, higher memory density and bandwidth, and PCIe 4.0 support. AMD also seems to have fewer security vulnerabilities.
The fastest Intel Xeon processors have a slight single-threaded performance advantage, which can still make them good choices for OLTP workloads. On April 14, 2020, AMD released three new frequency-optimized SKUs in this family, that I wrote about here. Those are all excellent choices for SQL Server usage. Now, with these frequency-optimized SKUs, AMD has closed one of the final remaining 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 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 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. Again, if you are thinking about getting an AMD EPYC 7002 series processor that is not in the table below, you are making a mistake!
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!
AMD is on the verge of releasing their 7nm Zen 3 EPYC 7003 Series “Milan” processors. Supposedly, the large cloud vendors already have a large number of systems with these new processors. They use the same Zen 3 architecture as the AMD Ryzen 5000 series desktop processors, so we should see similar substantial performance improvements compared to the EPYC 7002 Series.
These processors are compatible with existing model servers, so they should be available quickly once they are released. It seems extremely likely that EPYC 7003 processors will have noticeably better ST performance than any current Intel server processors.
Intel plans to release their 10nm Ice Lake-SP server processors sometime in 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.
If you pick one of the processors from the two 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 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). 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!