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

Question

Someone can help me understand in English the concept of 'NUMA' and its characteristics?

I have found sites in English etc, but I still do not understand!

Answer 1

This concept is not a concept of SQL Server, or Mysql, in fact it is a concept that comes from Hardware architecture and the database needs to have resources to support (or take better) these hardware resources.

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

Processors of different nodes can access the memory of other nodes, but logically with different times and forms, which leads to a 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 appropriate amount of memory for the query and if it does not, it queues it, which varies according to the required memory size (if I’m not mistaken).

There is a counter in Memory Manager Object that tells you the amount of queries that are waiting for memory, if you are having trouble with missing memory, the counter will be with a high value, the good is even zero :)

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

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

Material about this in Pt-br is difficult, but this link gives a very basic overview of resources and clarifies the basic concepts of architecture and what SQL uses: https://technet.microsoft.com/en-us/library/ms178144.aspx

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

http://www.amazon.com/Pro-Server-Internals-Dmitri-Korotkevitch/dp/1430259620/ref=sr_1_3? s=books&ie=UTF8&qid=1432705945&sr=1-3&Keywords=sql+Internals

I found a great material on Linux + Mysql that covers the concepts: http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/