One issue I recently ran into with a customer was a case where they were using a new, two-socket server with two Intel Xeon E5-2697 v2 Ivy Bridge-EP processors. This particular 22nm processor has twelve physical cores, plus hyper-threading, so you can have 24 logical cores for one entire physical processor, and 48 logical cores for a two-socket server with both sockets populated.
The reason why we had an issue is because both SQL Server 2012 and 2014 Standard Edition have an artificial license limit of either 16 physical cores or 32 logical cores (along with a limit of four sockets, whichever is lower). This meant that SQL Server 2014 Standard Edition could only use 32 out of the 48 logical cores in this new server. To make matters worse, the available cores were spread unevenly across the two NUMA nodes in the server. There were 24 logical cores on one NUMA node and 8 logical cores on the other NUMA node in the server, which was not an ideal situation.
Fortunately, it is pretty easy to fix this issue by using the ALTER SERVER CONFIGURATION command that was introduced in SQL Server 2008 R2. On the negative side, fixing it was not completely intuitive, because of these dumb Standard Edition license limits.
You can use the queries in the code below to detect and correct this issue.
-- Balancing your available SQL Server core licenses evenly across two NUMA nodes -- Glenn Berry -- SQLskills.com -- 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 FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE [status] = N'VISIBLE ONLINE'; -- Fixing the problem -- Unfortunately, this does not work, due to the license limits in SQL 2012/2014 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 32 logical core licenses across two NUMA nodes -- This is valid for an Intel processor, with HT enabled ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 15, 25 TO 40;
A similar issue with “grandfathered” SQL Server 2012 Enterprise Server+CAL customers was discussed by my colleague, Jonathan Kehayias here.
6 thoughts on “Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes”
We already have suffered that unbalancing problem in some customers. The solution is to balance it across the NUMA nodes manually with the process affinity mask as you do.
Just a suggestion, we got better performance (running real customer load benchmarks or tpc like benchmarks) when using just even or odd processor numbers in the mask for each NUMA node instead of a continuous range. This way the potential penalty of HT seems to be minimized. In other customers we also found convenient to exclude the first physical core (as it seem used by default by some OS processes) or give a lower load to the NUMA node that have connected to its pci-e slots high IO demanding devices (HBAs, Mellanox, 10 Gbps cards…)
do you uncheck the first cpu of the second numa node as well or only cpu0?
I have often wondered if this would also be the basis for a good approach for multiple SQL Instances on a single server.
In other words use the processor affinity to assign cores to specific instances.
Yes, you could do that.
A bit late to the party, but definitely required in Always On multi instance installation, otherwise cpu scheduling is awful, Always On breaks.
very helpful simple solutions for my sql database performance.