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:
sys.dm_os_sys_info http://msdn.microsoft.com/en-us/library/ms175048.aspx