For Day 27 of this series, I am going to talk about Power Management and its effect on processor performance. I have written about this subject a couple of times before, here and here. Other people, such as Paul Randal (blog|Twitter) and Brent Ozar (blog|Twitter) have written about this subject here and here.
Power Management is when the clock speed of your processors is reduced (usually by changing the processor multiplier value) in order to use less electrical power when the processor is not under a heavy load. On the surface, this seems like a good idea, since electrical power costs can be pretty significant in a data center. Throttling back a processor can save some electricity and reduce your heat output, which can reduce your cooling costs in a data center. Unfortunately, with some processors, and with some types of SQL Server workloads (particularly OLTP workloads), you will pay a pretty significant performance price (in the range of 20-25%) for those electrical power savings.
When a processor has power management features that are enabled, the clock speed of the processor will vary based on the load the processor is experiencing. You can watch this in near real-time with a tool like CPU-Z, that displays the current clock speed of Core 0. The performance problem comes from the fact that some processors don’t seem to react fast enough to an increase in load to give their full performance potential, particularly for very short OLTP queries that often execute in a few milliseconds.
This problem seems to show up especially with Intel Xeon 5500, 7500 (Nehalem-EP and EX), Intel Xeon 5600, E7 series processors (Westmere-EP and EX families) and with the AMD Opteron 6100, 6200, and 6300 series (Magny Cours, Bulldozer and Piledriver families). Much older processors don’t have any power management features, and some slightly older processors (such as the Intel Xeon 5300 and 5400 series) seem to handle power management slightly better. I have also noticed that the Intel Sandy Bridge-EP processors seem to handle power management a little better than the Nehalem and Westmere did, i.e. they don’t show as noticeable of a performance decrease when power management is enabled.
Basically, you have two types of power management that you need to be aware of as a database professional. The first type is hardware-based power management, where the main system BIOS of a server is set to allow the processors to manage their own power states, based on the load they are seeing from the operating system. The second type is software-based power management, where the operating system (with Windows Server 2008 and above) is in charge of power management using one of the standard Windows Power Plans, or a customized version of one of those plans. When you install Windows Server 2008 or above, Windows will be using the Balanced Power Plan by default. When you are using the Balanced Power Plan, Intel processors that have Turbo Boost Technology will not use Turbo Boost (meaning that they will not temporarily overclock individual processor cores for more performance).
So, after all of this, what do I recommend you do for your database server? First, check your Windows Power Plan setting, and make sure you are using the High Performance Power Plan. This can be changed dynamically without a restart. Next, run CPU-Z, and make sure your processor is running at or above its rated speed. If it is running at less than its rated speed with the High Performance Power Plan, that means that you have hardware power management overriding what Windows has asked for. That means you are going to have to restart your server (in your next maintenance window) and go into your BIOS settings and either disable power management or set it to OS control (which I prefer).
