sqlskills-logo-2015-white.png

SQLskills SQL101: Processor Selection for SQL Server

As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

 

SQL Server Licensing

Since SQL Server 2012, Microsoft has been using core-based licensing for SQL Server Enterprise Edition. With non-virtualized servers, you are required to purchase a SQL Server core license for every single physical processor core in the entire host machine, period. Every single physical core present in the host machine must be licensed. It doesn’t matter if you have disabled physical cores in the host BIOS, or if you have exceeded the physical core license limit for SQL Server Standard Edition, you still have to license every single physical core in the machine.

With virtualization, the story is slightly different. Normally, you have to purchase a SQL Server core license for every single vCPU in your virtual machine, with a minimum of four core licenses per VM. The exception to this is if you purchase enough SQL Server core licenses for all of the physical cores in the entire host machine, and if you also purchase Microsoft Software Assurance. If you do this, you can then create as many VMs with as many vCPUs as you like, without worrying about counting the vCPU cores at all.

Windows Server 2016 Licensing

Windows Server 2016 has a new core-based licensing model with a minimum of eight physical core licenses per processor and 16 physical core licenses per host machine. Fortunately, these Windows Server 2016 core licenses are relatively affordable, especially for Windows Server 2016 Standard Edition (which is all that is required for most SQL Server 2016 instances). The danger from this new licensing model is that it may encourage well-meaning server administrators to select a processor with more physical cores than they actually need for SQL Server, in order to “get their money’s worth” from the Windows Server 2016 licenses that they are required to buy for a new server. This could actually be a very expensive mistake from a SQL Server 2016 licensing cost perspective!

Modern Server Processors for SQL Server

Current generation Intel server processors have anywhere from four to twenty-four physical cores in each physical processor. For two-socket servers, this means the Intel Xeon E5-2600 v4 “Broadwell-EP” Product Family. For four-socket and higher servers, this means the Intel Xeon E7-8800 v4 “Broadwell-EX” Product Family. Upcoming server processors from both AMD and Intel will have up to thirty-two physical cores per physical processor.

Previously, I explained the relevant differences between physical sockets, physical cores and logical cores here. One important fact to keep in mind is that Microsoft does not care (for pricing purposes) whether a physical core is fast or slow. Regardless of the performance of the core, the per-core license cost is exactly the same.

Knowing this, you should purposely choose a particular processor SKU that has the best single-threaded performance possible for a given physical core count. A very common mistake I see is where a server administrator purposely selects a low-range or mid-range processor SKU (at a given core count) to save a small amount of money on the hardware. Quite often, they save far less than 1% of the total system cost, but give up anywhere from 20-40% of their single-threaded performance.

For any particular server processor product family, you have a range of available processor SKUs with different physical core counts and other relevant performance specifications, such as base clock speed, L3 cache size, and QPI speed. Generally speaking, the lower core count processors have much better single-threaded CPU performance than the higher core count processors from the same product family. Quite often, you can purposely pick a fast, lower core count processor to both get better single-threaded CPU performance and to save a huge amount of money on your SQL Server 2016 licensing costs.

Conclusion

The key takeaway here is that it is very important to do some thoughtful analysis of your available processor choices for a server when you are going to have a SQL Server workload. The worst thing you can do is to just let someone else (who may not fully understand how SQL Server licensing works) make the choice with no input from you. It is unfortunately all too easy to make a very bad choice that costs significantly more than it should and also gives up a lot of performance.

I have written a number of articles for SQLPerformance.com that get into much more detail on this subject.

 

 

One thought on “SQLskills SQL101: Processor Selection for SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.