How to determine number of physical processors?

Question

Post reply

How to determine number of physical processors?

SQL ORACLE

One Orange Chip

Points: 27807
More actions
March 14, 2007 at 12:50 pm

#72642

How to determine number of processors before an SQL Server is installed?
Your input will be greatly appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there may be other ways, but i found this snippet on sqlteam:

-- Get the number of processors that the server has

declare @NumProcs int

EXEC master..xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SYSTEM\CurrentControlSet\Control\Session Manager',

@value_name = 'RegisteredProcessors',

@value = @NumProcs OUTPUT

SELECT @NumProcs

SELECT convert(varchar,isnull(@NumProcs,'0')) + ' processors are installed on ' + isnull(@@servername,'local')

results on one of my better servers:

-----------

4

--------------------

4 processors are installed on local

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

Thank you very much for your input. But this script may not be able to provide a correct result. I ran it against my own PC. It returned 2, but my PC only has 1 processor in reality.

We can run master..xp_msver to retrieve the number of processors. However, how to retrieve the number of processors before an SQL Server is installed?

Many thanks once again.

Lowell SSC Guru Points: 323518 More actions · Answer 3

that registry entry i found is not accurate, you are right...my home machine, which is just a single processor AMD64 has a registry entry of EIGHT processors for that value...I'll poke around, so you are looking for a processor detection tool for machines on the network, and not necessarily SQL server instances, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

ok poking around the registry, i found this key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment\NUMBER_OF_PROCESSORS

now to read that from each machine on the network , i would simply create a program to read that key fromt he registry, and use psTools to runt he executable on every machine;

http://www.microsoft.com/technet/sysinternals/ProcessesAndThreads/PsTools.mspx

if you don't have code for reading the registry, I can send you something in vb6 or vb.NET to do it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adam Jablonski SSC Veteran Points: 217 More actions · Answer 5

Adam Jablonski

SSC Veteran

Points: 217

March 15, 2007 at 3:58 am

#695166

exec

master..xp_msver processorCount

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 6

Adam:

The approach you recommended helps only after installing SQL Server. In reality, we can go to the Device manager to find the number of processors (I just confirmed it). The data from either registry or the task manager may not give us correct information. The information on the number of processors is important in licensing.

Many thanks for your input.

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 7

Find wherever sysinfo.exe is on the C: drive and execute it from a cmd window... more information will appear than you could possibly use including the number, make, model, revision, and speed of the processor(s).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Meletis SSC Enthusiast Points: 110 More actions · Answer 8

Unfortunately there is no easy way of doing that as multi-cored and hyper-threaded processors report themselves more than once. e.g if you have a new server with 4 dual-core processors, in the operating system you will see 4x2(cores)x2(hyper-threading)=16

In sql you should set the license for 4 procs, but as maxdop is concerned it should be 8

Steve Jones - SSC Editor SSC Guru Points: 736059 More actions · Answer 9

It also depends on your OS. Some can detect cores, some detect the threading (so hyperthreading on appears as 2).

Windows XP can detect multiple logical processors, but not physical ones. Windows 2003 does a better job, but I'm not sure if the new multi-core processors are detected properly.

The bottom line is that I'm not sure it matters for the software. With virtualization, multiple cores, etc., the human looking at the hardare or the BIOS might be the only good way of accurately counting CPU slots. Any configuration based on this should be done by the admin.

http://www.intel.com/cd/ids/developer/asmo-na/eng/200678.htm

http://groups.google.com/group/comp.lang.asm.x86/browse_thread/thread/67cbd72171ff4114/f15f5666e966e4b5%23f15f5666e966e4b5

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 10

Jeff:

sysinfo does not work if we have multi-thread.

Many thanks for your input.