SQL Server 2014 Hardware Analysis Case Study

Imagine that you have been given the go-ahead to upgrade your entire data platform stack from SQL Server 2008 Enterprise Edition to SQL Server 2014 Enterprise Edition. You need to come up with a recommendation for your new database server hardware, looking to maximize performance while controlling your SQL Server 2014 license costs.

To help you with that effort, here is an example hardware analysis comparing an existing legacy four-socket server (a Dell PowerEdge R815) with four AMD Opteron 6168 processors to a new four-socket server (a Dell PowerEdge R920) with newer 22nm Intel Xeon E7 v2 Ivy Bridge-EX processors.

For a Dell PowerEdge R920, I would be looking at one of these three processors:

1. Xeon E7-8857 v2   (12 cores, 3.0 GHz base clock speed)

2. Xeon E7-8891 v2   (10 cores, 3.2 GHz base clock speed)

3. Xeon E7-8893 v2   (6 cores, 3.4 GHz base clock speed)

These three candidate processors all have higher base clock speeds and lower physical core counts than some other more common choices, such as the fifteen-core Xeon E7-4890 v2.

The closest equivalent AMD-based system I could find in the TPC-E benchmark results (to the legacy system) was an HP ProLiant BL685c G7 Blade Server with four, 2.2GHz AMD Opteron 6174 processors and 512GB of RAM, with an actual raw TPC-E score of 1464.12. The raw TPC-E score is a good way of measuring the overall CPU capacity of a system.

Dividing this score by the number of physical cores in the system gives us a score/core of 30.5, which is a good measure of single-threaded processor performance. Since the legacy system has slower 1.9GHz AMD Opteron 6168 processors (from the same generation and family), we simply need to adjust for the clock speed difference. Taking 1.9GHz divided by 2.2 GHz is 0.8636. Taking the actual 1464.12 score times 0.8636 gives us an estimated TPC-E score of 1264.46 for the legacy system. Dividing that by 48 physical cores gives an estimated score/core of 26.34 for the legacy system.

There is an actual TPC-E result for a four-socket IBM System x3850 X6 with four, 15-core 2.8GHz Intel Xeon E7-4890 v2 processors and 2TB of RAM, with a raw TPC-E score of 5576.27. Dividing this actual score by 60 physical cores gives us an actual score/core of 92.94.

We can adjust this actual result for the three candidate processors listed above to take into account the difference in core counts and base clock speeds to get estimated TPC-E scores for a four-socket system with each of those processors since they are from the same generation and family.

1. Xeon E7-8857 v2               5576.27 original score, times .80 (core count difference), times 1.0714 (clock speed difference), is 4779.53 divided by 48 total physical cores is 99.57 score/core

2. Xeon E7-8891 v2               5576.27 original score, times .66 (core count difference), times 1.1428 (clock speed difference), is 4233.73 divided by 40 total physical cores is 105.84 score/core

3. Xeon E7-8893 v2               5576.27 original score, times .40 (core count difference), times 1.2142 (clock speed difference), is 2708.28 divided by 24 total physical cores is 112.84 score/core

Comparing the legacy system to the actual new four-socket TPC-E result and my estimates for the other three processors, gives us this summary:

Processor                        TPC-E Score        Score/Core         Total Physical Cores     SQL 2014 License Cost (EE)

Opteron 6168                    1264.46                 26.34                     48                             $329,952.00     ($274,464.00 with AMD Core Factor discount)

Opteron 6174                    1464.12                 30.50                     48                             $329,952.00     ($274,464.00 with AMD Core Factor discount)                        

Xeon E7-4890 v2               5576.27                 92.94                     60                             $395,942.00

Xeon E7-8857 v2               4779.53                 99.57                     48                             $329,952.00       

Xeon E7-8891 v2               4233.73                 105.84                   40                             $274,960.00

Xeon E7-8893 v2               2708.28                 112.84                   24                             $164,976.00

This means that we could choose from having from roughly four times better single-threaded processor performance using the Xeon E7-8893 v2 processor or from having roughly four times more processor capacity using the Xeon E7-8857 v2 processor in a new system compared to the legacy system, depending on which processor we choose. The difference in SQL Server 2014 Enterprise Edition license costs between the different processor choices is quite dramatic. For example, going from the twelve-core processor to the faster ten-core processor lowers your SQL Server license costs by about as much as the actual server would cost.

TPC-E Single-Threaded Performance Leaderboard

