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	#VirtualCPUs	TotalCores	CPU Category
	1	4	4	4	x64

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