Second, AMD has officially released the AMD Opteron 4200 and 6200 series processors (even though they have been shipping to OEMs like Cray since September). Until Microsoft changed their licensing policy for SQL Server 2012 a couple of weeks ago, I was pretty hopeful that the AMD Opteron 6200 (with sixteen physical cores) might be competitive in the SQL Server space, especially for data warehouse workloads. With core-based licensing for SQL Server 2012 Enterprise Edition, that will be a much harder challenge for AMD.  You can check out AMD’s presentation here (PDF warning).

I really think the Sandy Bridge-EP (Xeon E5-2600 series) is going to be a world beater in the two socket space. Keep in mind that there will not be a Sandy Bridge-EX processor for four socket and above systems, so the current Xeon E7-4800 and E7-8800 series (Westmere-EX) will be the best you can get in that form factor, staying around until the Ivy Bridge-EX is released in probably 12-18 months. If you look at the per core performance on the TPC-E OLTP benchmark, the current Xeon X5690 (Westmere-EP) smokes the current Xeon E7-4870 (Westmere-EX) processor. The Sandy Bridge-EP should only increase that performance gap, which will make a two socket system with Sandy Bridge-EP a very compelling choice for SQL Server 2012 Enterprise Edition.

Rather than the old familiar socket-based licensing used in SQL Server 2008 R2 and below, SQL Server 2012 will use a combination of core-based and Server + Client Access License (CAL) based licensing, depending on which Edition you buy (and which choice you make for Standard Edition). With Standard Edition, you can choose core-based licensing or Server + CAL-based licensing. With Business Intelligence Edition, you have to use Server + CAL-based licensing, while Enterprise Edition requires the use of core-based licensing. Standard Edition is the base edition, with a limit of 16 physical processor cores. Microsoft has not announced whether there will be a RAM limit for Standard Edition (like the 64GB RAM limit in SQL Server 2008 R2 Standard Edition). Business Intelligence Edition includes all of the functionality of Standard Edition, plus extra BI features and functionality. Enterprise Edition includes everything in BI Edition, plus all of the extra Enterprise Edition features and functionality. Enterprise Edition is the top of the line edition of SQL Server 2012 that will now get everything that was in SQL Server 2008 R2 Data Center Edition.

If you are using core-based licensing (like you must for Enterprise Edition), each physical socket in your server must use a minimum of four core licenses. That means if you have old hardware that uses dual-core processors, you will still have to buy four core licenses for each socket. That is yet another reason to not use ancient hardware for a new version of SQL Server! Any Intel Xeon processor that only has two physical cores will be at least four-five years old by the time SQL Server 2012 is released, so it really should be retired. Keep in mind that only physical cores count for licensing purposes (on non-virtualized servers), so hyper-threading comes for free.

Core licenses will be sold in two-core packs, again with a minimum of four-cores per physical socket. The full retail license cost per physical core is $6874.00 for SQL Server 2012 Enterprise Edition. This is pretty grim news for AMD, with their higher physical core counts and lower per socket performance compared to Intel.  The table below shows the cost differential in pretty graphic detail.

For most OLTP workloads, you would be far better off from a performance perspective with a two-socket Intel Xeon X5690 server than you would be with a four-socket AMD Opteron 6180SE server. The extremely large license cost difference between those two choices makes Intel an even more compelling choice.

One way to somewhat confirm this assessment is to look at TPC-E scores for different systems and divide them by the total physical core count for the system (not by the thread-count). Looking at the table below, it seems like a two-socket Intel system is a pretty good choice.

Here are the links to the specific TPC-E Submissions that I refer to in the table above:

HP ProLiant DL380 G7 Server

HP ProLiant DL580 G7 Server

HP ProLiant DL585 G7

Of course, a new two socket server will have a lower total RAM limit than a new four socket server. For example, a two socket Xeon X5690 would be limited to 288GB of RAM, which is probably enough for most workloads. A two socket server will also have less total I/O capacity than a new four-socket server because it will have fewer PCI-E expansion slots. Still, you can pretty easily get 5-6GB/sec of sequential throughput out of a modern two socket server, which should be plenty for most workloads. Once the Intel 32nm Sandy Bridge-EP Xeon E5-2690 is released in Q1 of 2012, the wisdom of choosing a two-socket Intel based server will be even more clear.

