To NUMA or not to NUMA

 

There is always some confusion when it comes to NUMA and few information on which are Microsoft recommended best practices especially when dealing with SQL Server.

 

In this post I've tried to summarize the main considerations you should do concerning Hardware and Software NUMA - and some useful articles to further learn about this if you want to.

 

1) First of all,  do you have Hardware NUMA on your box?

 

Easy to find out if you have SQL 2005.

 

Just run the following DMV:

SELECT DISTINCT memory_node_id

FROM sys.dm_os_memory_clerks

 

If SQL Server returns only a single memory node (node 0), either the hardware NUMA is not present or the hardware is configured as interleaved (non-NUMA). If you think your hardware NUMA is configured incorrectly, contact your hardware vendor to enable NUMA.

 

Remember though that SQL Server ignores NUMA configuration when hardware NUMA has four or less CPUs and at least one node has only one CPU.

 

2) Let's say that you have hardware NUMA then. When should you consider changing the default configuration?

 

As a rule of thumb, if you have Hard NUMA, just one SQL Server instance, and no other major application on your box, you don't in general have to implement specific settings on your box. Automatically the system will load balance across the NUMA nodes and you will benefit of hard NUMA characteristics as locality of memory, CPU access, number of I/O and lazy writer threads.

 

 If you have, say more than 1 SQL instance or other major application on your box, you might want to provide extra resources to the most important one(s). With hard NUMA you can associate a single port to multiple nodes therefore providing improved performance for a priority application .

 

3)  What about soft NUMA then?

 

"You usually configure soft-NUMA when you have many CPUs and do not have hardware NUMA, but you can also use soft-NUMA to subdivide hardware NUMA nodes into smaller groups".

https://msdn2.microsoft.com/en-us/library/ms178144.aspx

 

Last but not least, for the best NUMA experience use SQL Server 2005!

 

"SQL Server 2000 through Service Pack 3 does not include special support for NUMA; however, Service Pack 4 has some limited NUMA optimizations. SQL Server 2005 has many substantial improvements, and users of NUMA are strongly encouraged to upgrade to SQL Server 2005 to take full advantage of NUMA architecture" (https://msdn2.microsoft.com/en-us/library/ms180954.aspx)

 

Useful resources:

Understanding Non-uniform Memory Access

https://msdn2.microsoft.com/en-us/library/ms178144.aspx

 

How SQL Server 2005 Supports NUMA

https://msdn2.microsoft.com/en-us/library/ms180954.aspx

 

SQL Server 2005 NUMA support & troubleshooting

https://blogs.msdn.com/slavao/archive/2005/08/02/446648.aspx

 

A new platform layer in SQL Server 2005 to exploit new hardware capabilities and their trends

https://blogs.msdn.com/slavao/articles/441058.aspx

 

- Beatrice Nicolini -