Fujitsu recently posted a new TPC-E benchmark result of 3777.08 for SQL Server 2014, using a two-socket server with the 18-core, 22nm Intel Xeon E5-2699 v3 (Haswell-EP) processor. This is the highest ever actual TPC-E score for a two-socket server, which sounds quite impressive on the surface.

One thing that I have been doing for years is to take the actual, raw TPC-E score, and divide it by the number of physical cores in the system (which is how SQL Server 2012/2014 is licensed on physical servers) to come up with a “Score/Core” figure as shown in Table 1. This simple calculation helps you evaluate the single-threaded performance of a particular processor, which is very relevant for OLTP workloads. Looking at the TPC-E results like this, the Intel Xeon E5-2699 v3  comes in at seventh place on the TPC-E Single-Threaded Performance Leaderboard. Why is this?

The server vendors (who put together these official TPC-E submissions) will always use the “top of the line” processor for a particular model server for one of these benchmark efforts. This top-level SKU is going to have the highest core count available from a particular CPU family and generation. Unfortunately, the highest core count processors from a particular CPU family and generation will run at lower base and turbo clock speeds than the lower core count, “frequency optimized” models from that same CPU family and generation. This means that the Score/Core result tends to decrease as the number of cores increases. This is partially offset by the architectural improvements that are added to each new generation processor, but those improvements usually don’t make up completely for the lower clock speeds.

So what relevance does this have for the average database professional?

Well, think about how much it would cost to purchase 36 processor core licenses for SQL Server 2014 Enterprise Edition. The answer is about $247,392.00, which is about ten times what a fully-loaded two-socket server would cost. If you were to choose the eight-core Intel Xeon E5-2667 v3 processor, with its much higher 3.2GHz base clock speed, it would only cost about $109,952.00 for the SQL Server 2014 licenses. You would also get probably 30-35% better single-threaded performance than with the 18-core model, while losing perhaps 35-40% of your total processor capacity.

If you are worried about total capacity, you could even buy a second server (if you could split your workload), and save enough on the license costs (32 core licenses vs. 36 core licenses) to pay for the second server. If you did this, you would have more total processor capacity, double the RAM, and much better OLTP performance. Remember, the actual raw TPC-E score is a gauge of the total processor capacity of the system, while the Score/Core helps you evaluate single-threaded processor performance.

I really wish the server vendors would take the relatively easy and inexpensive step of testing their benchmark configurations with different model processors. Once they had everything setup and tuned for the high-core count flagship processor, they could simply repeat the test runs and validation process for some of the more interesting lower core count “frequency optimized” processor models, and submit those results. TPC could help by listing the Score/Core results for all of the TPC-E benchmark submissions.

 

TpsE Score/Core System Processor Total Cores Sockets
1881.76 117.61 HP ProLiant DL380p Gen8 Intel Xeon E5-2690                                         16 2
1871.81 116.99 PRIMERGY RX300 S7 Intel Xeon E5-2690                                         16 2
1863.23 116.45 IBM System x3650 M4 Intel Xeon E5-2690                                         16 2
2590.93 108 IBM System x3650 M4 Intel Xeon E5-2697 v2 24 2
1284.14 107.01 HP ProLiant DL380 G7 Server Intel Xeon X5690                                  12 2
1268.3 105.69 PRIMERGY RX300 S6 12×2.5 Intel Xeon X5690                                  12 2
3777.08 104.92 PRIMERGY RX2540 M1 Intel Xeon E5-2699 v3 36 2
1246.13 103.84 PRIMERGY RX300 S6 Intel Xeon X5680                                  12 2
2472.58 103.02 PRIMERGY RX300 S8 Intel Xeon E5-2697 v2 24 2
817.15 102.14 IBM System x3650 M2 Intel Xeon X5570                                 8 2

Table 1: TPC-E Single-Threaded Performance Leaderboard

New Versions of Useful Tools for SQL Server

There is a completely rewritten version of Geekbench, which is a very useful cross-platform processor and memory performance benchmark that you can use for comparing and validating processor and memory performance on anything from your laptop to a very expensive database server in just a few minutes, with no configuration of the benchmark needed. As the Primate Labs web site puts it:

Geekbench 3 is Primate Labs’ next-generation processor benchmark, with a new scoring system that separates single-core and multi-core performance, and new workloads that simulate real-world scenarios. Geekbench 3 makes it easier than ever to find out if your computer is up to speed.

There is also a new 1.66 version of CPU-Z, that has support for some upcoming processors, such as the Intel Xeon E5-2600 v2 series (Ivy Bridge-EP).

image