0

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.

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

  2. 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.

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

  4. 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?

1

1 Answer 1

0

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.

1
  • 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, 2021 at 16:02

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