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.
Great article. Thanks for putting it together!
Glad you liked it.
Hi Glenn
Good article, however the choice of CPU also affects long term power consumption costs which I'd be interested in hearing your thoughts on. I agree that Intel provides better raw power than AMD per thread & the cost implications under SQL 2012's licensing model magnify the difference in per core performance between AMD & Intel.
However, over the 3 > 4 years lifecycle (sometimes more) of a SQL Server solution, the total power consumption costs are also an important factor in the overall costs. These costs are usually buried in the overall data centre costs but power costs are growing faster than the cost of SQL Server licensing in many parts of the world so this factor is also growing in importance.
Have you attempted any comparison of the total cost / performance difference between AMD & Intel?
Regards,
Greg Linwood
I agree that power consumption and power costs can be a significant long-term expense. From what testing I have seen, the Intel Xeon E5 Family has much better performance/watt than the AMD Opteron 6200 Family. The AMD Opteron 6300 Family is a little better than the 6200 Family in performance/watt, but still not as good as Intel.
http://www.anandtech.com/show/5553/the-xeon-e52600-dual-sandybridge-for-servers/7
Another factor to consider is that a higher performance processor can complete its workload more quickly and be able to reduce its power consumption more often (depending on your power management settings), so that will often have a major effect on overall power consumption. AnandTech has done some testing to confirm this.
Crystal-clear NUMA effect on the WSM-EX (aka E7) kit. We see 78, 72, 58 TPC-E/core at 2,4 and 8 sockets. With 2 and 4 sockets there is point-to-point memory connectivity since WSM-EX has 3 QPI links. Jumping to 8 requires glue (ala IBM eXA or HP PRIMA) else one ends up with 2-hop memory as is the case with the Sun x4800 (aka Sun Server X2-8). What brand server was that E7-8870 TPC-E result obtained on?
As for Sandy Bridge and per-core licensing I actually would rather see folks consider the E5-2643 which is *exaclty* an E5-2690 chopped in half so it is 4c/8t. If someone is replatforming from, say, Harpertown Xeon (also 4c but no SMT) they can easily expect 4.5-5x performance improvement when jumping to E5-2643–if SPEC is any indicator (and I find it usually is). So folks, do yourself a favor and at least test a 2S server fitted with E5-2643. I know HP Proliant and Cisco UCS (rack-mounted) are offering this Intel SKU as an option.
Nice article, Glenn
kevinclosson.wordpress.com
It's a pity that so few TPC-E results are reporting Watts/tpsE as this would be more relevant to SQL and save us from having to interpret what more generic tests such as AnandTech mean with a SQL workload which usually has heavy I/O characteristics &CPU idle time.
The few TPC-E results which have been published with Watts/tpsE do show a huge difference in power consumption between systems with HDD vs SSD storage but there are far too few to derive anything meaningful as far as CPU utilisation is concerned:
http://www.tpc.org/tpce/results/tpce_energy_results.asp
I obtained the TPC-E benchmark kit from Microsoft a few years ago & ran it in our lab but found the workload characteristics to be very CPU compute intensive & far less I/O intensive compared with TPC-C so the difference in power consumption between the systems with / without SSDs is curious to me.
I suspect that much of it comes down to CPU idle time rather than power differences between the two types of storage, along the lines of your last comment – that the CPUs get far more idle time when running on SSDs vs HDDs, yielding far less spinlock time & context switching.
It's all very interesting & as electricity is getting more expensive, trading off a little CPU performance for the long term power savings (which can cost bucket loads over a few years) is becomming more attractive. Naturally you always need enough raw CPU power to get the job done but trying to avoid power wastage isn't an easy assessment in CPU selection.
Regards,
Greg Linwood
Thanks, Kevin! The E7-8870 TPC-E result was an NEC Express5800/A1080a-E. I have recommended the E5-2643 to people to save on SQL Server 2012 licensing costs quite a few times in the past. It actually has a higher base clock speed (3.3GHz) than the E5-2690 (2.9GHz), although it has a lower Turbo Boost speed (3.5GHz for the E5-2643) vs. (3.8GHz for the E5-2690).
Either one of those can easily replace a four-socket server from 3-4 years ago in most cases.
I am all for using more efficient components where possible, both for environmental reasons and for economic reasons. Despite that, I think mission critical database servers (which are usually relatively few in number at most organizations) are not the best place to make extreme power efficiency a higher priority over CPU performance. The current generation of servers from all of the major vendors is much more efficient than servers from a few years ago. Most 1U and 2U servers with Xeon E5 processors use between 200-400 watts, depending on how they are configured and how much load they are under. In the United States, electricity currently costs between 10-15 cents per kWh in most areas.
If we go with 400 watts, running 24 hours/day, that is 9.6 kWh per day. At 15 cents per kWh, that is $1.44 per day for the electricity. If you keep this 2U database server for three years, that would only be $1576.80 in electricity for the lifetime of the server. That is relatively insignificant compared to the original hardware cost ($10,000.00), the SQL Server 2012 core-licensing costs for 16-cores ($109,984.00), and the partial labor cost of your staff over three years.
The extra CPU performance you get from choosing the correct processors and disabling power management can be used for things like backup compression and data compression, which may let you use a much smaller number of spindles and storage space for your overall workload, which would also translate into power savings. Doing things like retiring older, less efficient servers and consolidating or virtualizing onto fewer, new database servers is a better strategy to reduce your electrical consumption.
It is a different story if you have hundreds or thousands of web servers running open-source software. That would be a better example of where power costs are a larger percentage of your overall costs.
Are the electricity costs you've quoted retail (home) rates or data centre rates? Data centre rates are usually higher than retail rates as DC rates usually indirectly reflect aircon on top of the actual per-server / rack draw and other factors such as redundancy infrastructure.
In Australia, rack power is more like AUS$500 per kw/month in a quality DC and a rack full of busy servers will typically be in the vicinity of 10 kw, though less in racks with mixed servers (SQL + other systems).
Power draw on our own servers vary from $100 > $500 per month depending on the size of the server, how much internal storage, how many CPUs etc. The life power cost of our own servers over 4 years runs into 5 figure sums.
The power cost trend is also increasing & likely to increase even faster in future, particularly in countries like Australia that are heavily taxing excessive power consumption. I'm not sure how power cost trends look in the US, the picture could be very different of course.
I'm also not suggesting that servers should be given any less power than is necessary, just that power shouldn't be wasted for both cost & environmental reasons & this is a factor in choosing a CPU which might tip things more toward AMD. We use both but I haven't done any careful comparisons, hence I was interested in whether you might have done such a comparison
Hi Glenn,
Actually, both the 2690 and 2643 run at 3.4GHz when only 4 cores are active. The 2643 is really a 2690 chopped in half (with on-die logic). The 2690 core frequency drops to 3.3GHz once 6 cores are active. The 2643 drops to 3.4 once 3 cores are active. The difference in these core frequencies matter little for load/store workloads like databases… the amped up frequencies don't help stalls :-) Besides, that's only 3% difference anyway. The bigger point–that you make well–is the fact that these SQL Server customers should choose their SKUs when re-platforming from vintage 4yr old systems… On a per core basis E5 top-bin SKUs are about 4-5x more powerful (e.g., Xeon 5400 compared to E5-2643).
Thanks Glenn for putting together a great article!! I have read your book 'SQL Server hardware' and find it a great resource for everybody. Just wanted to know when will you be refreshing it with new and latest information.
Great article, now all I have to do is to get the company to buy the right servers and not virtualize them. Thanks for the link.
I see comments are a bit stale here, and in a causal search, this is surprisingly the only spot I turned up any real discussion on this topic. You would think it would merit a little more for those in situations that aren't so clear-cut as to where to allocate the db dollars. With the price of SQL on the core licensing model, and the proliferation of CPU configurations, there are a lot of trade-offs available. I really like to optimize things, so maybe it's just me, but any SMB (like us) will benefit from the right blend.
We've been running a 200GB transactional database on SQL 2005 Std for about 3-1/2 years. We now run a mirror pair of HP G6 servers, each with a single X5550 and 36GB. I've got 20x 15K spindles on one data file, and performance is really fine for us, excepting the bad query or relation that creeps in from time to time. CPU load is typically 20% up to 30% peaks.
We recently decided to bite the bullet and upgrade to (2012) Enterprise Ed. Our goal is continuous availability, so we expect to help meet that with the online index rebuilds, table partitioning, and some of the Always-On features – and more memory. (Right now index rebuilds are pretty much ignored, making us more susceptible to blocking from a dirty query. And, while we can probably do the table partitioning by other means, we expect it to be worthwhile in terms of app maintenance and just plain time to use the Enterprise feature.)
While we're at it, we decided it would be prudent to refresh the hardware. We're not going > 4 cores – there's clearly no need, and the pricey software addition makes that even easier. Besides just having new hardware, the prime benefit there is more memory (another prime benefit of Enterprise). Not having really looked at hardware in awhile, I was initially disappointed that most all the CPUs of any clock rate are 6-core+. Now I believe that you can disable cores in the HP bios (but I haven't confirmed), and then also noticed the E5-2643 chip that would seem perfect for us. We don't usually do CTO, but that would seem to be a great fit. 192GB on one CPU in an HP Gen8 is pretty cheap (~ $2K). I don't want to cover up bad database development or admin with hardware, but performance headroom at that price is a no-brainer. I even notice a 2-core CPU on the Gen8 (E5-2637) that would really let me go nuts with the RAM. So, I'm even wondering if there might be a good trade-off there between nearly 400GB of RAM, and fewer HDD spindles.
Perhaps you could say we're not performance constrained, but are more people constrained. If a few thousand extra at refresh keeps the database humming, and saves us time, it seems well worth it. This might not be a great case study for your post, but thought I'd share it. I'm always open to comments in return. Thanks!
Hi Gary,
The article is several months old, but I am glad you found it. The Intel Xeon E5-2643 is an excellent choice to minimize your SQL Server 2012 core-license costs. A dual-core, Intel Xeon E5-2637 would actually be a terrible choice, since you are forced to buy a minimum of four core licenses per physical processor. You also won't be able to use more than 192GB of RAM if you only have one physical processor in a two-socket server like a DL380p Gen 8. Having 192GB should be sufficient compared to the 36GB you have now.
Thanks for your reply, Glenn. I was just reviewing the SQL licensing this morning, and saw the same thing you mentioned – that the license requires a minimum of 4 cores per physical CPU. Interestingly, though, it looks like running SQL in a VM only requires a minimum of 4 core licenses, so I could deploy on a dual 2643 box (or any 2P server), and have access to the larger RAM capacity.
Maybe it's not typically done, but when I've got a smaller database of fairly consistent size, and I can provision 256GB of RAM for about the price of a dozen 15K drives, then that's a trade-off that would seem to make eminent sense. With 768GB headroom in the Gen8 (when 32GB DIMMs become affordable), this would be the last machine we'd ever need ;-)
The only thing to disrupt that perfect picture is running SQL on a hypervisor. I'm generally not a fan of complicating things that way on line-of-business platforms. Maybe I'm just old school. Or just old.
Thanks again for your reply, Glenn, and for helping sort out the options in some sensible way.
Why not a AMD cpu?
Because you will pay significantly more in SQL Server 2012 licensing costs and get significantly lower single-threaded performance with modern AMD processors compared to modern Intel processors. That does not seem like a good idea, and it may be difficult to justify from a business perspective.
Thank you, Sir.
Glenn's got a whole paragraph on that, no?
Also see:
http://sportstoday.us/technology/sql-server-2012—demystifying-hardware—processor-vendor-selection.aspx
Notably:
"AMD processors have higher physical core counts in their processors compared to Intel, and they provide lower performance per physical core. This forces you to pay for more SQL Server 2012 core licenses but get lower single-threaded performance, which is not a very good combination."
And here:
http://www.myotherpcisacloud.com/post/2013/04/19/Processor-Shopping-for-SQL-Server-2012.aspx
Notably:
"AMD cores tend to perform much worse per core than Intel cores on an OLTP workload, and that crazy numbers of processor cores present with diminishing returns regardless of the manufacturer. So far things are not looking good for AMD. AMD can pack more cores on a die, but that just simply does not make up for their gap in single-threaded performance."
Those are just a couple of quotes I pulled out; please read the whole articles (and search for many others most of which come to similar conclusions).
Hi
Wondering if this cracking blog has any more modern CPU analysis? There's quite a few more out there now.
On the other hand, the main thrust of the argument hasn't changed.
I'd also like to highlight the VM issue – in that the 2014 licensing (maybe the others?) view Virtual Cores as cores – whether they are hyper thread 'cores' or not…
One can easily imagine a future version (quite iminent now) updating the licensing again to incorporate hyper thread cores as licensable entities – which if you are on a renewed SA contract you will roll into blindly.
Hello great acticule, one question do you think that i will get same performance with E5-2620?, i see that these procesor have a smal cache, and i would like to know if the cache of the procesor impact in SQL performance.
Thanks
Simon,
The E5-2620 is quite old now, and it was not a very good choice when it was new. It has a low base clock speed, small L3 cache, and low QPI speed.
http://ark.intel.com/products/64594/Intel-Xeon-Processor-E5-2620-15M-Cache-2_00-GHz-7_20-GTs-Intel-QPI