On October 12, 2013, Frank Delattre released CPU-Z 1.67, which has some support for some new processors and a new validation tool. Even though CPU-Z was originally developed for the hardware enthusiast over-clocking community, it actually is very valuable for server administrators and for database professionals. First, it lets you identify your exact processor model, with all of its specifications and instruction set support. Second, it helps confirm whether you have any form of power management in effect that is reducing the current speed of the processor cores on your database server.
This can be caused by your Windows Power Plan in Windows Server 2008 or newer. By default, the Power Plan is set to Balanced instead of High Performance like we want for a database server. This is easy to change, and goes into effect immediately without rebooting the server. After you make this change in Windows, you should use CPU-Z to see what your processor core speed is. If it is still throttled back, you are going to have to change your BIOS power management setting.
This is necessary because the Windows Power Plan can be overridden by hardware-level power management settings that are set in your main BIOS setup. You typically want to set your hardware-level power management to OS Control or to Max Performance in the BIOS. This will let your processors take advantage of Intel Turbo Boost or AMD Turbo Core, and it will let them run at their full base clock speed or above at all times. This is quite important for OLTP query performance, but have also seen it affect ETL performance. Many storage vendors, such as Fusion-io and LSI also recommend that you make these changes to avoid having the PCI-E expansion slots affected by power management.
Figure 1 shows my desktop processor running at full speed with Turbo Boost (see the Core Speed indicator at the bottom left quarter of the form), while Figure 2 shows the processor being throttled back due to power management.
Figure 1: Processor Core Speed without any Power Management
Figure 2: Processor Core Speed with Power Management in Effect
I have seen OLTP query performance improve by as much as 25% by making these simple changes. The level of improvement you will see depends on the exact processors you are using (newer processors like Haswell, Ivy Bridge, and Sandy Bridge handle power management better than older processors, such as Nehalem and Westmere), and whether or not you have other big performance bottlenecks.