Search This Blog

SQL Server CPUs -- physical, virtual and core count

I received an email from one of my readers this morning, asking if there was a way to query CPU information from the server with tSQL.  I put together the piece below, and wanted to go ahead and share it here with all of you.  Real quick and easy, uses sys.dm_os_sys_info and xp_msver to return specific details about your CPUs, physical, virtual and core count.

    DECLARE @results TABLE (
        ID INT NULL,
        CNAME VARCHAR(100) NULL,
        IVALUE FLOAT NULL,
        CVALUE VARCHAR(128) NULL  )

    INSERT @results
    EXEC ('[master]..[xp_msver]');;

    WITH CPUinfo
    AS (
SELECT 
([cpu_count] / [hyperthread_ratio]) [#PhysicalCPUs],
                 CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
  ELSE (([cpu_count]-[hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))    END [#CoresPerCPU],
     [cpu_count] [#VirtualCPUs],
            CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
    ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] /            [hyperthread_ratio]))     END [TotalCores],
     ( 
       SELECT CVALUE FROM @results WHERE CNAME = 'Platform' ) [CPU Category]
       FROM sys.dm_os_sys_info            )

       SELECT
             [#PhysicalCPUs],
             [#CoresPerCPU],
             [#VirtualCPUs],
             [TotalCores],
         LTRIM(RIGHT([CPU Category], CHARINDEX('x', [CPU Category]) - 1)) [CPU Category]
       FROM 
          CPUinfo





Run it against your servers, you'll see different results, but this is what I get from my laptop:    
#PhysicalCPUs#CoresPerCPU#VirtualCPUsTotalCoresCPU Category
1444x64

Take a look at both of these for a little more information:
        xp_msver                     http://msdn.microsoft.com/en-us/library/ms187372.aspx
        sys.dm_os_sys_info      http://msdn.microsoft.com/en-us/library/ms175048.aspx