sqlskills-logo-2015-white.png

Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes

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

  1. 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…)

  2. 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.

    1. A bit late to the party, but definitely required in Always On multi instance installation, otherwise cpu scheduling is awful, Always On breaks.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.