Introduction to NUMA schemas in SQL Server

SQL Server Data tools–business Intelligence for Visual Studio 2012 installation Prompts "The CPU architecture ..." solution

For now, the rapid increase in CPU frequency and the rapid growth of the number of CPUs have not been able to promote the CPU's speed of access to memory.

Although the L3 cache was proposed to solve some of the problems, but the CPU access to memory slow phenomenon has not changed, bottlenecks still exist.

In order to solve the problem of CPU access memory more effectively, the industry introduced the NUMA concept

First, we introduce the NUMA architecture, as shown in the following figure:

Each NUMA node (hardware numa or software NUMA) has a related I/O completion port for processing network I/O, which helps distribute network I/O processing across multiple ports

The above structure shows two NUMA nodes, each NUMA node has some CPUs, an internal bus, and its own memory, and can even have its own IO. Each CPU has a direct access to its closest memory.

Therefore, the system will perform better with the NUMA architecture. It is noteworthy that under the NUMA structure, the number of CPUs can be increased more conveniently.

Under non-NUMA architecture, increasing the CPU can cause the system bus load to be heavy and the performance improvement is not obvious.

While each CPU can access memory on other NUMA nodes, the cost is to slow it down, which is to be avoided as much as possible.

Applications that are unaware of this structure, sometimes perform worse on NUMA machines because they often unconsciously access remote memory, causing overall performance degradation.

Typically, NUMA schemas also have hardware and software points.

First, hardware Numa

Hardware NUMA is supported on a hardware level. How do you know if there is hardware NUMA on this machine? The best way is to ask the hardware vendor.

But if you want to know how many NUMA nodes there are in the machine, you can use the following query under SQL Server Management Studio to see if you can return several NUMA nodes

1SELECTDISTINCT memory_node_id from Sys.dm_os_memory_clerks

Alternatively, you can view the error log for SQL Server, as the following error log indicates that the system has two NUMA nodes

After SQL Server SP3, SQL Server begins to support the NUMA schema, and memory Access uses the most recent memory from the CPU to improve performance.

Second, the software Numa

If the hardware itself does not support NUMA, you can also set NUMA at the software level, such as a machine with 4 CPUs, set to two Numa NODE

One node consumes CPU 0x11 (binary encoding) and the other node consumes CPU 0x1100 (binary encoding).

Then, you can make the following modifications on the registry, with SQL Server 2008 as an example: