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.
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!
7 thoughts on “How to Balance SQL Server Core Licenses Across NUMA Nodes”
Thanks for the post and script Glenn!
In my case I have the following:
“SQL Server detected 2 sockets with 12 cores per socket and 24 logical processors per socket, 48 total logical processors; using 40 logical processors based on SQL Server licensing.”
I expanded on your query to find NUMA node and cpu_id information to include ‘VISIBLE OFFLINE’ as well. This was helpful for me to verify what I need to set my process affinity CPU to in order to spread evenly across two NUMA nodes in my configuration.
— 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
SET PROCESS AFFINITY CPU = 0 TO 19, 24 TO 43;
Thanks! That is a good addition.
For SQL Server Enterprise on a server with a high core count, can less core packs be used if not all the cores are required? We’ve got a 2 socket 10 core server with 1.5TB memory installed. We spec’d the server to maximize the memory and minimize the compute. We only need maybe 6-8 of the 20 cores for our application which is a data warehouse and is not CPU bound. Would less core packs not just result in the message described in this post and a requirement to manually balance the cores across the NUMA nodes?
Unfortunately no. You have to license every single physical core that is present in the system, even if it is disabled in the BIOS.
As far as the message in the error log goes, SQL Server will use every single physical and logical core that the operating system can see. If you have disabled cores in the BIOS, the OS won’t see them, but your still have to license them.
If you are going bare metal, you would have been better off to get a four or six core CPU (which would still have the same memory capacity as the ten core CPU).
One way around this is to use virtualization. You could create a VM that only has the number of cores that you think you need, and just license the cores in the VM.