Monday, April 23, 2007

SQL Server and NUMA Architectures

Recently I was asked to write some information around SQL Server architecture that will be published as an appendix in an upcoming Microsoft Press book (I'll post more info when it is available). One of the topics that I chose to write about was how SQL Server 2005 handles Non Uniform Memory Access (NUMA) and how DBAs should really understand NUMA architectures on "high end" systems.

I was very surprised to learn that very few professional DBAs have actually heard of NUMA, and fewer even understood the ramifications of configuring SQL Server 2005. (that data is based on a very unscientific poll of people I know who are DBAs or DB developers)

What Exactly is NUMA?

If you're into reading fiction and have ever come across anything written by Clive Cussler , then you might think that NUMA stands for the "National Underwater Marine Agency", but in this case it actually stands for "Non Uniform Memory Access", which basically allows hardware manufactures to make up for the fact that processor and bus speed is starting to catch up with memory speed, and having multiple processor cores with large memory sets can actually begin to degrade the performance of applications. In a nutshell, NUMA pairs specific processors with specific regions of memory. This pairing is known as a NUMA node. For example, if you have a machine with 8GB of RAM and 4 processor cores, the NUMA configuration might end up as 2 4GB NUMA memory nodes, each hosting 2 processor cores. Each processor can access the full 8GB of RAM, but 4GB will be considered "local" memory and therefore faster than the "foreign" memory associated with the other NUMA node.

SQL Server and NUMA Configuration

Generally speaking, most SQL Server 2005 DBAs will probably not have to worry about NUMA configuration, however, it is important to note that the higher the processing load on the server, the more relevant NUMA configuration is. SQL Server 2005 provides a dynamic management view to detail how NUMA is used on the server. The following query will detail the NUMA configuration on SQL Server 2005:

SELECT DISTINCT memory_node_id FROM sys.dm_os_memory_clerks

Running this query will return the number of NUMA nodes configured. If the query returns only one row, then NUMA is not used.

There are several BOL topics and MSDN articles that are available on how to configure NUMA and SQL Server, but generally speaking they are obtuse and hard to follow.

In a follow-on post I'll walk through a NUMA configuration scenario to detail exactly how NUMA can be used to your advantage on a high-volume system.


Anonymous said...

I ran the suggested stored procedure and ended up with 4 distinct nodes, 0-3. However, the results don't detail the makeup of each node. We have a 64 bit 4 duo processor machine (making 8 procs, no?) with 20 Gb of memory. I could assume that my 4 NUMA nodes each have 2 cpus and 5 Gb of memory but how can I be sure?

Ted Malone said...

If you look in the SQL Log (The error log under the management node in Management Studio) you'll see the information presented as part of the startup text, or you can run the following query, it will tell you how the memory is broken down in your NUMA configuration:

memory_node_id AS [Memory Node],
SUM(single_pages_kb) AS [Single Pages Kb],
SUM(multi_pages_kb) AS [Multi Pages Kb],
SUM(awe_allocated_kb) AS [AWE Allocated Memory Kb]
FROM sys.dm_os_memory_clerks
GROUP BY memory_node_id;

Hope this helps.