{"id":943,"date":"2015-02-25T12:30:52","date_gmt":"2015-02-25T20:30:52","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=943"},"modified":"2018-11-13T10:52:26","modified_gmt":"2018-11-13T18:52:26","slug":"balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes\/","title":{"rendered":"Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes"},"content":{"rendered":"

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<\/a> 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.<\/p>\n

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<\/a> (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.<\/p>\n

Fortunately, it is pretty easy to fix this issue by using the ALTER SERVER CONFIGURATION<\/a> 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<\/a>.<\/p>\n

You can use the queries in the code below to detect and correct this issue.<\/p>\n

-- Balancing your available SQL Server core licenses evenly across two NUMA nodes<\/span>\n-- Glenn Berry<\/span>\n-- SQLskills.com<\/span>\n\n\n-- Get socket, physical core and logical core count from SQL Server Error Log<\/span>\nEXEC<\/span> sys.xp_readerrorlog 0, 1, N'detected'<\/span>, N'socket'<\/span>;\n\n\n-- SQL Server NUMA node information <\/span>\nSELECT<\/span> node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count, \n       active_worker_count, avg_load_balance, resource_monitor_state\nFROM<\/span> sys.dm_os_nodes WITH<\/span> (NOLOCK) \nWHERE<\/span> node_state_desc <> N'ONLINE DAC'<\/span> OPTION<\/span> (RECOMPILE);\n\n\n-- SQL Server schedulers by NUMA node<\/span>\nSELECT<\/span> parent_node_id, \n  SUM<\/span>(current_tasks_count) AS<\/span> [current_tasks_count], \n  SUM<\/span>(runnable_tasks_count) AS<\/span> [runnable_tasks_count], \n  SUM<\/span>(active_workers_count) AS<\/span> [active_workers_count], \n  AVG<\/span>(load_factor) AS<\/span> avg_load_factor\nFROM<\/span> sys.dm_os_schedulers WITH<\/span> (NOLOCK) \nWHERE<\/span> [status] = N'VISIBLE ONLINE'<\/span>\nGROUP<\/span> BY<\/span> parent_node_id;\n\n\n\n-- SQL Server NUMA node and cpu_id information<\/span>\nSELECT<\/span> parent_node_id, scheduler_id, cpu_id\nFROM<\/span> sys.dm_os_schedulers WITH<\/span> (NOLOCK) \nWHERE<\/span> [status] = N'VISIBLE ONLINE'<\/span>;\n\n\n-- Fixing the problem<\/span>\n\n-- Unfortunately, this does not work, due to the license limits in SQL 2012\/2014 Standard Edition<\/span>\nALTER<\/span> SERVER CONFIGURATION SET<\/span> PROCESS AFFINITY NUMANODE = 0,1;\n\n-- Msg 5833, Level 16, State 2, Line 7<\/span>\n-- The affinity mask specified is greater than the number of CPUs supported or licensed on this edition of SQL Server.<\/span>\n\n\n-- This command spreads your available 32 logical core licenses across two NUMA nodes <\/span>\n-- This is valid for an Intel processor, with HT enabled<\/span>\nALTER<\/span> SERVER CONFIGURATION\nSET<\/span> PROCESS AFFINITY CPU = 0 TO<\/span> 15, 25 TO<\/span> 40;<\/pre>\n
 <\/pre>\n