xp_instance_regread -> It is an undocumented stored proc for reading registry settings.
If the following script is run, it returns cpu mark, model information.
EXEC xp_instance_regread
'HKEY_LOCAL_MACHINE',
'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
'ProcessorNameString';
|
sys.dm_os_sys_info -> The other method to collect cpu information is sys.dm_os_sys_info system view. It contains logical cpu count,physical cpu count, total memory etc..
I prepared a script that how to get cpu some cpu information within Sql Server.
--- How to get cpu-ram
drop table if exists #tmp1 ---may run on sqlserver 2016 and later
create table #tmp1(id int default(1),Val varchar(100),
[Data] varchar(100))
insert into #tmp1(Val,[Data])
EXEC xp_instance_regread
'HKEY_LOCAL_MACHINE',
'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
'ProcessorNameString';
select compname as ComputerName,
Data as ProcessorName,
Number_of_Logical_CPU,
hyperthread_ratio,
Number_of_Physical_CPU,
Total_Physical_Memory_IN_KB
from (
select 1 as id,
SERVERPROPERTY('MachineName') as compname ) as tbl
inner join #tmp1 on tbl.id=#tmp1.id
inner join (
SELECT 1 as id,
cpu_count AS [Number_of_Logical_CPU]
,hyperthread_ratio
,cpu_count/hyperthread_ratio AS [Number_of_Physical_CPU]
,physical_memory_kb/1024 AS [Total_Physical_Memory_IN_KB]
FROM sys.dm_os_sys_info
) as tbl2 on tbl.id=tbl2.id OPTION (RECOMPILE);
|
