One day I received a message from one of my clients who said he is experiencing a HIGH Duration of queries.
Together we tried to look for queries that 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 to which the NUMA was exposed.
In short - the fewer 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 breathe. As a result, we get an 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 memory access. Each CPU talks to the memory controller to get the memory. When there are more than 8 CPUs, a single memory controller becomes the bottleneck. This became a scalability issue. NUMA is a hardware configuration 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:
Kommentare