CPUs on a Machine

Question

Post reply

CPUs on a Machine

Eric Peterson

Hall of Fame

Points: 3192
More actions
January 10, 2008 at 10:16 am

#180548

All
I am documenting my SQL Server environments.
Anyone have a way to determine (through TSQL ) how many CPUs are being used by a SQL Server Instance?
Thanks in Advance
Eric Peterson

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Indeed there is.

exec xp_msver 'ProcessorCount' -- There are a lot of other options on msver. Well worth a look.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

The only problem with xp_msver is that it tells you how many processors SQL thinks there is, if you have hyperthreading it will count the logical processors. Maybe you pull this through WMI.

Andrew

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

For what it's worth - xp_MSVer returns the number of CPU COREs (since they are perceived as separate processors by the OS).

On a slightly different note - where did the "dedicate this specific CPU to SQL Server"? Did they take that away (i.e. it now uses ALL processors whether or not you like it)? That would certainly explain the change in licensing policy.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Eric Peterson Hall of Fame Points: 3192 More actions · Answer 4

exec xp_msver 'ProcessorCount' works great for showing the number of CPU cores. It is what I was looking for.

Some of my SQL Servers are SQL 2000 and the xp_msver doesn't work. Any ideas on that one?

Thanks again

Eric Peterson

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

I have both 2000 and 2005 on this box, and Xp_msver works under both. What error are you getting when you run it on 2000?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Eric Peterson Hall of Fame Points: 3192 More actions · Answer 6

Thanks again all

I found the procedure, but in SQL 2000 you have to be in the master database or reference the master database for it to work...exec master.dbo.xp_msver 'ProcessorCount'

Thanks

Eric Peterson

Steve T SSCommitted Points: 1994 More actions · Answer 7

This does not give you the physical count, this gives you the logical count only. Just fyi.

If you have hyperthreading (rare these days) and/or dual core systems, they would double the count. So if you are accounting for logical processors, this would work. If you are accounting for physical, this would not work. Windows XP/2003/2000 does not have any way of showing you the actual physical count by default. See article http://support.microsoft.com/kb/932370.

You can try this free tool available at this site. It will give you the physical count.

---> http://www.gtopala.com/siw-download.html

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

GilaMonster (1/10/2008)
Indeed there is.
exec xp_msver 'ProcessorCount' -- There are a lot of other options on msver. Well worth a look.

Clarification: This will return the number of processors that SQL thinks the machine has. Not the number of physical processors.

SQL doesn't know if the processors that it's using are physical chips, cores or hyperthreaded virtual cores. It's what the OS tells it that it has.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass