Tuesday, November 20, 2012

SQL Server - Find number of physical processors, cores, logical processors and RAM using T-SQL


There are times when you want to know the SQL Server box configuration, specifically processors. It can be frustrating if you have to wait for a couple of days for another group in your ogranization to give the information. Here is something, that you can use to find out the details yourself.

I found many posts that help you find physical and logical CPUs. But I was interested specifically in the number of cores too. Few posts mentioned that there is no way you can find number of cores using T-SQL.

Here is the query that can give you the number of cores too. Test it yourself and let me know if this does not work in your scenario.

SELECT ( cpu_count / hyperthread_ratio )AS NumberOfPhysicalCPUs
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS NumberOfCoresInEachCPU
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( cpu_count / hyperthread_ratio ) * ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS TotalNumberOfCores
, cpu_count AS NumberOfLogicalCPUs
, CONVERT(MONEY, Round(physical_memory_in_bytes / 1073741824.0, 0)) AS TotalRAMInGB
FROM
sys.dm_os_sys_info
 
 Please keep in mind, you need to have VIEW SERVER STATE permission to use the DMV sys.dm_os_sys_info 

 

4 comments:

  1. Thanks for the information. very useful ...

    ReplyDelete
  2. This is awesome, been looking for something like this for ages, only thing is that it doesn't work properly on SQL Server 2012 as they have changed the column name in the DMV from physical_memory_in_bytes to physical_memory_kb, this doesn't matter for me so much though as it's the physical cores I care about for licensing.

    ReplyDelete
  3. Fixed for SQL2012:
    SELECT cpu_count / hyperthread_ratio AS NumberOfPhysicalCPUs, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio))
    END AS NumberOfCoresInEachCPU, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE (cpu_count / hyperthread_ratio) * ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio))
    END AS TotalNumberOfCores, cpu_count AS NumberOfLogicalCPUs, CONVERT(MONEY, ROUND(physical_memory_kb / 1048576.0, 0)) AS TotalRAMInGB
    FROM sys.dm_os_sys_info

    ReplyDelete
  4. does not work.
    Core i7 2600 has 1 Socket, 4 Cores, 8 Logical Processors (Hyperthreading ON).
    But,
    NumberOfPhysicalCPUs = 1 (it is right)
    TotalNumberOfCores = 8 (this is wrong)
    NumberOfLogicalCPUs = 8 (it is right)

    ReplyDelete