Glenn Berry

Selecting a Processor for SQL Server 2012

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 Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download

Featured Author

Paul Randal, CEO of SQLskills, writes about knee-jerk performance tuning, DBCC, and SQL Server internals.

Paul’s Posts

Since Microsoft revamped the licensing model for SQL Server 2012, it is especially important to do some thoughtful analysis before you decide exactly which processor to use for a database server that will be running SQL Server 2012. The move to core-based licensing for SQL Server 2012 Enterprise Edition means that a careless decision about precisely what processor you will be running on can cost both a great deal of money and a great deal of performance and scalability. The same issue exists (to a lesser extent) with SQL Server 2012 Standard Edition.

The difference in SQL Server 2012 licensing costs between a good processor choice and a bad processor choice can more than pay for your hardware and your storage subsystem in many cases. Given this, how do you go about making an optimal processor choice for SQL Server 2012 Enterprise Edition? Normally, the first step would be to decide whether you wanted to use an Intel Xeon processor or an AMD Opteron processor (SQL Server 2012 does not support the Intel Itanium processor family).

Unfortunately, it is very difficult to make a good technical or business case to select an AMD Opteron processor for use with SQL Server 2012 Enterprise Edition. Since the introduction of the Intel Nehalem architecture (Intel Xeon 3500, 5500, 6500 and 7500 series) in 2008-2010, AMD has simply not been able to compete with Intel when it comes to single-threaded processor performance. AMD does not have the financial or technical resources to compete with Intel in terms of performance or power efficiency. With each new processor family release from Intel, AMD has been falling further behind. This is not a good thing for the I.T. industry, since a lack of viable competition from AMD will inevitably slow down the pace of innovation from Intel.

Since an Intel Xeon processor seems to be the only viable choice for SQL Server 2012, the next step is deciding which one of the many available Xeon families and models would be the best choice for your intended SQL Server 2012 workload. Intel has different Xeon product families for different socket count servers. For single-socket workstations and servers they have the Intel Xeon E3 family. For two-socket workstations and servers they have the Intel Xeon E5 family. Finally, for two-socket, four-socket, and eight-socket (or more) servers, they have the Intel Xeon E7 family.

Since this article is discussing SQL Server 2012 Enterprise Edition, we will ignore the single-socket Intel Xeon E3 family, since Intel Xeon E3 processors are limited to using 32GB of DDR3 RAM. There are some niche scenarios where it might make very good sense to use an Intel Xeon E3-1290V2 processor in a single-socket server with 32GB of RAM in combination with SQL Server 2012 Enterprise Edition. Perhaps you have a relatively small database where you need the absolute fastest single-threaded performance and you also need specific Enterprise Edition features such as SQL Server AlwaysOn Availability Groups.

The more common choice is between a two-socket server and a four-socket (or more) server. You are going to have to assess your workload size and intensity and decide whether it can run on a smaller, but faster two-socket server, or whether you will have to make the jump to a larger, slower four-socket or larger server. This decision is directly affected by your required total CPU capacity, total physical RAM capacity, and your total required I/O capacity (which is related to the number and type of PCI-E expansion slots in the server).

One common misconception is that bigger Intel-based servers (in terms of socket counts) are faster servers. This is simply not true, for a number of reasons. The sales volume and market share of two- socket servers is much higher than it is for four-socket and larger servers. There is also less engineering and validation work required for two-socket capable Intel processors compared to four-socket capable Intel processors. Because of these factors, Intel releases new processor architectures more frequently and earlier for lower socket count servers. Currently, Intel’s single-socket E3 family is using the 22nm Ivy Bridge and the two-socket E5 family is using the 32nm Sandy Bridge-EP, while Intel E7 family is using the older 32nm Westmere-EX microarchitecture.

Another reason is that you do not get linear scaling as you increase your socket count, even with Non-uniform memory access (NUMA) architecture processors, which scale much better than the older symmetrical multiprocessing (SMP) architecture. This means that a four-socket server will not have twice the processor performance or capacity as a two-socket server with the same model processor.

This can be confirmed by comparing the TPC-E OLTP benchmark results of two-socket systems with Intel Xeon E7-2870 processors to four-socket systems with Intel Xeon E7-4870 processors to eight-socket systems with Intel Xeon E7-8870 processors. Even though these are essentially the same processor with the same individual performance characteristics, the TPC-E benchmark score does not double as you double the socket count, as you can see in Table 1.

Processor Socket Count TPC-E Score Total Core Count TPC-E Score/Core
Xeon E7-2870

2

1560.70

20

78.04
Xeon E7-4870

4

2862.61

40

71.57
Xeon E7-8870

8

4614.22

80

57.68

Table 1: Comparison of TPC-E Scores as Socket Count Increases

When I think about comparing single-socket to two-socket, to four and eight-socket processors, I like to use a car and truck analogy. A single-socket server is like a Formula-1 race car, being extremely fast but having very little cargo capacity. A two-socket server is like a Tesla Model S, being very fast and having pretty decent cargo capacity. A four-socket server is like a large SUV, being slower but having more cargo capacity than a Tesla Model S. Finally, an eight-socket server is like a Mack truck, able to haul a huge load at a much slower rate than an SUV.

Processor Socket Count TPC-E Score Total Core Count TPC-E Score/Core
Xeon E5-2690

2

1881.76

16

117.61
Xeon E5-4650

4

2651.27

32

82.85

Table 2: Comparison of TPC-E Scores for Two Xeon E5 Processor Models

Comparing Table 1 to Table 2, we can see that the Intel Xeon E5 family does quite a bit better on TPC-E than the Intel Xeon E7 family does, which is no surprise, since we are comparing the newer Sandy Bridge-EP to the older Westmere-EX microarchitecture. From a performance perspective, the two-socket Xeon E5-2690 does much better than the two-socket Xeon E7-2870. In my opinion, you really should not be using the two-socket Xeon E7-2870 for SQL Server 2012 because of its lower single-threaded performance and higher physical core counts (which means a higher SQL Server 2012 licensing cost).

Currently, my favorite Intel server processor is the Intel Xeon E5-2690. It will give you excellent single-threaded performance and relatively affordable SQL Server 2012 licensing costs. If you need to step up to a four-socket server, then I would choose an Intel Xeon E5-4650 processor instead of using an Intel Xeon E7-4870 processor, since you will get better single-threaded performance and lower SQL Server 2012 license costs. Using TPC-E benchmark scores is an excellent way to compare the performance and SQL Server 2012 license efficiency of different processor families.