How to get host server CPU info from SSMS

Question

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?

VtC as dup of How can I tell how many Cores SQL Server is actually using? — LowlyDBA - John M, May 10 '21 at 14:33

score 0 · Answer 1 · answered May 10 '21 at 07:53

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 < @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:

I hope this helped.

Thank you so much @Francesco, much appreciated. For some reason that i don't understand however, the values I have for cores using T-SQL, don't seem to match the info I had been given. I have therefore reverted to Sys admin to provide the info. Thanks again for your time, very much appreciated — PTL_SQL, May 10 '21 at 16:02

How to get host server CPU info from SSMS

1 Answers1