How to get host server CPU info from SSMS Help please!
I need to get number of CPU's and cores per cpu for each host server running SQL Server. Querying master.sys.dm_os_sys_info is not producing the same result as what is seen when checked on the host server.

kindly point me in the direction of a code I can use please.

What is the difference between physical_cpu and logical_cpu and which one do I need? I'm trying to calculate total cores on each machine for licensing purposes.

Can i take the number of cores returned by the query pasted below as what I need to calculate my license requirement?

In performance monitor on the server, I have sockets =2, Cores = 16, logical processors = 32. Is 16 cores what I need for my SQL 2012 and 2016 licence calculations?


Here the query that  I'm using right now:
USE master;

SET NOCOUNT ON

SELECT 'physical_cpu' = cpu_count / hyperthread_ratio
    ,'cores' = CASE 
        WHEN hyperthread_ratio = cpu_count
            THEN cpu_count
        ELSE (cpu_count / hyperthread_ratio) * ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio))
        END
    ,'logical_cpu' = CASE 
        WHEN hyperthread_ratio = cpu_count
            THEN cpu_count
        ELSE ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio))
        END
FROM master.sys.dm_os_sys_info

Anything better than that?
I found this query here:
----------------------------------------------------------------------------------------------------------------
-- CPU VISIABLE ONLINE CHECK
----------------------------------------------------------------------------------------------------------------
DECLARE @OnlineCpuCount int
DECLARE @HiddenCpuCount int
DECLARE @LogicalCpuCount int

SELECT @OnlineCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'
SELECT @HiddenCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status != 'VISIBLE ONLINE'
SELECT @LogicalCpuCount = cpu_count FROM sys.dm_os_sys_info 

SELECT @LogicalCpuCount AS 'ASSIGNED ONLINE CPU #', @OnlineCpuCount AS 'VISIBLE ONLINE CPU #', @HiddenCpuCount AS 'HIDDEN ONLINE CPU #',
   CASE 
     WHEN @OnlineCpuCount &lt; @LogicalCpuCount 
     THEN 'You are not using all CPU assigned to O/S! If it is VM, review your VM configuration to make sure you are not maxout Socket'
     ELSE 'You are using all CPUs assigned to O/S. GOOD!' 
   END as 'CPU Usage Desc'
----------------------------------------------------------------------------------------------------------------
GO

And if you want you can also check each CPU one by one with this:
SELECT scheduler_id, 
       cpu_id, 
       STATUS, 
       is_online
FROM sys.dm_os_schedulers; 
GO

Now, if you are trying to calculate the licensing you can use several websites:

MS SQL 2019 Std Licensing Calculator (unofficial)
MS SQL 2019 Ent Licensing Calculator (unofficial)
https://www.dxc.technology/SLMS/flxwd/148445-dxc_sql_calculator
https://www.sqlgovernor.com/en/calculator/

I hope this helped.