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.

Bigger Database Servers Get Faster

For quite some time, I have been talking about how current Intel-based four-socket database servers have had significantly lower single-threaded processor performance than current Intel-based two-socket database servers. This is because the first generation Intel Xeon E7 processors were using the relatively old 32nm Westmere microarchitecture that was introduced in early 2011 for the initial Xeon E7 (Westmere-EX) product line.

These E7 processors also use much lower base and turbo clock speeds than current Xeon E5 v2 processors, which also hurts their single-threaded processor performance. They do have higher overall concurrent load capacity due to higher total memory capacity and more total processor cores, but the individual processor cores in most four-socket servers have been much slower than what you find in a modern two-socket server. Simply put, bigger servers are not faster servers. It is like comparing an eighteen wheeler truck to a Tesla Model S.

Now, that old assessment is going to change somewhat, with the release of the 22nm Intel Xeon E7 Processor v2 Family (Ivy Bridge-EX), and new model servers from the major server vendors that have even higher memory capacity, PCI-E 3.0 support, and 12Gbps SAS/SATA support, along with much faster RAID controllers. These processors are a substantial improvement over the previous generation 32nm Intel Xeon E7 processors (Westmere-EX) that have been available since early 2011.

It will still be possible to configure a new two-socket server, such as a Dell PowerEdge R720, with an appropriate 22nm Intel Xeon E5-2600 Processor v2 Family (Ivy Bridge-EP) processor that will have better single-threaded performance than a new four-socket server such as a Dell PowerEdge R920, but the gap will not be nearly as large as it once was.

The actual good news here for a database professional is the fact that you will be able to have a four-socket server that has as much load capacity as a previous generation, eight-socket server, that also performs nearly as well as a current two-socket server, while paying 25% less for your SQL Server 2012/2014 license costs (compared to a previous generation eight-socket server). This is a pretty big gift from Intel!

A more pessimistic view is that your SQL Server 2012/2014 license costs could rise by 50% as you move from an existing server equipped with four, ten-core Xeon E7-4870 processors (with a total of forty physical cores) to a new server with four, fifteen-core Xeon E7-4890 v2 processors (with a total of of sixty physical cores). For reasons known only to Intel, the lower core count SKUs in the Xeon E7-48xx v2 product family are not “frequency optimized”, meaning they do not have higher clock speeds than the high-end, E7-4890 v2 processor. The base and turbo clock speeds of the best lower core-count SKUs in the E7- 48xx v2 family actually drop off pretty quickly as the core counts go down. The shared-L3 cache sizes also drop off very quickly, as does the processor price, as you can see in Table 1.

ProcessorPhysical CoresL3 CacheBase ClockTurbo ClockPrice
E7-4890 v21537.5 MB2.8GHz3.4GHz$6,619.00
E7-4860 v21230 MB2.6GHz3.2GHz$3,838.00
E7-4830 v21020 MB2.2GHz2.7GHz$2,059.00
E7-4820 v2816 MB2.0GHz2.5GHz$1,446.00
E7-4809 v2612 MB1.9GHzN/A$1,223.00

Table 1: Selected Intel E7-48xx v2 Processors

 

With the Xeon E4-48xx v2 product family, you are going to want to choose either the E7-4890 v2 or the E7-4860 v2 model processors in most situations, since the lower core count processors are giving up a substantial amount of performance due to their lower clock speeds and smaller L3 cache sizes. If you really want to reduce your core counts to reduce your SQL Server 2012/2014 license costs, you would be better off with the Intel Xeon E5-26xx v2 product family processors that are used in two socket servers. Another alternative is the upcoming Intel Xeon E5-46xx v2 product family processors that are used in four-socket servers.

Either of those choices would be better than one of the lower core count processors in the E7-48xx v2 product family, at least from a pure processor performance perspective.

Intel also has refreshed the E7-88xx v2 product family that is meant for eight-socket and larger servers. For some reason (probably for HPC use), Intel does have “frequency-optimized”, lower core-count models in this product family, as you can see in Table 2.

ProcessorPhysical CoresL3 CacheBase ClockTurbo ClockPrice
E7-8890 v21537.5 MB2.8GHz3.4GHz$6,841.00
E7-8857 v21230 MB3.0GHz3.6GHz$3,838.00
E7-8891 v21037.5 MB3.2GHz3.7GHz$6,841.00
E7-8893 v2637.5 MB3.4GHz3.7GHz$6,841.00

Table 2: Selected Intel E7-88xx v2 Processors

 

