Databases Reference
In-Depth Information
Hardware NUMA
To prevent memory bus contention problems and enhance overall performance, newer systems have
incorporated more than one system bus, each accommodating one or more processors. Each processor
group has its own memory, called local memory. Processors within the group use the local memory
first. This group is called one NUMA node. Processors within one node can, when necessary, access
memory in a foreign node, but local memory access is significantly faster than accessing memory in a
foreign node.
SQL Server 2005 is NUMA-aware. At service startup, SQL Server 2005 detects the underlying hardware
architecture and will automatically take advantage of the underlying hardware design. You do not need
to do anything special, unlike with SQL Server 2000, where you have to turn on a trace flag to use it.
Software NUMA
As mentioned earlier, if your server box is of NUMA design, then SQL Server automatically configures
itself to take advantage of that feature. However, for non-NUMA hardware such as traditional SMP
boxes, if you have multiple processors to play with, you can still divide processors into different nodes,
thus allowing SQL OS and SQL Server network interface to use them properly. That can potentially
alleviate I/O and lazywriter (a periodic process that checks and evaluates the status of Buffer Pool
buffers) bottlenecks.
For example, suppose you have eight SMP processors. By default, there will be one single lazywriter
and one single I/O thread for all eight processors. In NUMA, each node has its own single
lazywriter and I/O thread. If you sub-divide those eight processors into four soft-NUMA nodes, you will
gain three additional I/O threads and three additional lazywriter threads, which could increase your
server performance.
Soft-NUMA is configured within the registry. Following are the registry entries for the previous
example's configuration:
[HKEY_LOCAL_MACHINE
\
SOFTWARE
\
Microsoft
\
Microsoft SQL
Server
\
90
\
NodeConfiguration]
[HKEY_LOCAL_MACHINE
\
SOFTWARE
\
Microsoft
\
Microsoft SQL
Server
\
90
\
NodeConfiguration
\
Node0]
"CPUMask"=dword:00000003
[HKEY_LOCAL_MACHINE
\
SOFTWARE
\
Microsoft
\
Microsoft SQL
Server
\
90
\
NodeConfiguration
\
Node1]
"CPUMask"=dword:0000000C
[HKEY_LOCAL_MACHINE
\
SOFTWARE
\
Microsoft
\
Microsoft SQL
Server
\
90
\
NodeConfiguration
\
Node2]
"CPUMask"=dword:00000030
[HKEY_LOCAL_MACHINE
\
SOFTWARE
\
Microsoft
\
Microsoft SQL
Server
\
90
\
NodeConfiguration
\
Node3]
"CPUMask"=dword:000000C0
Note that you can configure soft-NUMA on both SMP and NUMA hardware. In other words, you can
divide the hardware nodes even further into soft-NUMA nodes. One important thing to remember is that
the soft-NUMA node should be fully contained with the hardware NUMA node.
When soft-NUMA is configured, you can use network connection affinities to distribute different appli-
cation loads to different processors. See the section ''Network Connection Affinity'' later in this chapter
on how to set that up.
Search WWH ::
Custom Search