Friday, August 29, 2014

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,
        CVALUE VARCHAR(128) NULL  )

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

    WITH CPUinfo
    AS (
([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            )

         LTRIM(RIGHT([CPU Category], CHARINDEX('x', [CPU Category]) - 1)) [CPU Category]

Run it against your servers, you'll see different results, but this is what I get from my laptop:    
#PhysicalCPUs #CoresPerCPU #VirtualCPUs TotalCores CPU Category
1 4 4 4 x64

Take a look at both of these for a little more information:

No comments:

Post a Comment