SQL SERVER – Discussion on understanding NUMA

At the SQLPass conference I have had the opportunity to meet a number of you and you folks have inspired me to learn and share after every trip to this conference. This year, I was fortunate to do a number of sessions this year too. Just the preparation for these session give me an opportunity to learn more than ever.

When I was doing the day long 42 tips, during the lunch break there was an interesting conversation that is worth a share. One of the attendee walked up to me and asked an interesting question:

Attendee: Hey, thanks for the blog every single day.

Pinal: Thanks, glad to hear from you and hope you are learning something today too.

Attendee: Couldn’t agree more Pinal. I like the way the blog simplifies some of the concepts.

Pinal: Oh thanks again. I think that is the best way we can learn together.

Attendee: BTW, I have been wanting to ask you a simple question.

Pinal: Sure, let me try to give it a shot. Tell me.

Attendee: What is NUMA and I don’t quite get where to start. And when was it introduced with SQL Server?

Pinal: Sure, let me give you a high level understanding first and then I will blog.

Attendee: Oh, that would be great. Tell me more about it.

I started the conversation like:

Over the years, symmetric multiprocessing (SMP) architecture has been very popular. A common characteristic of SMP architecture is that all CPUs share a single front bus that is used to access memory, manage tasks and threads. This front bus becomes a point of contention, making it very difficult to scale.

Now with this understanding, NUMA addresses this issue very effectively. In NUMA, we have multiple nodes (localized structures) that make up the larger machine, and each node has its own bus. This minimizes front bus contention, leading to better scalability than SMP architecture offers. However, in NUMA each node also has its own local memory. So if a node has to access non-local (foreign) memory, it can be multiple times slower when compared to local memory access. An application that is NUMA-aware can address this issue by doing most of its work on the local node. I will give you MSDN links for reference.

Understanding Non-uniform Memory Access

NUMA Scenarios

Now to answer the second part of the question – From SQL Server 2005 it is fully NUMA aware. While 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 are running later versions of Windows. That is to cover some of the history.

I had to move out for another serving for lunch but this conversation suddenly came up to my mind yesterday that I thought would be great to share.

Reference: Pinal Dave (https://blog.sqlauthority.com)