Quick Scan Report – Not Using All Cores

What this check looks for

The count of schedulers in sys.dm_os_schedulers whose status is VISIBLE ONLINE, compared against cpu_count in sys.dm_os_sys_info. When the first is smaller than the second, SQL Server is running on fewer processors than the machine has given it.

This page also covers issue 217, which is the same finding raised from the licensing side.

Why it matters

The machine has processors that SQL Server is not using, and the machine is being paid for either way. Whether that is a licensing cost, a virtual machine allocation or physical hardware, the gap is waste, and it is waste with no symptom other than the server being slower than it should be.

There are several reasons SQL Server ends up here, and the fix is different for each:

• The edition’s core limit. Standard edition caps the cores it will use, and Express caps it far lower. Give a Standard instance a 32 core machine and it will use its limit and leave the rest idle, permanently and silently. This is the most common cause and there is no setting that changes it.
• Affinity mask. Somebody restricted SQL Server to specific processors, usually years ago, usually to “leave room” for something else. The schedulers for the excluded processors show as VISIBLE OFFLINE.
• Resource Governor, where a pool has been given an affinity or a CPU cap.
• The virtual machine’s allocation, where the guest was given cores the host is not actually scheduling, or where a socket and core layout confuses the licensing limit.
• A NUMA node offline, which shows the same way.

The licensing case has a trap worth knowing. SQL Server licensed per core counts sockets and cores in a particular way, and a virtual machine presented as 16 sockets with 1 core each can hit an edition limit that the same 16 processors as 2 sockets with 8 cores each would not. Changing the virtual machine’s socket and core layout is free and sometimes recovers the difference entirely.

How to confirm it yourself

The gap itself:

SELECT (SELECT COUNT(*) FROM sys.dm_os_schedulers WITH (NOLOCK)
         WHERE [status] = 'VISIBLE ONLINE' AND [parent_node_id] < 64) AS [sql_is_using],
       (SELECT [cpu_count] FROM sys.dm_os_sys_info WITH (NOLOCK))     AS [os_has],
       SERVERPROPERTY('Edition')                                       AS [edition];

Which schedulers are offline, and why:

SELECT [parent_node_id],
       [status],
       COUNT(*) AS [schedulers],
       SUM([current_tasks_count]) AS [tasks]
  FROM sys.dm_os_schedulers WITH (NOLOCK)
 WHERE [parent_node_id] < 64
 GROUP BY [parent_node_id], [status]
 ORDER BY [parent_node_id], [status];

VISIBLE OFFLINE means affinity or Resource Governor. OFFLINE means the node is not available at all.

The hardware layout, which is what the licensing limit is applied to:

SELECT [cpu_count], [hyperthread_ratio], [socket_count],
       [cores_per_socket], [numa_node_count], [virtual_machine_type_desc]
  FROM sys.dm_os_sys_info WITH (NOLOCK);

And the settings that could be responsible:

SELECT [name], [value], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] LIKE 'affinity%';

The error log states the licensing limit explicitly at startup, which is the definitive answer:

EXEC sp_readerrorlog 0, 1, N'detected', N'socket';

It prints a line such as “SQL Server detected 2 sockets with 8 cores per socket … using 16 logical processors based on SQL Server licensing”.

How to fix it

Work out which cause it is first, because two of them are not fixable by configuration.

If it is the edition limit, the options are to upgrade the edition or to stop paying for processors that cannot be used. Standard edition’s limit is what it is. On a virtual machine, reducing the allocated processors to the limit frees them for other guests and costs nothing. Check the socket and core layout first, since a relayout sometimes recovers cores for free.

If it is affinity mask, clear it. The default, letting SQL Server use everything, is right for essentially every modern instance:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'affinity mask', 0;
EXEC sp_configure 'affinity I/O mask', 0;
RECONFIGURE;

Affinity settings were a tuning technique for hardware that no longer exists, and they usually travel with other settings from the same era. Check priority boost and lightweight pooling while you are there; both have their own checks.

If it is Resource Governor, look at the pool’s CAP_CPU_PERCENT and AFFINITY settings and decide whether they still reflect a real requirement.

Then check MAXDOP. Fixing the core count changes what a correct MAXDOP is, since the guidance is the smaller of eight and the cores in one NUMA node. Two checks in this report cover that.

How long it takes

About half an hour to identify the cause. Clearing an affinity mask is one statement and a restart; an edition or virtual machine change is a separate decision.

Related reports

Related checks

Frequently asked questions

We are on Standard edition, so this is expected. Then the finding is telling you that you are paying for processors the edition will not use. Reducing the allocation or upgrading the edition are both rational; leaving it is the only option that is neither.

Does hyper-threading count? Yes. cpu_count counts logical processors, so a 16 core machine with hyper-threading reports 32.

Is affinity ever the right answer? Very rarely, and essentially never on a dedicated SQL Server. Modern versions schedule better than a fixed mask does.

Why is a scheduler VISIBLE OFFLINE rather than OFFLINE? VISIBLE OFFLINE means SQL Server can see the processor and has been told not to use it, which points at affinity or Resource Governor. OFFLINE means it is not available at all.