3

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?

enter image description here

enter image description here

  • 1
    Is it a virtual machine? – Stijn Wynants Aug 10 '17 at 11:26
  • You might want to review this answer and look at few more counters beside PLE. – SqlWorldWide Aug 10 '17 at 11:36
  • What is output of select top 5 (virtual_address_space_committed_kb/1024) as virtual_address_space_committed_MB, (locked_page_allocations_kb/1024) locked_page_allocations_MB, (pages_kb/1024) [memory allocated MB] from sys.dm_os_memory_nodes and SELECT scheduler_id, cpu_id, current_tasks_count, runnable_tasks_count, current_workers_count, active_workers_count, work_queue_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255; . How many NUMA nodes you have ? – Shanky Aug 11 '17 at 7:51
  • @StijnWynants, no it is a physical cluster. The behavior is the same on both cluster nodes. – Claus Bruun Aug 14 '17 at 8:02
  • @Shanky, I have two NUMA nodes. I have edited the original questinon with the output of the two selects. – Claus Bruun Aug 14 '17 at 8:10
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.