How to Balance SQL Server Core Licenses Across NUMA Nodes

Introduction

SQL Server 2019 Standard Edition has hardware-related, per instance license limits. These license limits involve processor core counts, physical sockets, and RAM usage. The limits have been in place since SQL Server 2012. Some of the limits have increased somewhat since SQL Server 2012, but they are still something you need to be aware of. This post will show you how to balance SQL Server Core licenses across NUMA nodes.

With modern server hardware, it is very easy to exceed these license limits on a two-socket server. It is also quite easy to exceed these limits on a VM. If you exceed the socket or core limits for a SQL Server Standard Edition instance, you will have several problems.

First, SQL Server will only use the number of sockets or cores that the SQL Server Standard Edition license allows. This is true even if the operating system sees those sockets and cores. Second, by default, the cores that SQL Server will use will not be evenly balanced across your NUMA nodes. Third, Microsoft will still expect you to license all of the physical processor cores in the machine. This is true even if SQL Server won’t use them.

Update: I have a YouTube video that demonstrates this on an AMD EPYC 7763 system.

How About an Example?

Let’s say that you install SQL Server 2019 Standard Edition on a new, two-socket server. You follow my advice, and choose a processor from my “Recommended Intel Processors for SQL Server” post. To be specific, you decide to get two Intel Xeon Gold 6246R processors. This processor has 16C/32T, so two of them will have a total of 32C/64T.

On a non-virtualized instance, the license core limit is based on physical processor cores. You can only use 24 total physical cores with an instance of SQL Server 2019 Standard Edition. Your new machine has 32 physical cores, but SQL Server 2019 Standard Edition is only going to use 24 of them. If HT or SMT is enabled, these numbers are doubled, since you have logical cores.

Using my example processor on a two-socket server, here is what you will see in the SQL Server error log. This tells you that SQL Server is not using all of the logical processors that the operating system can see.

SQL Server detected 2 sockets with 16 cores per socket and 32 logical processors per socket, 64 total logical processors; using 48 logical processors based on SQL Server licensing.

Ok, that is bad enough, but it gets worse. Unless you fix it, SQL Server 2019 Standard Edition will use 32 logical cores on NUMA node 0, but only 16 logical cores on NUMA node 1. This can have a significant negative effect on performance. What you want is for SQL Server to use 24 logical cores on each NUMA node.

How to Balance SQL Server Core Licenses Across NUMA Nodes

Fortunately, it is pretty easy to fix this issue by using the ALTER SERVER CONFIGURATION command that was introduced in SQL Server 2008 R2.

The queries below will help you understand if you have a problem or not. An example ALTER SERVER CONFIGURATION command is also shown. Note: You will probably have to use different PROCESS AFFINITY values for your specific situation.

-- Balancing your available SQL Server core licenses evenly across two NUMA nodes
-- Glenn Berry


-- Get socket, physical core and logical core count from SQL Server Error Log
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';


-- SQL Server NUMA node information 
SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count, 
       active_worker_count, avg_load_balance, resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK) 
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);


-- SQL Server schedulers by NUMA node
SELECT parent_node_id, 
  SUM(current_tasks_count) AS [current_tasks_count], 
  SUM(runnable_tasks_count) AS [runnable_tasks_count], 
  SUM(active_workers_count) AS [active_workers_count], 
  AVG(load_factor) AS avg_load_factor
FROM sys.dm_os_schedulers WITH (NOLOCK) 
WHERE [status] = N'VISIBLE ONLINE'
GROUP BY parent_node_id;



-- SQL Server NUMA node and cpu_id information
SELECT parent_node_id, scheduler_id, cpu_id, status
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE [status] IN (N'VISIBLE ONLINE', N'VISIBLE OFFLINE')
ORDER BY parent_node_id, cpu_id;

-- Fixing the problem

-- Unfortunately, this does not work, due to the license limits in SQL 2012 and newer Standard Edition
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0, 1;

-- Msg 5833, Level 16, State 2, Line 7
-- The affinity mask specified is greater than the number of CPUs supported or licensed on this edition of SQL Server.


-- This command spreads your available 48 logical cores evenly across two NUMA nodes 
-- This is valid for a processor with HT or SMT enabled
-- 24T + 24T with a 16C/32T CPU
ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU = 0 TO 23, 32 TO 55;

BTW, I wrote about this licensing issue back in 2013. If you would like more information about the difference between physical cores and logical cores, please read the post below.

• What is the Difference Between Physical Sockets, Physical Cores, and Logical Cores?

Final Words

These issues (the core limit and the potential imbalance across NUMA nodes) have been around since SQL Server 2012. The best way to avoid them completely is to make sure you are not installing SQL Server Standard Edition on a machine that exceeds the core or socket limits. This is true for both physical and virtual machines.

If you have already installed SQL Server Standard Edition on a multi-socket machine that exceeds the license limits, you can balance the SQL Server Core licenses across your NUMA nodes. This will minimize the performance impact of the license limit.

If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!