Sql-server – One NUMA node uses all memory

memorynumasql serversql-server-2012

I am running a SQL 2012 SP3 CU9 Standard server with 2 CPU's each having 4 cores (hyperthreading enabled). I have 384 GB RAM (Yes I know that I cannot use it all in SQL standard). Lock pages in memory is enabled.

My problem is that one of the CPU nodes seems to allocate all memory leaving just a little for the secound CPU node, causing one of the nodes to have a very low PLE and the other one a high PLE.

Did I configure something wrong?

Best Answer

When it comes to CPU core usage, Standard Edition starts with the first NUMA node. Whenever it exhausts the available licensing (2016 & newer, it's 4 sockets or 24 cores, whichever comes first - 2014 & prior, it's 4 sockets or 16 cores, whichever comes first), then it doesn't use the remaining cores.

Here's the tricky part: if it exhausts licensing, and whole NUMA nodes remain with zero cores activated, then that entire NUMA node is offline - unusable for CPU work or memory. I've blogged about that in more detail here.

You asked about memory, and I have a hunch (although I don't have a server handy to prove it) that it might work the same way. If the first NUMA node contains 128GB or more of memory, then that might be the vast majority of memory in use.

It's a very rare edge case, and I wouldn't expect to see any guidance from Microsoft to confirm or deny this. (It's certainly not something they'd test as part of mainstream delivery - if you've got 384GB RAM, you'd wanna use Enterprise Edition instead.)

I'm stretching to think about how you might disable this behavior, other than playing around with soft NUMA.

Related Solutions

Sql-server – Low PLE on NUMA node 000, high on 001

If you have a read-intensive query running on one NUMA node (in this case, 0), then it can experience a lower page life expectancy relative to other NUMA nodes.

That's totally normal.

To see what queries are running right now, you can use Adam Machanic's excellent sp_WhoIsActive. It's totally free. Some folks even run it every X minutes and log the data into a table so they can go back to see what was running at the time PLE nosedived.

SQL Server – NUMA Nodes, MAXDOP, and PLE Explained

SQL SERVER 2005 SP3

Your best bet first is to upgrade it to a supported SP - First Download SP4 then Download CU3 --> Build NO: 9.00.5266. Support for SQL server 2005 is ending next year (2016/04/12) even for the latest SP and CU - so better upgrade to a 2012/2014.

You can use my script to help you suggest a good MAXDOP value.

Can anyone explain to me the impact of parallel executions across NUMA nodes, is it just the same as exhausting worker threads and slower access time when using foreign memory ?

SQL server is NUMA aware. This means that SQL server knows what NUMA node the processors are in and what NUMA node the memory is in. Due to this, SQL server will assign worker thread on correct NUMA node for the data that you want to access.

Refer to :

Below are some queries that will help you find out if there is an imbalance of schedulers between the NUMA nodes - which can lead to significant performance problems under load :

SELECT 
  parent_node_id,
  scheduler_id, 
  [cpu_id], 
  is_idle, 
  current_tasks_count, 
  runnable_tasks_count, 
  active_workers_count, 
  load_factor
FROM sys.dm_os_schedulers
WHERE [status] = N'VISIBLE ONLINE';

--- check the current tasks, runnable tasks, active workers and avg load factor COUNT ...
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
WHERE [status] = N'VISIBLE ONLINE'
GROUP BY parent_node_id;

You should monitor SQL Server:Buffer Node and SQL Server, Memory Node as opposed to just PLE - monitor the individual Buffer Node:Page life expectancy counters (there will be one Buffer Node performance object for each NUMA node).

exhausting worker threads

IF you set the MAXDOP setting as default (which is 0) then it could lead to worker thread starvation.

Best Answer

Related Solutions

Sql-server – Low PLE on NUMA node 000, high on 001

SQL Server – NUMA Nodes, MAXDOP, and PLE Explained

Related Question