I recently had the opportunity to specify the purchase of a couple of Dell PowerEdge R810 2U servers that will be used as database servers for SQL Server 2008 R2. These two servers have four Intel Xeon E7-4870 (32nm Westmere-EX) processors and 256GB of RAM each.  This model server can hold up to 512GB of RAM using relatively economical 16GB DIMMs, or up to 1TB of RAM with the still outrageously expensive 32GB DIMMs. That is quite a bit of power in a 2U server.

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.

Filed under: Computer Hardware, Processors, SQL Server 2008 R2, SQL Server 2012 Tagged: CPU, Westmere-EX