While troubleshooting high CPU usage on one of the instances, someone pointed out that the “Max Degree of Parallelism” option was set correctly or not. It was set correctly to 4 and it did not contribute to the high CPU usage. Then the discussion went on to find out How many Physical Processors does the server have? Even though this question did not have much importance in this context, it was a good one. Because this information is not easy to get without using 3rd party tools.
The number of CPUs the Task Manager, Windows Device Manager, Perfmon display are the number of Logical CPUs. The Processors now a days are Dual Core/Hyper-threaded. The number of CPUs that SQL Server detects are all Logical Processors.
Here is how I find out the number of Physical CPUs installed on a given server. The server in the screenshot has 6 Logical Processors.
One of the options to find out the number of Physical CPUs installed is to use Process Explorer. In Process Explorer clicking on View –> System Information option would display the System Information in General. Clicking on Show one graph per CPU option would display the information as in this screenshot.
As highlighted in this screenshot, the Logical CPUs are grouped together according to the number of Physical CPUs. We have 3 such groups which in turn means that the Server has 3 Dual Core CPUs, hence the SQL Server is recognizing 6 Logical CPUs.
But on some servers I have seen that Process Explorer does not display the information correctly. Like in the below example where Windows Recognizes 8 Logical CPUs and Process Explorer also treats each one of those CPUs to be different Physical CPUs.
For sure I know that this server does not have 8 Physical CPUs installed on it. Welcome CPU-Z, the free tool which reports CPU related information far more accurately.
As in this screenshot, the number of processors listed is 2 and those processors are hyper-threaded to run in 4 threads. Thus the number of logical CPUs is arrived as detected physical CPU * number of threads. In this case the number of Logical CPUs would be 2 * 4 = 8.
The information related to the Physical CPUs can be retrieved in many ways from BIOS, through a custom application (like the one of Slava Oks) and so on. But I like to use freely available tools like CPU-Z which do the job for me with ease.