SQL Server – How to Get CPU Speed

cpupowershellsql serversql-server-2016

when I run the following query from Glenn Berry 

-- Hardware information from SQL Server 2016  (Query 18) (Hardware Info)
SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],
committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type], 
sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc AS [Virtual Machine Type], 
softnuma_configuration_desc AS [Soft NUMA Configuration],
sql_memory_model_desc -- New in SQL Server 2016 SP1
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
------

enter image description here

when I use powershell I get more details though:
enter image description here

I have been reluctant to learn powershell, but I plan to accept it soon…

meanwhile, the question is:

Is there any way in sql server to find out the speed of the processor (in this case 2.60 Ghz)?

Best Answer

You could use xp_cmdshell, or you could use xp_regread. Since xp_regread is 'safer' (I mean, you don't have to change any configuration options to use it), you could do something like this:

DECLARE @cpu_speed_mhz int,
        @cpu_speed_ghz decimal(18,2);

EXEC master.sys.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                            @key = 'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
                            @value_name = '~MHz',
                            @value = @cpu_speed_mhz OUTPUT;

SELECT @cpu_speed_ghz = CAST(CAST(@cpu_speed_mhz AS DECIMAL) / 1000 AS DECIMAL(18,2));

I tested this on a few different computers and it seemed to get me what I needed. It may work for you as well.

Just remember that xp_regread is undocumented, and may blah blah blah like Microsoft ever deprecates anything anyway.

Hope this helps!

Related Question