Doubt about 'NUMA' (non uniform memory access) in Microsoft SQL Server

This concept is not a concept of SQL Server, or MySQL, it is actually a concept that comes with hardware architecture and the database needs to be able to support (or take better advantage of) these hardware resources. >

This concept comes from a trend that our current hardware has to have more than one processor, as a result you will realize that each processor has its communication bus, including memory, so they receive their own channels. communication (I / O) and its own portion of memory (we call it a node-node).

Processors from different nodes can access the memory of other nodes, but logically with different times and forms, which leads to non-uniform memory access .

When you run a Query, SQL Server needs to allocate memory for execution, so it uses a set of mechanisms to ensure that it has the right amount of memory for the query, and if it does not, it puts it in a queue, which varies according to the required memory size (if I'm not mistaken).

There is a counter in the Memory Manager Object that tells you the amount of queries that are waiting for memory, if you have a memory problem, the counter will be high, the good even zero :) < p>

So, in fact, depending on the hardware you have, you end up having to understand a bit of the architecture available to adjust the performance of the database server, either by modifying hardware or configurations on the server.

Much of these metrics, including NUMA-node memory allocation can be analyzed via Query (see SQL Internals) and SQL Server versions higher than 2008, have explicit improvements based on the number of nodes available in hardware. Server Internals).

Material about this in Pt-br is difficult, but this link gives you a very basic view of the features and clarifies the basics of the architecture and what SQL uses: link

To better understand, or to go deeper into how memory access behavior can be optimized, see:

link

I found a great material on Linux + MySQL that covers the concepts: link