I could see some scenarios where you might want to get an eight-socket server with the six-core E7-8893 v2, so that you could have the same physical core count, while having double the memory capacity and much better single-threaded processor performance than a four-socket server with the twelve-core E7-4860 v2. The hardware cost would be significantly higher, since you would be buying eight processors for $6,841.00 each instead of four processors at $3,838.00 each, but for many organizations, that would not be a major issue.

Some server vendors may offer the Xeon E7-88xx v2 processors in their four-socket server models, since they are pin-compatible, which would give us a lot more flexibility as far as processor selection goes. I really wish Intel had “frequency-optimized” models in their Xeon E7-48xx v2 product family, to make this even easier.

A SQL Server Hardware Tidbit a Day – Day 28

For Day 28 of this series, we are going to talk about some factors to consider if you are thinking about building a desktop SQL Server 2012 system for development or testing use. I get lots of questions about this subject, and I have been thinking about it some anyway, hence today’s topic.

In many organizations, old retired rack-mounted servers are repurposed as development and test servers. Sometimes, old retired workstations are used for this purpose. Quite often, these old machines are three to five years old (or even older). For example, you will often find old Dell PowerEdge 1850, PowerEdge 6850, and PowerEdge 1950 servers being used for this purpose. These vintage machines are about four to seven years old, and long out of warranty. Their performance and scalability is quite miserable by today’s standards, even compared to a modern desktop.

For example, a Dell PowerEdge 1850, with two Intel Xeon Irwindale 3.0GHz processors and 8GB of RAM has a 32-bit Geekbench score of about 2250. A Dell PowerEdge 6800 with four Xeon 7140M 3.4GHz processors and 64GB of RAM has a 32-bit Geekbench score of 5023. A newer Dell PowerEdge 1950 with two Intel Xeon 5440 processors and 32GB of RAM will have a 32-bit Geekbench score of about 7500. For comparison, my current main workstation has a 22nm Intel Core i7-3770K processor with 32GB of RAM and a 512GB OCZ Vertex 4 SSD. This system has a 32-bit Geekbench score of 12713.

My argument is that in many situations, given a very limited hardware budget, it may make more sense (for development and testing) to build or buy a new desktop system based on a modern platform rather that using relatively ancient “real” server hardware. Your main limiting factors with a new desktop system will be I/O capacity (throughput and IOPS) and memory capacity, but there are some ways around that..  You should be able to build or buy a very capable test system for less than $1500.00, perhaps far less, depending on how you configure it.

Your two main good choices right now are a 22nm Core i7 Ivy Bridge (using a Core i7-3770 or i7-3770K processor) with an Z77 chipset-based motherboard, or a slightly less expensive Core i5-3570 or i5-3570K processor. The Core i7 will have four cores plus hyper-threading, while the Core i5 will have four cores, but no hyper-threading. Either one of these systems will support up to 32GB of RAM, which is how much you should get (since desktop DDR3 RAM is so affordable).

You need to look at the motherboard features and specifications closely to make sure you get what you need without paying too much for unnecessary features. You want to get a motherboard that has as many SATA ports as possible (preferably newer 6Gbps SATA III ports) with hardware RAID support if possible. At the same time, you don’t really need the premium gaming (such as SLI or Crossfire support) and over-clocking features in a top-of-the line motherboard. The entry level motherboards will usually have fewer SATA ports, which is a good reason to go a little higher in the lineup. You can also buy inexpensive PCI-e SATA III expansion cards to add even more SATA ports. You also want to make sure to get a motherboard with four memory slots, since some entry-level motherboards will only have two slots.

Depending on your motherboard vendor, you might run into driver issues with Windows Server 2012. The problem is not that there are no drivers, but the fact that the motherboard vendors sometimes wrap the actual driver installation programs in their own installation programs that do OS version checking that fails with Windows Server 2012 (since they assume you will be using Windows 7 or Windows 8).

You can buy a large, full tower case, with lots of internal 3.5” drive bays. Then you can buy a number of 1TB Western Digital Black 6Gbps hard drives and/or some consumer grade SSDs, depending on your needs and budget. This will let you have a pretty decent amount of I/O capacity for a relatively low cost. Very fast consumer SSDs are now available for less than $1/GB of space, so you can probably find a way to afford one or more of them for your system.

If you can wait until early to mid June, the 22nm Intel Haswell processors will be available. These will require a new motherboard, but will give you about 5-10% better single-threaded CPU performance at the same clock speed as Ivy Bridge, along with a few other benefits.