4

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)?

10

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.