How is performance impacted by having too many NUMA nodes?

2894816411_01dc38281c_zHaving too many NUMA nodes can lead to some really strange CPU behavior. What you’ll probably see is that the CPU workload of the machine isn’t evenly balanced across the CPUs. If for example you have 32 cores on the VM, and you have 16 NUMA nodes, with two cores per NUMA node you’ll probably see two cores (might be four, or six it depends on a lot of factors) running very hot compared to the other cores on the server.

At one client I was working with in 2015 they had a server which was configured very similarly to what I’ve described above. They had 32 cores, with 11 NUMA nodes. One NUMA node has 4 cores, the next 9 had three cores each, while the 11th NUMA node had one core in it.

When monitoring the performance on the server what we saw was that three cores were running at 100% (or close to it) while the other cores were running at about 4%.

The reason for this, is that SQL Server is NUMA aware, and it was trying to get the best performance possible out of the configuration that it had access to.

The solution in this case was to reconfigure the virtual machine so that it had only two vNUMA nodes. We did this by changing the number of virtual sockets to 2, and put 16 virtual cores on each virtual socket. Once this was done CPU load on the server was much more balanced across the cores. Now instead of 3 cores running at 100% 16 cores were handing the same workload (that workload was still isolated to a single NUMA node, but now the node had 16 cores in it) but the CPUs were bouncing around the 15-20% range. Suddenly the box was happier, and more importantly the application which the end users was using was behaving much better and queries were responding much faster as they no longer needed to wait for a CPU to become available.

Now you may be asking yourself how a system could end up with a screwy NUMA configuration like this. And the answer is pretty simple. The system grew over time, and more and more cores were added to the server little by little as the years went by. As the system needed more CPU resources more cores were added, but no attention was paid to how those cores were presented to Windows. So we ended up with Windows seeing 32 cores in a very strange configuration.

Because most SQL Servers are running inside VMs these days, we need to be careful how we configure those VMs to ensure that we get the proper configuration of the virtual machine. If we don’t have the correct configuration of the virtual hardware there’s little hope of getting good performance out of the VM.

Check your NUMA node configuration to make sure that it’s correct and if it isn’t correct it. Especially if the configuration is very odd looking. And if you aren’t sure, check with us, we’re happy to help.

Denny

The post How is performance impacted by having too many NUMA nodes? appeared first on SQL Server with Mr. Denny.

Contact the Author | Contact DCAC

How can you tell how many NUMA nodes your SQL Server has?

Back in the good old days (the late 1990s) you didn’t need to worry about things like NUMA unless you were running the top 0.001% of workloads. Today however even a fairly small server can have multiple NUMA nodes. NUMA configurations aren’t just for physical SQL Servers either. Virtual Machines can have NUMA configurations as well (typically called vNUMA).

There’s a few different ways of figuring out how many NUMA nodes you have in your server. If you are logged onto the server via RDP or on the physical console you can find this just by opening task manager. Once task manager is open go to the Performance tab (if it isn’t visible select the “More Details” button at the bottom) and select the CPU graph. You should see a graph on the right. Right click on that graph and select “Change graph to”, then you should see a NUMA node option. If the NUMA node option is greyed out, then you have one NUMA node. If it isn’t greyed out then select the option and however many graphs you see is how many NUMA nodes you have. It should look something like this.

CPU

The second option is to use SQL Server Management Studio. Connect to the server using the object explorer. Right click on the instance in the object explorer and select the CPU tab. Expand the “ALL” option. However many NUMA nodes are shown is the number of NUMA nodes that you have as shown below.

SSMS

You can even expand each NUMA nodes to see which logical processors are in each NUMA node.

The next way to see what your NUMA configuration is, is to look at the ERRORLOG file. Every time SQL Server is started it will log the NUMA configuration towards the beginning of the ERRORLOG file. That line will look something like this example.

Server Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

