• evyatar36

HOW NUMA nodes might help you -

One day I received a message from one of my clients who said he is experiencing HIGH Duration of queries.


Together we tried to look for queries which cause this issue, but without any luck.

so, I changed my perspective and looked for HARDWARE issues, bad configurations and something that can tell us why the server is running as is.


Then I noticed that the memory was cut into Half, never in use. out of 128 GB only 64 GB were in use, in SQL SERVER 2014 STANDARD Edition. what caused it, as I figured out, was a license limitation of SQL Server and it prevented the CPU processors from being exposed to the Memory Node, hence, allowing the server to use only the memory which the NUMA was exposed to.


in short - the less CPU processors in each NUMA is exposed to the memory node, the less memory it can use.

after adjusting the value of the processors for each NUMA, we immediately noticed the memory usage raise to its full potential, giving SQL SERVER more "Air" to breath and we get immediate response from the client for a better performance experience.


SO, what are NUMA NODES(SOFT NUMA)?

NUMA stands for Non-Uniform Memory Access, The purpose of NUMA is for Scheduler (CPU) to have faster access to memory. Each CPU talks to memory controller to get the memory. When there are more than 8 CPUs, single memory controller becomes bottleneck. This became a scalability issue. NUMA is a hardware configuration which was built to scale beyond the processor and memory limitations of traditional Symmetric-Multi-Process (SMP) systems.


for more information, you can use the next links:


  1. https://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide#understanding-non-uniform-memory-access

  2. https://sqlhands.com/2020/09/23/automatic-soft-numa/


0 comments

STAY IN TOUCH

Get New posts delivered straight to your inbox

Thank you for subscribing!