This is a subject I have written about a couple of times before, here and in much more depth here.  Much more recently, fellow SQL Server MVP Brent Ozar (blog| twitter) has also blogged about this subject.  I thought I would show you how to use CPU-Z to easily determine whether you are being negatively affected by power management settings in your main BIOS or in your Windows Power Plan. Fixing this problem is very easy, although it might require a server reboot (depending on your BIOS setting), and it can make you look like a super hero to your management!

CPU-Z is a freeware system profiler that lets you identify a wealth of interesting and important information about any machine that is running on any recent version of Windows. It is available for download here. This tool is very popular with hardware enthusiasts, who like to use it to document how much they have been able to over-clock their processor, which is useful for geek bragging rights. You can use it for a more mundane but useful purpose, i.e. finding out some of the low level hardware details about your database server. I always use the no install version, which is just an executable file inside of a zip file. Running this executable analyzes the system in a few seconds, answering several important questions that you may have about the machine, such as whether the hardware is 64-bit capable, the size of the installed memory sticks (is it four 1GB sticks or two 2GB sticks), and the exact model number of the processor(s).

The CPU tab gives you a very high level of detail about the CPU(s) in a machine, including the model number, codename, manufacturing technology, clock speed, supported instruction sets and cache types and sizes. It also shows the number of cores and number of threads (which equates to logical CPUs visible to the operating system). It also shows you the rated speed and current speed of the processor(s) in your machine, which is the tipoff that you are suffering from power management.

Figure 1: Example of CPU tab in CPU-Z

The Caches tab gives you more information about the various CPU caches, including the L1 Data Cache, L1 Instruction Cache, L2 Cache, and L3 Cache (if the processor has one). Remember, SQL Server is very dependent on L2 and L3 cache size for performance.

Figure 2: Example of Caches tab in CPU-Z

The Mainboard tab reveals information about the manufacturer, model number, chipset and main BIOS version of the server motherboard. This helps you identify the chipset used by the server motherboard and what version the main BIOS firmware is currently on. It is generally a good idea to keep your main BIOS firmware up to date to prevent stability problems with your server hardware. It will also save time if you ever need to call your hardware vendor for support, since they will want you to upgrade to a current version of the main BIOS firmware (and any other firmware) as part of their troubleshooting efforts.

Figure 3: Example of Mainboard tab in CPU-Z

The Memory tab tells you the type and total amount of RAM installed in the system. This will give you an idea whether you can add additional RAM to your system.

Figure 4: Example of Memory tab in CPU-Z

The Serial Presence Detect (SPD) tab tells you how many memory slots are available in the system, and what size, type, speed, and manufacturer of each memory stick that is installed in each slot. This is extremely useful information when you are considering whether you can add more RAM to an existing system. You need to determine whether you either have any empty slots and/or whether your chipset and motherboard will let you use larger capacity memory modules to add additional RAM to your system. On some servers, the SPD tab will be blank, which is not much fun!

Figure 5: Example of SPD tab in CPU-Z

The two examples of output shown below in Figures 6 and 7 will help illustrate some of the more useful information that the CPU-Z tool exposes on the CPU tab. In this case, we have a 2.83GHz Intel Xeon E5440 “Harpertown”, with four cores and four threads (which means no hyper-threading) in Figure 6, while we have a newer 2.66GHz Intel Xeon X5550 “Gainestown”, with four cores and eight threads (which means that it has hyper-threading and it is enabled) in Figure 7. You can see differences in the supported instructions, along with differences in the size and types of cache between the two examples.

Figure 6: Intel Xeon E5440 CPU Details

Figure 7: Intel Xeon X5550 CPU Details

Using CPU-Z removes all doubt about the details of the processor(s) and memory installed in an existing system. It also confirms whether the processor is 64-bit capable (which both of these are, since EM64T is listed in the Instructions list), and whether the processor supports hardware virtualization (which both of these also do, since VT-x is in the Instructions list). You can also determine whether any power saving technology, such as Intel Enhanced Speed Step (EIST) is enabled by comparing the rated speed of the processor to the current Core Speed of the processor. If the current Core Speed of the processor is lower than the rated speed of the processor, that meaning that the processor has been throttled back to save power. In this case, the Xeon E5440 is running at its full rated speed of 2.83GHz (which is good), while the Xeon X5550 is only running at 1595MHz, even though it is rated at 2.67GHz (which is bad for performance). This means that some form of power management is in effect on the Xeon X5550, which has lowered its clock speed to save power.

Electrical power saving technologies like EIST are great for laptop computers, since they can help extend battery life significantly, and they are a good idea for desktop workstations since they can reduce power usage quite dramatically, but they are not a good idea for a mission critical database server. This is due to the fact that the switching mechanism that is used to vary the processor speed in response to the workload does not react quickly enough to spikes in the database server’s workload to avoid hurting query response times, especially with OLTP workloads. It is a good idea if you are concerned about performance to disable any power saving features in the BIOS of your database server (or set it to OS Control), and to make sure that Windows is using the “High Performance” Power Plan. The CPU tab in CPU-Z will help you detect the current clock speed of your processor(s), which will let you discover whether you have a problem with power management.

If you have an Intel Xeon 55xx, 56xx, 65xx, or 75xx processor, you may notice that the current Core Speed is actually higher than the rated clock speed for the processor. This means that the processor is using Intel Turbo Boost technology, which actually works quite well despite the somewhat silly name. Turbo Boost technology allows individual processor cores to be over-clocked if the overall processor is lightly loaded, which allows that processor core to handle a typical OLTP type query faster, assuming that the query is not waiting on non-processor resources. The upcoming Intel “Sandy Bridge” processor will have an improved version of Turbo Boost that will more aggressively overclock more cores based on the ambient temperature of the system. AMD’s upcoming “Bulldozer” processor will also have the ability to temporarily overclock all of the processor cores by 500MHz based on the processor load.

So now that you know all of this, run, don’t walk, and download CPU-Z and start running it on your servers! I bet you will find some interesting information…