Here you can see the CPU tab report for my Intel Core i5-2410M processor in my little 3.2lb Toshiba Portege R835-P55X. As I have said many times before, CPU-Z is a great little tool for quickly finding most of the gory details about your processor(s) and memory, whether it is in a laptop or in a database server.

In Figure 1, you can see my Intel Core i5 650, which is rated at 3.2GHz, is actually running at 3325.5MHz, which indicates that it is running at its full rated speed, plus 125.5MHz more, due to the effect of Intel Turbo Boost.

The latest release, which is version 1.59 of CPU-Z, was released on November 23, 2011.

Figure 1: CPU-Z 1.59

I have been using previous versions of this tool on production systems for many years, and it has never caused a problem for me. I think it is quite safe. If you are worried about using it, then you should not use it.

Running this utility will give you a lot of very detailed information about your processor(s), your motherboard, and your memory. It can also be used to confirm the actual current clock speed of your processor cores, which can be a big issue with some processors (particularly the Intel Xeon 55xx, 56xx,  and 75xx, along with the AMD Opteron 61xx families). when either software or hardware based power management is in effect.

Interestingly, the desktop and mobile Intel Sandy Bridge processors that I have checked so far do not show the huge difference in performance like those earlier processors when they are running with power management enabled.

In case you have never seen or used this utility before, you can see a screenshot of the CPU tab in Figure 1, below.

Figure 1: CPU-Z CPU Tab

This utility is free, and in my opinion, completely safe to run on a database server.

This tool will give you a great amount of detail about your processor(s), caches, motherboard, and memory, among other things. The CPU tab is shown in Figure 1.

Figure 1: CPU tab of CPU-Z 1.57

For example, this shows that I have a 45nm, Intel Core i7 930, that has a rated speed of 2.80GHz, but it is actually running at 2.93GHz. This shows that the processor is running at full speed, with Turbo Boost increasing the speed to 2.93Ghz (at least on Core 0).

It also shows that this processor is x64 compatible, since we see EM64T as one of the supported instructions. We can also see the size and types of the L1, L2, and L3 caches. Finally, I can see that this processor has four cores and eight threads, which means that it has hyper-threading, and hyper-threading is enabled.

The mainboard tab shown in Figure 2 gives you a lot of useful information about the motherboard, chipset, and main BIOS version.

Figure 2: Mainboard tab of CPU-Z 1.57

Figures 3 and 4 show the Memory and Serial Presence Detect (SPD) tabs of CPU-Z, which give you very useful information about the type and amount of memory that you have in your machine. Note, on some machines, the SPD tab will be blank. This seems to depend on the type and age of your machine, and the version of CPU-Z that you are using.

Figure 3: Memory tab of CPU-Z 1.57

Figure 4: SPD tab of CPU-Z 1.57

Over the several times I have talked about using CPU-Z, I have gotten numerous questions about whether it is safe to run on a production SQL Server. All I can say is that I have been using it myself for years, with absolutely no problems. Many other well-known people in the SQL Server community have been doing the same thing. I think it is an extremely valuable tool.

If you don’t feel comfortable using this tool, then don’t use it…

• Intel Xeon “Westmere-EX” support
• GPU frequency report on Intel Sandy Bridge processor
• AMD Zacate/Ontario processors support
• Report TDP on main page when available
• Cores clocks on floating menu
• P67/H67 stepping report

Since Intel Xeon “Westmere-EX” support is listed, I hope that means that the Westmere-EX is getting close to being released. The Westmere-EX is a 32nm die shrink of the previous 45nm Nehalem-EX server processor, that will have 10 physical cores, plus hyper-threading, for a total of 20 logical cores per physical processor. These are used in four socket and greater database servers. Keep in mind, you will need both Windows Server 2008 R2 and SQL Server 2008 R2 to be able to use more than 64 logical cores with SQL Server. With Westmere-EX, you could have 80 logical cores in a commodity four-socket machine.

Here are a few screenshot examples, from some of my personal systems. One new feature that I really like is the listing of the Max TDP (Thermal Design Power) for the processor, on the CPU tab, just to the left of the processor logo. This gives you a rough idea of the power efficiency of the processor. It is supposed to be used as a design guide for systems designers when they are considering cooling design for a system. You can see that both of my Sandy Bridge desktop systems have a Max TDP of 95 watts, while my little 3.2lb 32nm Arrandale laptop has a Max TDP of 25 watts. The older 45nm Desktop Core i7 920 has a Max TDP of 125 watts.

You can also right-click next to the processor selection combo-box, and get a floating menu that lists the current clock speed of each physical core of a given processor. With Intel Turbo Boost, you will see the core speed jumping around based on the overall load on the system.  If you have a newer Intel processor that supports Turbo Boost (Xeon 5500, 5600, 6600, or 7500 for servers, and Core i5 or Core i7 for desktop and mobile) you should always have it enabled, in my opinion.