For each NUMA node there is in the server there will be a line in the ERRORLOG file. The CPUs which are in the NUMA node are shown based on the values in the CPU mask and the Active CPU mask. In this case the server has a single NUMA node with 8 cores in the NUMA node. How do we get a value of 000000ff for 8? This is hex, so the first core is 1, the second core is 2, the third core if 4 and the fourth code is 8. Add those up and you get 15. Because we’re dealing with hex, we count 1 through 9, then to get beyond 9 we use letters. A is 10, B is 11, C is 12, D is 13, E if 14 and F is 15 (yes F is supposed to equal 16 but if we start counting at 1 and skip 0 it works, so work with me; all you need to remember is that F means 4 CPUs). In almost all deployments the number of CPUs will be some multiple of 4 so you’ll end up with either 0s or Fs. If there’s other values in there that might be ok, or it might not. More digging will be required.

You can also use T-SQL (or performance monitor) to see how many NUMA nodes there are. By looking at the sys.dm_os_performance_counters DMV you can see how many NUMA nodes SQL sees. The query below will give you the exact number. For every row it returns there will be one NUMA node.

select *

from sys.dm_os_performance_counters

where object_name = ‘SQLServer:Buffer Node’

and counter_name = ‘Page life expectancy’

How many NUMA nodes is too many? Like most questions, that’s a pretty loaded question. If you have more than 4 then odds are something is configured incorrectly. If you have more than 8 I can pretty much guarantee that something in configured incorrectly. If you have one or two, that’s probably OK. The general rule of thumb that I tell people is that if you have more than 2 it needs to be reviews because it’s probably wrong. The most that I’ve seen is 16 NUMA nodes on a box, and that starts going some REALLY strange things. But more on that on another day.

For now, let’s just be happy that we know how many NUMA nodes are too many, and how to figure out how many we have.

Denny

The post How can you tell how many NUMA nodes your SQL Server has? appeared first on SQL Server with Mr. Denny.

Contact the Author | Contact DCAC

How many NUMA nodes should I have if I have lots of RAM and just a few cores?

2894816411_01dc38281c_zThere are some systems out there which have a lot of RAM, but only a few processors and these machines may need a non-standard NUMA configuration in order to be properly setup. For this example, let’s assume that we have a physical server with 512 Gigs of RAM and two physical NUMA nodes (and two CPU sockets). We have a VM running in that machine which has a low CPU requirement, but a large working set. Because of this we have 4 cores and 360 Gigs of RAM presented to the VM.
Now the default configuration for this would be to have a single NUMA node. However this isn’t going to be the best configuration for the server. The reason that this isn’t the best possible configuration is because all the memory which is allocated to the VM can’t fit within a single NUMA node. Because of this fact we need to tell the hypervisor that we want to split the four cores into two separate NUMA nodes which will allow the hypervisor to split the memory across the two physical NUMA nodes evenly, presenting two NUMA nodes to the guest with 180 Gigs of RAM from each NUMA node. (How you do this depends on the hypervisor that you’re using.)

Once this is done SQL will now know which NUMA node the memory and CPUs are assigned to, and it will correctly place work onto the correct CPU based on the NUMA node which contains the data which the work (query) needs to access.
Now every machine should not be configured this way if there is a small number of cores. The only time this becomes an issue is when there is less physical RAM per NUMA node than we are presenting to the guest OS.
How do we know how much RAM there is per NUMA node? You’ll probably need to ask your server team. The general rule is RAM/CPU Sockets. In the example above we have 512 Gigs of RAM with two CPU Sockets. In modern servers each CPU socket is usually its own NUMA node, however this may not be the case in the servers you are working with. And the CPU sockets only count if there is a processor physically in the socket.

Hopefully this helps clear up some things on these servers with these odd configurations.

Denny

The post How many NUMA nodes should I have if I have lots of RAM and just a few cores? appeared first on SQL Server with Mr. Denny.

Contact the Author | Contact DCAC

Recommended reading from mrdenny for June 06, 2014

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: RechousaPT also known as Pedro Martins

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny

Contact the Author | Contact DCAC
1 2

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers