One crucial part of doing a complete data platform upgrade is proper hardware and processor selection. Because of how SQL Server 2012 and newer core-based licensing works, processor selection is extremely important from a performance/scalability perspective and from a license cost perspective. Often, your SQL Server core license costs can far outweigh the hardware and storage costs, and a poor processor selection can make this situation much worse than it has to be.
On a non-virtualized server, every single physical core in the machine must have a SQL Server core license, with a minimum of four physical cores per socket. For licensing purposes, it does not matter if you have disabled any of cores in the BIOS, you still have to pay for them. On a virtualized server, you have two main choices. First, you can just license the virtual cores for each VM (which might map to underlying logical or physical cores in the host), with a minimum of four virtual cores per VM. Your other choice (if you have Microsoft Software Assurance) is to license all of the physical cores in the entire host, after which, you can create as many VMs with as many virtual cores as you like, assuming you are running Windows Server 2016 Datacenter Edition.
It also does not matter (to Microsoft) whether each core has good or bad performance, the license cost is exactly the same. Microsoft explains the Compute Capacity Limits by Edition of SQL Server to help make this more clear. What this all means is that you want to pick a particular processor at a given physical core count that has the best single-threaded performance possible, in order to maximize the value you get from each SQL Server core license. Quite often, you can and should pick a lower core count processor SKU that runs at a higher base clock speed and also may have more L3 cache per core rather than a high core count processor SKU (from the same processor family).
Intel recently introduced their new 14nm Intel Xeon Scalable Processor family for server usage. These processors use a completely new naming convention from previous Xeon families, and they are divided into Platinum, Gold, Silver, and Bronze lines. For SQL Server usage, I would avoid the Silver and Bronze lines completely, since they have greatly reduced clock speeds and often do not have Turbo Boost or hyper-threading. Their lower hardware cost is a false economy, since you give up so much performance per core, while your license costs remain the same per core.
Instead, you should focus primarily on the Gold line (unless you really need extremely high core counts or eight-socket support, which requires a Platinum model). Within the Gold line there are a number of particular processor SKUs that I think are the “best” choice for SQL Server at a particular physical core count. These choices are shown in Figure 1.
Figure 1: Preferred Intel Scalable Processor SKUs for SQL Server Usage
Intel needlessly complicates this by their greedy product differentiation efforts. For example, they have some SKUs with an M suffix that support 1.5TB of RAM per socket instead of the 768GB of RAM per socket you get with a SKU that does not have the M suffix (so Xeon Platinum 8180M vs. Xeon Platinum 8180, for example). An M SKU means a $3,000.00 price increase, which you may want to pay for some SQL Server workloads. Keep in mind that Windows Server 2016 has a 24TB RAM limit, which you still would not hit with an eight-socket server with eight Xeon Platinum 8180M processors.
For most SQL Server workloads, you should be looking at the 4, 6, 8, or 12 core models in Figure 1. For example the 12-core Xeon Gold 6146 has a higher base clock speed and more L3 cache/core compared to the 14-core Xeon Gold 6132 or 16-core Xeon Gold 6142 models, so that you will actually have more capacity and better single-threaded performance with the Xeon Gold 6146, at a much lower SQL Server license cost.
The bottom line here is that you really need to some careful analysis to avoid making an expensive mistake. Make sure you don’t let Shon, the server admin make your hardware selection choices with no input from you!
Additional Resources
I have a new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.
Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.
Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017.
Here is a link to the complete series about upgrading SQL Server.