Total Pageviews

Sunday, September 22, 2013

NUMA - Memory and MAXDOP settings

It is common practice to put more hardware power to alleviate the application performance issues rather than fixing the issues. For short life and non-mission critical applications, it makes sense. But for the mission critical applications that benefit from upgraded hardware, it does not last long. Fixing application issues become more obvious and important. At the same time, tweaking the server configuration is also necessary to operate the Database server flawlessly.

Modern hardware, such as NUMA based server technology, has a tremendous capability to process application requests faster than SMP architecture. Microsoft SQL Server is fully capable of using the NUMA architecture and taking advantage of it. Starting from SQL 2000 SP4, Microsoft supports Hardware NUMA and in each release, support for the NUMA architecture is getting enhanced.

NUMA and Memory Setting:
In a NUMA based system, memory setting (min server memory and max server memory) plays an important role. It is generally the best practice to configure memory in such a way that allocated memory is distributed evenly across all NUMA nodes. This will help each NUMA node to operate independently without demanding memory from other nodes. Accessing memory on another NUMA node is called “remote memory access” and accessing memory on the same NUMA node is called “local memory access”. Accessing different node for memory introduces latency.

To get best out of the NUMA system, the following settings are highly recommended:

1.      Lock Pages in Memory: The SQL Server Service account needs to have “Lock Pages in Memory” in place in the Windows local security policy. This will prevent paging out SQL Server memory back to Windows.

2.      Max and Min Server Memory: Max and Min server memory will need to be equal for two reasons:

(a)   This will reduce overhead (allocation and de-allocation) that would otherwise be used by SQL Server dynamically managing these values.
(b)   As Memory calculation is usually derived from “Max Server Memory”, SQL Server Engine will have better values to allocate physical memory evenly to each NUMA node. This will reduce “Foreign Memory” requirement that occurs during data processing on one node.

3.      MAXDOP: For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node. Meaning that if a server has 4 NUMA nodes and each NUMA node consists with 4 CPUs then MAXDOP will be 4 or less. This will reduce threading overhead that occurs which will then be utilizing more NUMA nodes simultaneously.

Memory Allocation in each NUMA node:
To learn how much memory each node has received, PerfMon or sys.dm_os_perfromance_counters can be used. Following is a buffer allocation from an 8 node NUMA system.

DMV Query:
select  counter_name ,
        cntr_value * 8 / 1024 node_memory_mb ,
from    sys.dm_os_performance_counters
where   [object_name] like '%Buffer Node%'
        and counter_name like 'Total Pages%'
order by instance_name
compute sum(cntr_value * 8 / 1024 )

select  counter_name ,
        cntr_value * 8 / 1024  total_buffer_mb ,
from    sys.dm_os_performance_counters
where   [object_name] like '%Buffer Manager%'
        and counter_name like 'Total Pages%'
order by instance_name

CPU Usages in each NUMA Node:

Some disadvantages:
Although NUMA architecture is increasing processing power, there are some usage patterns which introduce some Latch contention in 32+ cores. In that case, database schema design such as index needs to be reviewed. A detailed guideline can be found in Microsoft’s technical document paper: “Diagnosing and Resolving Latch Contention on SQL Server”.

If overtime “Foreign Pages” counter is high for one or more nodes, this usually means that the nodes require more memory to perform a particular workload. While adding more memory might help, it is recommended to see if the query can be optimized along with index changes.

Read More:
SQL Server and Large Pages Explained

Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

SQL Server, Buffer Node Object

Diagnosing and Resolving Latch Contention on SQL Server

How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks)

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.