SQL Server A to Z – NUMA


Back to our ABCs for a bit. Today we’re talking about Non-Uniform Memory Access, aka NUMA.

What is NUMA?

Once upon a time, servers only had a few, relatively slow processors. And these processors all used the same shared memory bus to access system memory. But because there were so few processors and because they were slower than the memory on the server this worked ok. Over time, however, processors got faster, and soon they were operating faster than the server memory. So, the processors ended up spending a lot of time waiting for memory calls to complete. To compensate for this, manufacturers started making processors with fast cache built in. This helped limit the number of accesses to the main server memory, and everyone was happy again. For a while. But applications kept getting larger and it wasn’t long before that cache just wasn’t enough. Processors needed access to more and more memory. Servers were being built with more and more processors. And all of this was still running over that same single shared memory bus. Can you see where that might be a problem? This is called symmetric multiprocessing, or SMP.

What NUMA does is split the processors into smaller groups, or nodes, that each have their own memory bus and subset of main memory. The memory associated with a particular node is referred to as local memory. All the nodes are also interconnected, so one node can access memory attached to another node, called remote memory. Accessing remote memory, however, is slower than accessing local memory, hence the “non-uniform” thing.

Hard or Soft?

With hardware NUMA, all this is done via special hardware containing physically separate system buses, each with its own physical set of processors, memory, and sometimes even separate IO channels.

But if you don’t have hardware NUMA or if you want to further subdivide your hardware NUMA nodes, you can configure what’s called soft-NUMA. Why would you do this? When using NUMA, SQL Server creates an IO thread and a lazywriter thread for every NUMA node. So by configuring soft-NUMA, you can still see some benefit by reducing IO and lazywriter contention.

How do I know if I’m using NUMA?

Connect to your SQL Server instance and run the following query:

SELECT DISTINCT memory_node_id
FROM sys.dm_os_memory_clerks

If you only get back one row, you’re not using NUMA. If you know your server has hardware NUMA, it may be that it’s configured to use interleaved memory instead, effectively turning your NUMA system back into an SMP system. Or it could be that your sysadmin configured it wrong. (I keed, I keed!)

Further Reading

Wikipedia: Non-Uniform Memory Access

Understanding Non-Uniform Memory Access

NUMA Frequently Asked Questions

How to: Configure SQL Server to Use Soft-NUMA

Also recommended:

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>