Choosing and Configuring Hardware - Professional SQL Server 2005 Performance Tuning - page 169

Local memory access

Memory

System bus

CPU 0 CPU 1 CPU 2 CPU 3

Foreign memory acces (4 local)

×

NODE 0

Memory

System bus

CPU. . CPU. . . . . CPU n

NODE 1

Figure 6-13

You can see if SQL Server is using NUMA buy running the following query or checking the startup
messages in the SQL Server errorlog:

SELECT DISTINCT memory_node_id FROM sys.dm_os_memory_clerks

The query shows the number of memory nodes that SQL Server is using. If you return a zero then you're
only using one node and no NUMA. If you're running on an AMD processor, however, you might get an
unexpected result; all AMD processor architectures (as far as I'm aware) are implemented as NUMA, so
if you have a dual-core AMD and run the above script you'll see two NUMA nodes reported.

When an application isn't NUMA aware you can configure the server to emulate an SMP architec-
ture. This means that each node doesn't distinguish between local and foreign memory. We call this
interleaved-NUMA and it is illustrated in Figure 6-14.

Memory Memory

CPU 0 CPU 1 CPU 2 CPU 3 CPU ... CPU ... CPU ... CPU n

Foreign Memory

Local Memory

Figure 6-14

Best Practice
SQL Server 2000 should only use interleaved-NUMA. I see lots of customers with SMP
servers up to 16 processors and most go for NUMA when looking at 32 + processors.

Soft NUMA
Soft NUMA is a SQL Server 2005 feature that was developed to help the Microsoft test teams to run use
case scenarios simulating NUMA hardware. It soon became apparent, however, that there were customer