10 SQL Server NUMA Nodes Best Practices
SQL Server NUMA nodes can be a complex topic. This article covers the 10 best practices for working with NUMA nodes in SQL Server.
In a SQL Server environment, it is important to understand how NUMA nodes are used and configured. NUMA nodes are a way of grouping together physical processors and memory so that each node has its own dedicated resources.
Configuring SQL Server NUMA nodes correctly can help to improve performance and avoid potential issues. In this article, we will discuss 10 best practices for configuring SQL Server NUMA nodes.
1. Use the same number of NUMA nodes as physical CPU sockets
If you have a four-socket server with two NUMA nodes, each NUMA node will only be able to access half of the total system memory. This can lead to performance issues because SQL Server may have to wait for data to be brought in from other NUMA nodes, leading to increased latency.
Additionally, using more NUMA nodes than physical CPU sockets can also lead to performance issues. This is because when SQL Server tries to access data that’s spread across multiple NUMA nodes, it has to send requests to each node individually. This can again lead to increased latency and decreased performance.
2. Keep SQL Server instances to a single NUMA node
When SQL Server is running on a NUMA system, it will try to keep all of the data and code for a given database within the same NUMA node. This is because accessing data that is local to a NUMA node is much faster than accessing data that is located on a different NUMA node.
However, if you have multiple SQL Server instances running on the same server, each instance may end up using data and code from different NUMA nodes. This can lead to performance problems, as the SQL Server instance will have to access data from multiple NUMA nodes, which is slower than accessing data from just one NUMA node.
Therefore, it’s best to keep each SQL Server instance on its own NUMA node. This way, each SQL Server instance will only use data and code from its own NUMA node, which will improve performance.
3. Don’t use more than 64 logical processors on a single instance
If you have more than 64 logical processors on a single instance, the SQL Server process will be spread across multiple NUMA nodes. This can lead to performance degradation because of increased memory and CPU contention.
To avoid this issue, make sure to configure your SQL Server instances with the correct number of NUMA nodes for the number of logical processors they have. You can use the sys.dm_os_nodes dynamic management view to check the number of NUMA nodes on your SQL Server instance.
4. Avoid using Hyper-Threading (HT) with SQL Server
When SQL Server is running on a NUMA node, it will try to keep all of its threads within that node. However, if HT is enabled, each logical processor appears as two NUMA nodes to SQL Server. This can cause SQL Server to spread its threads across multiple nodes, which can lead to performance issues.
Therefore, it’s best to disable HT when using SQL Server on a NUMA node. You can do this in the BIOS or by using the following Windows Registry setting:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management\NumaAware=0
5. Consider the impact of memory allocation and max server memory settings
If you have a SQL Server instance with more than one NUMA node and you’re not using the max server memory setting, then SQL Server will allocate an equal amount of memory to each NUMA node. This can lead to sub-optimal performance because some NUMA nodes may end up with more free memory than others.
To avoid this, it’s important to use the max server memory setting. This ensures that SQL Server will only allocate as much memory to each NUMA node as is needed, which can help improve performance.
It’s also important to consider the impact of memory allocation on NUMA nodes when configuring SQL Server. For example, if you have a four-socket server with two NUMA nodes, you should configure SQL Server to use half of the available memory on each NUMA node. This ensures that each NUMA node has an equal amount of memory to work with, which can help improve performance.
6. Monitor for NUMA node performance issues
NUMA node performance issues can often be the root cause of SQL Server performance problems. If you’re not monitoring for NUMA node performance issues, you may not be aware that there’s a problem until it’s too late.
The best way to monitor for NUMA node performance issues is to use the Windows Performance Monitor tool. You can find detailed instructions on how to do this in the “Monitoring NUMA Node Performance” section of the SQL Server NUMA Best Practices white paper.
Once you’re monitoring for NUMA node performance issues, you’ll be able to quickly identify and resolve any problems that arise. This will help keep your SQL Server running smoothly and avoid any potential performance issues.
7. Check the affinity mask configuration setting
The affinity mask setting tells SQL Server which NUMA nodes it can use. If the affinity mask is not configured correctly, SQL Server will not be able to use all of the available NUMA nodes, and this can lead to performance problems.
To check the affinity mask setting, open the SQL Server Configuration Manager and go to the SQL Server Network Configuration > Protocols for
8. Review your hardware configuration
NUMA nodes are created by dividing up physical processors and memory into smaller groups. Each NUMA node has its own dedicated processor and memory, which gives it better performance than if it had to share those resources with other NUMA nodes.
However, not all hardware is created equal. Some hardware configurations are better suited for NUMA nodes than others. For example, Intel processors with more than eight cores per socket are generally better suited for NUMA nodes than AMD processors.
It’s important to review your hardware configuration to make sure it’s optimally configured for NUMA nodes. If it’s not, you may not be getting the best possible performance from your SQL Server.
9. Always test changes in a non-production environment
If you’re not familiar with NUMA, it stands for Non-Uniform Memory Access. In a nutshell, it’s a way of configuring SQL Server so that it can take advantage of multiple processors and multiple cores.
The reason why it’s important to test changes in a non-production environment is because there’s a lot of potential for things to go wrong when you’re working with NUMA nodes. For example, you might accidentally configure SQL Server to use more NUMA nodes than your server has available, which can cause performance problems.
Or, you might make a change that causes SQL Server to use a different NUMA node for some of its data than it does for other data, which can also lead to performance issues.
Testing in a non-production environment gives you a chance to try out different configurations and see how they affect performance before you implement them in production. That way, you can avoid any potential problems and ensure that your SQL Server is running as efficiently as possible.
10. If you need help, ask an expert!
If you’re not familiar with SQL Server NUMA nodes, they can be a bit tricky to configure. There are a lot of different settings that can impact performance, and it’s easy to make a mistake that can cause serious problems.
Asking an expert for help ensures that your SQL Server NUMA nodes are configured correctly and optimally for your environment. It’s worth the investment to get it done right the first time, and avoid any potential issues down the road.
