If you are in the process of evaluating and selecting the components for a new database server to run an OLTP workload on SQL Server 2012 Enterprise Edition, you have several initial choices that you have to make as a part of the decision process. First you have to decide whether you want to go with an AMD-based server or an Intel-based server. Next, you need to decide on the server socket count, which means choosing a single socket, dual socket, quad 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 several 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, who may not be familiar with SQL Server 2012 licensing and the demands of different workload types, could be a lasting, costly mistake.
With the new core based licensing in SQL Server 2012 Enterprise Edition, you need to pay closer attention to your physical core counts, and whether you are more concerned with extra scalability (from having more physical cores), or whether you want the absolute best OLTP query performance (from having a processor with fewer cores but a higher base clock speed from the same generation). Unlike in the past, having more physical cores will cost you more for your SQL Server 2012 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 OLTP performance from using two dual-socket servers instead of one quad-socket server.
So, here are the Intel processors that I recommend in late May 2012 for OLTP workloads, with their high level specifications and some commentary.
Single Socket Server (High Capacity)
Intel Xeon E5-2470 (32nm Sandy Bridge-EN)
- 2.3 GHz, 20MB L3 cache, 8 GT/s Intel QPI 1.1
- 8 cores, Turbo Boost 2.0 (3.1 GHz), hyper-threading
- Three memory channels, six memory slots per processor, 96GB RAM with 16GB DIMMs
Single Socket Server (High Performance)
Intel Xeon E3-1290 V2 (22nm Ivy Bridge)
- 3.7 GHz, 8MB L3 cache, 5 GT/s Intel QPI 1.1
- 4 cores, Turbo Boost 2.0 (4.1 GHz), hyper-threading
- Two memory channels, four memory slots per processor, 32GB RAM with 8GB DIMMs
At least one Tier One vendor (Dell) is offering a single socket server with the new Sandy Bridge-EN processor family. This is the entry level, dual-socket capable Sandy Bridge processor that has lower clock speeds and less memory bandwidth than the Sandy Bridge-EP processor family. Despite this, it does give you the ability to have eight physical cores and 96GB of RAM in a single socket server. You would see much better single-threaded OLTP performance from a new 2nd generation E3-1290 V2 Ivy Bridge processor, but you would be limited to four physical cores and 32GB of RAM. Again, if you can partition your workload, two single socket Xeon E3-1290 V2 based servers would give you much better OLTP performance than one Xeon E5-2470 based server for the same SQL Server 2012 Enterprise Edition licensing cost.
Dual Socket Server (High Capacity)
Intel Xeon E5-2690 (32nm Sandy Bridge-EP)
- 2.9 GHz, 20MB L3 cache, 8 GT/s Intel QPI 1.1
- 8 cores, Turbo Boost 2.0 (3.8 GHz), hyper-threading
- Four memory channels, twelve memory slots per processor, 384GB RAM with 16GB DIMMs
Dual Socket Server (High Performance)
Intel Xeon E5-2643 (32nm Sandy Bridge-EP)
- 3.3 GHz, 10MB L3 cache, 8 GT/s Intel QPI 1.1
- 4 cores, Turbo Boost 2.0 (3.5 GHz), hyper-threading
- Four memory channels, twelve memory slots per processor, 384GB RAM with 16GB DIMMs
Choosing the popular Xeon E5-2690 would cost twice as much for the SQL Server license costs as the quad-core Xeon E5-2643. Once again, if you can partition your workload, two dual socket Xeon E5-2643 based servers would give you better overall OLTP performance than one Xeon E5-2690 based server for the same SQL Server 2012 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. This might not make sense, especially with the added management and maintenance overhead.
Quad Socket Server (High Capacity)
Intel Xeon E7-4870 (32nm Westmere-EX)
- 2.4 GHz, 30MB L3 cache, 6.4 GT/s Intel QPI 1.0
- 10 cores, Turbo Boost (2.8 GHz), hyper-threading
- Four memory channels, sixteen memory slots per processor, 1024GB RAM with 16GB DIMMs
Quad Socket Server (High Performance)
Intel Xeon E5-4650 (32nm Sandy Bridge-EP)
- 2.7 GHz, 20MB L3 cache, 8 GT/s Intel QPI 1.1
- 8 cores, Turbo Boost 2.0 (3.3 GHz), hyper-threading
- Four memory channels, twelve memory slots per processor, 768GB RAM with 16GB DIMMs
The brand new Xeon E5-4650 will give you significantly better single-threaded OLTP query performance in a quad-socket server than the older E7-4870, at the cost of less total capacity because of the lower core count, smaller L3 cache, and lower RAM capacity. It would save you enough on SQL Server 2012 Enterprise Edition license costs to buy the server itself and still have money left over. Still, as my recent analysis of TPC-E scores by physical core showed, you would get the best possible OLTP performance from a two socket Xeon E5-2690 based system if your workload will fit on a two socket server. I think a high percentage of SQL Server 2012 workloads will run very well on a modern two socket server. You really do take a performance hit when you go from a two-socket server to a four-socket server.
Eight or More Socket Server
Intel Xeon E7-8870 (32nm Westmere-EX)
- 2.4 GHz, 30MB L3 cache, 6.4 GT/s Intel QPI 1.0
- 10 cores, Turbo Boost (2.8 GHz), hyper-threading
- Four memory channels, sixteen memory slots per processor, 2048GB RAM with 16GB DIMMs (eight sockets)
You really only have one choice when you go above a four socket server. Unfortunately, even with Non-Uniform Memory Access (NUMA), you will not see 1:1 scaling as you double your socket counts in a server. The situation is much better than it used to be with the old Symmetrical Multi-Processing (SMP) architecture, but looking at recent TPC-E scores show that the eight-socket Xeon E7-8800 family takes quite a performance hit per physical core compared the four-socket Xeon E7-4800 family. You really want to run your workload on a greater number of lower socket count servers if you can.
I always like to hear what you think about my posts, so be sure to let me know!
8 thoughts on “Recommended Intel Processors For SQL Server 2012 OLTP Workloads”
Thanks Glenn. Very helpful.
Hi Glenn,
Mighty interesting reading, but i have one control question: What is you definition of High capacity versus High performance.
Should i interpret High capacity as many users or throughput in combing through large datasets and High performance as computing capacity?
Theo,
High performance generally means single-threaded processor performance (which is very important for OLTP workloads). High capacity generally means the number of concurrent users or queries that the system can support, whether it is lots of single-threaded queries or lots of parallel DW-type queries.
Hi Glenn,
great post
in DW/OLAP queries ,do you recommend Enable/Disable Hyper-Threading?
thanks in advance
Ideally, you would test with and without HT using your workload. Barring that, I advise people to enable HT by default, even with DW/OLAP workloads.
Thank you Glenn