Since each Xeon E7-4870 processor has 10 physical cores, plus hyper-threading, this gives me a total of 80 logical cores visible to Windows Server 2008 R2. You can see this in Task Manager below. With machines like this, it will be very nice to get the improved Task Manager that will be in Windows 8.

In order to recognize more than 64 logical processors, you must have both Windows Server 2008 R2 and SQL Server 2008 R2 or SQL Server 2012 installed. With either of these combinations, you can have up to 256 logical processors. Microsoft has already announced that Windows 8 will support up to 640 logical processors. Once Windows Server 8 is released, it is extremely likely (although not officially announced) that SQL Server 2012 running on top of Windows Server 8 will also support up to 640 logical cores. Of course, with the new core-based licensing in SQL Server 2012 Enterprise Edition, that would be a pretty expensive proposition.

Looking at CPU-Z, you can see that the Core Speed of Core#0 on Processor #1 is running at 2794.6MHz, which shows that Intel Turbo Boost is enabled and running, since the rated clock speed is only 2.40GHz. In order to get this, you should make sure that you have the Windows Power Plan set to High Performance (instead of the default of Balanced), and that your main BIOS Power Management is set to OS Control. Otherwise, you will have some sort of power management in effect, either from Windows or from the hardware itself. This will cause your processors to throttle back to a slower speed (by reducing the Multiplier) when the processor load is relatively light. It can also cause issues for Fusion-io cards on your PCI-E expansion slots.

When the processors see a spike in load, they will increase the multiplier to increase the clock speed of the cores. The reaction time of this throttle up mechanism is slow enough that it can affect the performance of short-duration OLTP queries. I have seen this both on synthetic benchmarks (like GeekBench) and in the real-world.  The Intel Sandy Bridge processors that I have tested, such as the Core i7-2600K seem to be much less susceptible to this problem.

Depending on what combination of settings you have for your BIOS power management and Windows Power Plan, you may also lose out on the benefit of Turbo Boost, which will “overclock” individual cores based on the overall load, temperature and power usage of each processor. I think power management is a great thing for laptops, desktops, and web servers, but not for mission critical database servers.

Until the recent introduction of the Intel Xeon E7 processor series, Intel had different processor families for different socket count servers. For example the Xeon 3xxx family was for single-socket servers, the Xeon 5xxx family was for two socket servers, and the Xeon 7xxx family was for four socket (or more) servers.

Now, if you want to, you can choose a Xeon E7-2800 series processor for a two socket server, a Xeon E7-4800 series processor for a four socket server, or a Xeon E7-8800 series processor for an eight socket (or more) server.

For an OLTP workload on a two socket server, I would prefer an Intel Xeon X5690 processor over an Intel Xeon E7-2870 processor because of its better single-threaded performance due to its higher clock speed (within the same processor architecture and generation).  For a DSS/DW workload, I would prefer the E7-2870, due to its higher core count and larger L3 cache size.

My philosophy for database server processor selection is to pay the price premium for the absolute best processor available for each socket in your server.  The SQL Server processor license cost is pretty high (even for Standard Edition), so you want to get as much performance and scalability capacity as possible for each processor license that you buy. It is very unlikely that you will ever upgrade to a better processor in an existing database server, so you will be stuck with your choice for the life of the server. If you have “excess” processor capacity, you can consider using it to trade CPU utilization for I/O utilization by using backup compression and data compression (if you have SQL Server 2008 Enterprise Edition).

Unlike a laptop, or web server, I think it is a mistake to buy a processor that is a couple of steps down from the “top-of-the line” for database server usage.

CPU Reviewer Kit for Intel Sandy Bridge

Intel Processor Comparison Tool

How to choose the right processor

Intel Processor Information

CPU-Z info for 45nm Core i7 930

Geekbench results for 45nm Core i7 930 with 12GB of RAM

Geekbench results for 32nm Core i7 2600K with 16GB of RAM

My little Core i7 2600K system just dominates my Core i7 930 system that I use at work on the Geekbench benchmark. That Bloomfield Core i7 is still a very fast system that I am pretty happy with, but Sandy Bridge is significantly better.