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