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:
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 ,
instance_name
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 ,
instance_name
from sys.dm_os_performance_counters
where [object_name]
like '%Buffer Manager%'
and counter_name like 'Total Pages%'
order by instance_name
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