NUMA in SQL Server: A Lunch Chat About Local and Remote Memory

NUMA in SQL Server was the topic of a lunch break chat at the SQLPass conference. I met many of you there, and you inspire me to learn and share after every trip. This year I also did a number of sessions. Preparing for them taught me more than ever.

The Lunch Break Question

During my day long 42 tips session, an attendee walked up to me at lunch and asked an interesting question. They thanked me for the blog every single day and said they like how it simplifies concepts. I thanked them and said that this is the best way for us to learn together.

Then came the question. What is NUMA, where should they start, and when did SQL Server get it? I said I would give a high level answer first and write a blog post after that.

SMP and the Front Bus

I started with symmetric multiprocessing (SMP). For years SMP architecture has been popular. In SMP, all CPUs share a single front bus. The bus is used to access memory and to manage tasks and threads. It becomes a point of contention, which makes SMP hard to scale.

How NUMA Fixes It

NUMA addresses this problem well. The machine is made of multiple nodes, which are localized structures, and each node has its own bus. That minimizes front bus contention and scales better than SMP.

Each node also has its own local memory. When a node has to access non-local (foreign) memory, the access can be multiple times slower than local memory access. An application that is NUMA-aware avoids this by doing most of its work on the local node.

Which Versions Support NUMA

Now the second part of the question. From SQL Server 2005, SQL Server is fully NUMA aware. In SQL Server 2000, support for NUMA was introduced in build 8.00.925.

NUMA support is only available on Microsoft Windows Server 2003-based servers and on servers that run later versions of Windows. That covers some of the history.

I had to leave for a second serving of lunch. The conversation came back to me recently, and I thought it was worth sharing.

NUMA is not about faster memory, it is about keeping work next to its own memory.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.