- Total number of physical CPUs
- Total number of physical cores per CPUs
- Total number of physical cores
- Total number of virtual CPUs
- Processor type (x86 or x64)
As a result of this question, I wrote the following script using sys.dm_os_sys_info and xp_msver, which returns the required information:
exec xp_msver 'ProcessorCount'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| DECLARE @xp_msver TABLE ( [idx] [int] NULL ,[c_name] [varchar](100) NULL ,[int_val] [float] NULL ,[c_val] [varchar](128) NULL )INSERT INTO @xp_msverEXEC ('[master]..[xp_msver]');;WITH [ProcessorInfo]AS ( SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus] ,CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio])) END AS [number_of_cores_per_cpu] ,CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio])) END AS [total_number_of_cores] ,[cpu_count] AS [number_of_virtual_cpus] ,( SELECT [c_val] FROM @xp_msver WHERE [c_name] = 'Platform' ) AS [cpu_category] FROM [sys].[dm_os_sys_info] )SELECT [number_of_physical_cpus] ,[number_of_cores_per_cpu] ,[total_number_of_cores] ,[number_of_virtual_cpus] ,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]FROM [ProcessorInfo] |
The code of this script is tested on SQL Server 2005 and above versions.
Reference: https://basitaalishan.com/2014/01/22/get-sql-server-physical-cores-physical-and-virtual-cpus-and-processor-type-information-using-t-sql-script/
