Post reply

CPUs on a Machine

  • 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

  • January 10, 2008 at 10:33 am

    #766748

    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

  • January 10, 2008 at 10:56 am

    #766761

    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

  • January 10, 2008 at 11:01 am

    #766764

    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?

  • January 10, 2008 at 11:19 am

    #766777

    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

  • January 10, 2008 at 11:23 am

    #766780

    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?

  • January 10, 2008 at 12:01 pm

    #766793

    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

  • January 10, 2008 at 1:22 pm

    #766834

    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

  • January 10, 2008 at 1:40 pm

    #766852

    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

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

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