Agent Jobs

Get Number Of Cores In SQL Server

From time to time you’ll need to visit your license costs, and to do so you’ll need to run some queries against sys.dm_os_sys_info so you can get the number of cores for SQL Server.

If you want all the dirt on sys.dm_os_sys_info just go here: https://msdn.microsoft.com/en-us/library/ms175048(v=sql.110).aspx

Here’s some quick SQL Logic to get you started.


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

Of course; if you’re curious about the logic, and need a simple way to verify you can always get a quick glance from basically using performance monitor. Just right-click the task-bar, and click ‘Task Manager’. If you look at the lower right you’ll see the number of cores and the number of logical processors.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s