Best Quad Core Processors for SQL Server 2014 Enterprise

SQL Server 2014 Enterprise Licensing Efficiency on Four Cores

Microsoft Core Licensing

Microsoft pricing per core is $13,748 (your price may vary depending on your licensing program) per set of two cores, with a four core minimum per processor (a dual core counts as a four core so it doesn’t make sense to go with less than 2 cores) so you’re looking at $27,500 in licensing costs.  It doesn’t matter how fast those cores are, you’re paying the same price if you run this on a Xeon E3-1220L v3 as you would on the most expensive processor money can buy… at a licensing cost of $6900/core you want to make every core count.

Quad Core Options

Most processors can quickly be eliminated.  What we’re looking for is support for as much memory as possible, a large L2 and L3 cache (see: Microsoft SQL 2012 Management and Administration by Ross Mistry pp. 685), and since licensing is core bound we want to get as high a frequency as possible to minimize the number of cores.  Xeon E7s don’t come with a quad core option.  The Xeon E3 has a memory limit of 32GB and also has a rather small 8MB L2 cache and no L3 cache so it won’t be the best option for most scenarios.  This leaves the Xeon E5 series…

Xeon E5 Processor Options

  • Xeon E5-4603 v2 (10M Cache, 2.20 GHz): $551.00 — too slow
  • Xeon E5-2407 v2 (10M Cache, 2.40 GHz): $250.00 — too slow
  • Xeon E5-2403 v2 (10M Cache, 1.80 GHz): $192.00 — too slow
  • Xeon E5-1620 v2 (10M Cache, 3.70 GHz): $294,00 — low cache and memory
  • Xeon E5-2609 v2 (10M Cache, 2.50 GHz): $294.00 — too slow
  • Xeon E5-2637 v2 (15M Cache, 3.50 GHz): $996,00 – winner
  • Xeon E5-2603 v2 (10M Cache, 1.80 GHz): $202.00 — too slow

It’s very easy to eliminate all but two… if you’re wondering why we don’t even consider the slower processors consider the E5-2637 v2 and compare it to the next fastest processor (E5-2609) and notice it’s 30% slower.  Sure you might save $800 on the CPU but you’re wasting 10 times that amount in the value of your licensing!   Microsoft isn’t going to give you a discount on a slower core so you may as well get the most performance you can get out of it.

After eliminating the slowest processors the two choices left are:

  • E5-1620 v2 – no SMP support, max memory 256GB, runs at a slightly higher clock.
  • E5-2637 v2 – Dual Processor option, larger 15MB cache, max memory 384GB,

Most Performance per Dollar: Xeon E5-2637 v2

Sandy-Bridge_EP_1Even though the E5-1620 v2 is clocked faster, that’s more than offset by the larger cache. This is especially the case with hyper-threading.  The extra 128GB of memory (make sure you’re motherboard has 12-dimm slots per CPU to get to 384GB) capacity is going to stretch licensing a lot further.

Also, having the dual processor option gives you flexibility to drop in another CPU on the motherboard in the future without having to acquire another server.  This is nice insurance against unexpected growth.

So, of the available quad core processors options the E5-2637 v2 is the most efficient processor from a performance per dollar and capacity per dollar for SQL Server Enterprise.

If you need more than four cores take a look at Glenn Berry’s articles: Selecting a Processor for SQL Server 2014 – Part 1 and Selecting a Processor for SQL Server 2014 – Part 2.

 

SHA256 Hash Stored Procedure for SQL Server 2008

The other day I was writing an ETL where I expected duplicate data to be accidentally sent over quite often so I needed an SHA-256 function to guarantee that a set of incoming rows were unique (notice I said the set of rows, not individual rows so that rules out using an index to prevent duplicates).  SQL Server 2012 has it but for this project I was stuck on SQL Server 2008 R2 which only has an SHA-1 function (obviously not good enough).

So here’s an SHA-256 stored procedure I wrote for SQL Server 2008…