Glenn Berry

Recommended Intel Processors for SQL Server 2014 Workloads

Save time monitoring and managing performance in the most challenging data environments.  More
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

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

Paul’s Posts

If you are in the process of evaluating and selecting the components for a brand new database server to run an OLTP or DW workload on SQL Server 2014 Enterprise Edition, you have several initial choices that you have to make as a part of the decision process.

First, you need to decide on the server socket count, which means choosing a two-socket, four-socket, or eight-socket server (at least in the commodity server market). After you choose the socket count, you need to decide exactly which of the available processors you want to use in that model server. Looking at the choices for most current model servers from the major system vendors, you will discover that you will have to pick from around 15-20 different specific processors. All of this can be a little overwhelming to consider, but I urge you to do some research, and to choose carefully.

Letting someone else pick your processors, who may not be familiar with SQL Server 2014 licensing and the demands of different database workload types, could be a lasting, costly mistake. A very common error that I see is someone picking a lower clock speed processor at a particular physical core count, from the same processor family and generation, in order to save a relatively small amount of money on the hardware costs. Doing this might cause you to give up a significant amount of performance (20-30%) in order to save a small fraction of the entire cost, including SQL Server 2014 licenses of the database server.

With the core-based licensing in SQL Server 2014 Enterprise Edition, you need to pay very close attention to your physical core counts, and think about whether you are more concerned with extra scalability (from having more physical cores), or whether you want the absolute best single-threaded CPU performance (from having a processor with fewer cores but a higher base clock speed from the same processor generation). Unlike in the good old days of SQL Server 2008 R2 and older, having more physical cores will cost you more for your SQL Server 2014 Enterprise Edition licensing costs. You really need to think about what you are trying to accomplish with your database hardware. For example, if you can partition your workload between multiple servers, then you could see much better overall OLTP performance from using two dual-socket servers instead of one quad-socket server. With a data warehouse workload, it may be much more difficult to partition your workload across multiple database servers.

So, here are the specific Intel processors that I recommend in mid-August 2014 for OLTP and DW workloads, with their high-level specifications and some commentary.

Two-Socket Server (High Capacity OLTP or DW)

Intel Xeon E5-2697 v2 (22nm Ivy Bridge-EP)

  • 2.7 GHz base clock speed, 30MB L3 cache, 8 GT/s Intel QPI 1.1
  • 12 cores, Turbo Boost 2.0 (3.5 GHz), hyper-threading
  • Four memory channels, twelve memory slots per processor, 384GB RAM with 16GB DIMMs

Two-Socket Server (High Performance OLTP)

Intel Xeon E5-2643 v2 (22nm Ivy Bridge-EP)

  • 3.5 GHz base clock speed, 25MB L3 cache, 8 GT/s Intel QPI 1.1
  • 6 cores, Turbo Boost 2.0 (3.8 GHz), hyper-threading
  • Four memory channels, twelve memory slots per processor, 384GB RAM with 16GB DIMMs

Having twelve physical cores per processor will let you run more concurrent queries OLTP queries or more effectively run a typical DW workload. Choosing the top of the line, 12-core Xeon E5-2697 v2 would cost twice as much for the SQL Server 2014 license costs as the 6-core Xeon E5-2643 v2. Once again, if you can partition your workload, two dual-socket Xeon E5-2643 v2 based servers would give you better overall OLTP performance than one Xeon E5-2697 v2 based server for the same SQL Server 2014 Enterprise Edition licensing cost. You would have more total memory between the two servers, and more potential I/O capacity, at the cost of buying two servers instead of one server. In some situations, this strategy might not make sense, especially with the added management and maintenance overhead of two servers instead of one.

Four-Socket Server (High Capacity OLTP or DW)

Intel Xeon E7-4890 v2 (22nm Ivy Bridge-EX)

  • 2.8 GHz base clock speed, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 15 cores, Turbo Boost 2.0 (3.4 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 1536GB RAM with 16GB DIMMs

Four-Socket Server (High Performance OLTP)

Intel Xeon E7-8893 v2 (22nm Ivy Bridge-EX)

  • 3.4 GHz base clock speed, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 6 cores, Turbo Boost 2.0 (3.7 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 1536GB RAM with 16GB DIMMs

The new Xeon E7-8893 v2 will give you significantly better single-threaded OLTP query performance in a four-socket server than the E7-4890 v2, at the cost of less total capacity because of the lower physical core count. The E7-8893 v2 is a "frequency-optimized" model that is actually designed for eight-socket servers, but is available in several new four-socket server models from the major server vendors. It is an excellent choice for a smaller OLTP workload, where you want the best possible single-threaded CPU performance, but want to minimize your SQL Server 2014 licensing costs.

It would save you enough on SQL Server 2014 Enterprise Edition license costs (about $250K) to buy the server itself and still have lots of money left over. I even think it is a better choice in many situations than a two-socket server with the 12-core, Intel Xeon E5-2697 v2, since you will have much higher single-threaded performance and much higher memory capacity. The downside is a higher hardware cost, since you will be buying four, quite expensive processors.

The higher core count Xeon E7-4890 v2 would be a better choice for a larger OLTP workload or for a DW workload. You will have more processor cores, which give you more total CPU capacity, which will cost you a significant amount extra for the SQL Server 2014 license costs.

Eight-Socket Server (High Capacity OLTP or DW)

Intel Xeon E7-8890 v2 (22nm Ivy Bridge-EX)

  • 2.8 GHz base clock speed, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 15 cores, Turbo Boost 2.0 (3.4 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 3072GB RAM with 16GB DIMMs (eight sockets)

Eight-Socket Server (High Performance OLTP)

Intel Xeon E7-8891 v2 (22nm Ivy Bridge-EX)

  • 3.2 GHz base clock speed, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 10 cores, Turbo Boost 2.0 (3.7 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 3072GB RAM with 16GB DIMMs (eight sockets)

In the eight-socket space, you can also choose a lower core count, frequency-optimized model (like the E7-8891 v2) that has a higher clock speed for better single-threaded OLTP performance. The lower core count will also save you a LOT of money on SQL Server 2014 licensing costs, although you will give up that extra load capacity with fewer total processor cores available. For a larger OLTP workload or for a DW workload, the 15-core E7-8890 v2 would be a better choice, since you will have a lot more overall CPU capacity, along with extra SQL Server 2014 licensing costs.

All of these recommended processors are from the same current-generation, 22nm Intel Xeon Ivy Bridge family, so you can make pretty easy performance comparisons based on core-counts, base and turbo clock speeds, and the size of the L3 cache. All of these processors will also support 32GB DIMMs (which are still more expensive per GB than 16GB DIMMs), and the upcoming 64GB DIMMs (which will be quite expensive per GB).

OLTP workloads are especially sensitive to single-threaded CPU performance, since most OLTP queries are short duration queries that usually run on a single processor core. Having more total cores is important for DW performance, and allows you to run higher concurrent query volumes, assuming you are not seeing memory or storage subsystem bottlenecks.