Some systems allow you to disable it in the BIOS, but I really cannot think of any good reason to do that.  Turbo Boost is basically intelligent over-clocking that gives you a nice single-threaded performance boost. All modern Intel processors have plenty of thermal head-room, so there is no danger of overheating the processor or the system because of Turbo Boost.

Figure 1: Sandy Bridge Core i7 2600K system

Figure 2: Sandy Bridge Core i5 2500K system

Figure 3: Arrandale Core i3 350M system

Figure 4: Bloomfield Core i7 920 system

On the other hand, life is full of risks. Everyone has to carefully consider the risks and rewards of any course of action before they decide whether to do something or not. Everyone has a different level of risk tolerance, and you have to find the right balance between being overly cautious and being overly aggressive. I have seen far too many DBAs who are almost afraid to do anything to a system. Afraid to make a configuration change to a SQL Server instance, afraid to drop an index, afraid to use data compression, etc..

One path to becoming a “wise” DBA is combining technical knowledge with good judgment and hard-won experience gained by making decisions and actually doing things as part of your day to day work. Hopefully, most of your decisions are good ones, and most things you try are beneficial, but nobody will have a 100% success ratio. Sometimes, you will fail, or just make a bad decision, but I bet you will learn from your mistake! 

You should also remember that good, experienced DBAs are very hard to find, and thus are quite valuable. Unless you have a habit of making bad mistakes and bad decisions, I would say that your reputation and job are safer than you think. In most cases, your employer needs you more than you need them. Think about that! As Franklin Delano Roosevelt said in 1933, “The only thing we have to fear is fear itself”. Well, it is time to climb down from the soapbox…

Getting back to CPU-Z, all the anecdotal evidence I have is good. I have been using it regularly for many years, running it on many hundreds of servers, never having any problems. Many other well-known SQL Server MVPs piped up with similar results in the Twitter thread. Of course, this is not definitive proof that CPU-Z will never cause a problem for anyone. If you are convinced that CPU-Z might be risky, then no amount of anecdotal evidence is likely to convince you otherwise.

If you have any sort of HA solution in place (such as fail-over clustering or database mirroring), you can always run CPU-Z on a passive node or on the mirror, to pretty much eliminate any possible risk. You can run it on the other nodes or other side of the mirror during your rolling maintenance activities. If you don’t have any HA solution in place, I would say you have bigger things to worry about than CPU-Z.

I would like to hear people’s opinions and experience with using CPU-Z on their systems, good or bad.

As you can see in Figure 1 below, the Core Speed with the Balanced Power Plan is 1599.5 MHz, because the operating system has reduced the multiplier to 12, which reduces the clock speed from the rated speed of 2.53 GHz. This is done in the interests of minimizing the electrical usage of the CPU(s) in the server, but it has the unfortunate side effect of also reducing CPU and memory performance in some benchmarks (and in real life, especially with OLTP workloads).  The performance reduction on Geekbench is typically around 20% in my testing.

Balanced Power Plan

Figure 1: Core Speed of 1599.5 MHz on Intel Xeon E5540

In Figure 2 below, we see the multiplier at 21, with a resultant Core Speed of 2799.1 MHz, which is actually above the rated speed of 2.53 GHz. This is due to the effect of the first generation Turbo Boost technology in the Nehalem-EP processor, which boosts the speed of some cores when the overall processor is not working that hard. This technology is enhanced with Turbo Boost 2.0 in Sandy Bridge to be much more aggressive based on the overall temperature of the system.

High Performance Power Plan

Figure 2: Core Speed of 2799.1 MHz on Intel Xeon E5540

You can run CPU-Z on individual systems to detect this, but that can be time consuming if you have dozens or hundreds of servers to manage. Fellow SQL MVP Aaron Bertrand (blog|twitter) has a nice article about how to use PBM to detect servers that are using the Balanced Power Plan.  Greg Gonzalez also recently blogged about how to use Group Policy to enforce the use of the High Performance Power Plan for your servers.

Don’t forget that you may need to also go into your main system BIOS and change the Power Management settings there to get your processors to run at full speed all of the time (which will require a reboot). I always try changing the Windows Power Plan first, and if that does not work, then you need go into your main BIOS and spelunk around to find the right settings to either disable power management or set it to OS Control.

Finally, if you have a few minutes, run CPU-Z on a system or two, and send me a screenshot of the results. My e-mail address is glennb  at newsgator dot com (which might slow down the spambots).  I always like adding to my CPU-Z screenshot collection!

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.

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…