How can I tell how many Cores SQL Server is actually using?

Question

I have two servers running SQL Server.

• Server 1: SQL Server 2008 R2 Express (4 core)
• Server 2: SQL Server 2012 Developer Edition (8 core)

As far as I am aware SQL Server 2008 R2 Express should only use one core.

SQL Server 2012 Developer version should use all 8 cores.

However if I run the following command inside of SQL query on SQL Server 2008 R2 Express it's showing 4 cores.

select scheduler_id, cpu_id, status, is_online 
from sys.dm_os_schedulers 
where status = 'VISIBLE ONLINE'

Am I using the right command to gauge the usage?

Answer 1

Express edition is limited by 1 processor, not 1 core:

SQL Server supports the specified number of processor sockets multiplied by the number of logical CPUs in each socket. For example, the following is considered a single processor for purposes of this table: A single-core, hyper-threaded processor with 2 logical CPUs per socket. A dual-core processor with 2 logical CPUs. A quad-core processor with 4 logical CPUs. SQL Server is licensed per processor socket, and not per logical CPU basis.

http://msdn.microsoft.com/en-us/library/ms143760(v=sql.105).aspx

And yes, sys.dm_os_schedulers can be used to ensure, that all 4 cores are mapped with schedulers.

Answer 2

There is a script you can use, I found it 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

Otherwise you can see the full list here:

SELECT scheduler_id, 
       cpu_id, 
       STATUS, 
       is_online
FROM sys.dm_os_schedulers; 
GO

Don't forget that Parallelism is a fantastic SQL Server option that is used to take benefits of having more than one processor on the hosting server and split the current task into smaller tasks and distribute it among the available processors in order to complete it faster.

Max Degree of Parallelism can be used to set the maximum number of processors that can be used to run the query in the parallel plan execution. There are many ways that can be used to set that value at the server, database, and query level.

Answer 3

Use this command with OS greater than 2007:

->> WMIC CPU Get DeviceID,NumberOfCores,NumberOfLogicalProcessors

For Physical servers DeviceID ~ Socket, NumberofCores ~ CPU's, NumberOfLogicalProcessors ~ Threads. MS now uses a core-based license model. CAL's are out of scope. Search "Microsoft SQL Server 2012 Licensing Guide" if further information is necessary.

Example below would be necessary to cost/charge 2-pack*4 CPU SQL Server License for Standard/Enterprise. Total 8 cores (CPU's).

DeviceID  NumberOfCores  NumberOfLogicalProcessors
CPU0      4              8
CPU1      4              8

Performance tab in Task Manager or CPU tab in Resource Monitor will show 16 CPU running. Logical Processors. Search "Intel X5550", which is the processor I was checking. Intel has all specifications on website ark.intel.com to x-check information needed.