Glenn Berry

Selecting and Configuring Hardware for SQL Server 2016 Standard Edition

Measure and improve performance for distributed workloads on Microsoft Analystics Platform Server.  More
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Aaron Bertrand, Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, and Joe Sack.

Register to Download

Featured Author

Erin Stellato is a Principal Consultant with SQLskills and a Microsoft Data Platform MVP.

Erin’s Posts

Since the release of SQL Server 2016 Service Pack 1 on November 16, 2016, with many very useful programmability-related features that were previously only available in Enterprise Edition, it has become much more feasible for many organizations to purposely choose to use SQL Server 2016 Standard Edition than it was in the past.

If you are thinking about doing this, you need to be aware of some common issues and pitfalls that you may run into when you install and use SQL Server 2016 Standard Edition on a new server with modern hardware.

Memory Limits and Configuration

The first issue is the per-instance licensing limits for SQL Server 2016 Standard Edition. The first license limit is the amount of memory that you can use for the buffer pool for each instance of SQL Server 2016 Standard Edition, which is only 128GB, just as it was in SQL Server 2014 Standard Edition. Personally, I think this limit is artificially low given the memory density of modern two-socket servers, but it is a limit we must deal with.

Current two-socket servers that use Intel Xeon E5-2600 v4 product family processors can use up to 12 DIMMs per processor, while 32GB DDR4 ECC DIMMs are the highest capacity that are also affordable per GB. Each server with this processor family has 4 memory channels per processor, with each channel supporting up to 3 DIMMs. A fully populated two-socket server with twenty-four 32GB DIMMs would have 768GB of RAM, which is far more than a single instance of SQL Server 2016 Standard Edition is allowed to use.

Since SQL Server 2016 Standard Edition has such a low per-instance memory limit, you should purposely choose an appropriate memory configuration that will let you use all of the license-limit memory while also getting the best memory performance possible. Only populating one DIMM per memory channel will give you the absolute best memory performance supported by your processor(s).

The major server vendors, such as Dell, offer detailed guidance on the possible memory configurations for their servers, depending on the number and specific type of processor selected. For SQL Server 2016 Standard Edition in a two-socket server with two Intel Xeon E5-2600 v4 family processors, choosing eight, 32GB DDR4 DIMMs would give you 256GB of RAM, running at the maximum supported speed of 2400MT/s.

This would allow you to set max server memory (for the buffer pool) to 131,072 MB (128GB), and still have plenty of memory left over for the operating system and for possible use by columnstore indexes and in-memory-OLTP. You would also have sixteen empty DIMM slots that could be used for future RAM expansion (which you could take advantage of if you did a subsequent Edition upgrade to Enterprise Edition). Another use for some of those empty DIMM slots would be for “tail of the log caching” on NVDIMMs (which is supported in SQL Server 2016 Standard Edition with SP1).

Processor License Limits

SQL Server 2016 Standard Edition is also limited to the lesser of four sockets or 24 physical processor cores. With current and upcoming processor families from both Intel and AMD that will have up to 32 physical cores, it is very easy to inadvertently exceed the per-instance processor core limit, with a number of dire consequences for performance and licensing costs.

The first negative effect of doing this is how SQL Server 2016 Standard Edition will allocate your available license-limit physical cores across your NUMA nodes. For example, if you had a new two-socket server that had two, 16-core Intel Xeon E5-2697A v4 processors, by default, SQL Server 2016 Standard Edition would use sixteen physical cores on NUMA node 0 and only eight cores on NUMA node 1, which is an unbalanced configuration that won’t perform as well as it could. You can fix this issue with an ALTER SERVER CONFIGURATION command as I describe here.

To add insult to injury in this situation, Microsoft would also expect you to purchase core licenses for all 32 physical cores in the machine, even though you are only allowed to use 24 physical cores per instance. This would be a roughly $15K additional license cost, for core licenses that you would not be able to use, unless you decided to run multiple instances on the same host machine. The additional license cost would pay for a typical two-socket server, depending on how it was configured.

Another common pitfall that you should avoid with Standard Edition is creating a virtual machine that has more than four sockets. If you do that, SQL Server Standard Edition will only use four sockets because of the socket license limit.

Processor Selection

Currently, the most modern Intel Xeon processor family for two-socket servers is the 14nm Intel Xeon E5-2600 v4 product family (Broadwell-EP) that was released in Q1 of 2016. Intel is on the verge of releasing the next generation 14nm Intel Xeon E5-2600 v5 (Skylake-EP), which is already available in the Google Cloud Platform. My guess is that these new processors (which will require new model servers) will be publicly available in Q2 of 2017.

Given this 24-physical core license limit, it is extremely important that you do not select a processor that has more than 12 physical cores (if you plan on populating both sockets of a two-socket server). This limits your selection of processor SKUs somewhat, but there are still four great choices available, as shown in Table 1.

Model Cores Est. TPC-E System Score Score/Physical Core License Cost
Xeon E5-2687W v4 24 3,673.00 153.04 $44,592.00
Xeon E5-2667 v4 16 2,611.91 163.24 $29,728.00
Xeon E5-2643 v4 12 2,081.36 173.44 $22,296.00
Xeon E5-2637 v4 8 1,428.39 178.54 $14,864.00

Table 1: Comparative two-socket system processor metrics

Table 1 shows the total physical cores, estimated TPC-E score, estimated TPC-E score/physical core, and total SQL Server 2016 Standard Edition license cost for a two-socket system, populated with two of the selected processor. You might notice that I have a twelve-core processor, an eight-core processor, a six-core processor, and a four-core processor, but there is no ten-core processor in Table 1. This is by design, since the three available ten-core processor models are all very bad choices for SQL Server, because of their very low base clock speeds.

The estimated TPC-E score for the entire system is a measure of the total CPU capacity of the system, while the score/core is a measure of the single-threaded CPU performance of that specific processor.

Summary

If you want the best performance possible at the lowest hardware and SQL Server licensing cost for a SQL Server 2016 Standard Edition instance, you should choose a memory configuration that only uses one DIMM per memory channel (meaning eight DIMMs total in a two-socket system with two Intel Xeon E5-2600 v4 family processors).

You should also purposely choose one of the four processors listed in Table 1. Any other processor choice is a potentially expensive mistake from this perspective.

Finally, if you are going to be using SQL Server 2016 Standard Edition, you should investigate and test whether Buffer Pool Extension (BPE) might help performance with your workload. Depending on your workload, “tail of the log” caching on an NVDIMM might also be very beneficial for your transaction log performance.