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

improvement. Try to come up with a test that delivers a specific metric that you can factor against the
cost to deliver a clear indication of the cost benefit of larger cache.

SystemArchitecture
Another key purchasing decision has to be around the machine architecture. For systems up to eight pro-
cessors there really isn't much choice. Single-socket, dual-socket, quad-socket, and even most eight-way
systems are only available in Symmetric Multi-Processing (SMP) configuration. It's only when you move
to 16-, 32-, or 64-way systems that you need to consider the options of SMP versus NUMA. This isn't
something you really need to configure but rather something you need to understand as an option when
considering the purchase of one type of system over another.

SMP
Symmetric multi-processing (SMP) is a computer architecture where multiple processors are connected
to a single shared memory through a system bus. This is the most common computer architecture today
and allows for any processor to work on any task regardless of where in memory the data is located.
Windows and SQL Server have been designed to take advantage of this by splitting work into separate
tasks where possible so that work can be evenly distributed between processors. The downside, which
you already read about at the start of this section,isthatmemoryismuchslowerthantheprocessors
accessing it and SMP compounds the problem because only one processor can access memory at a time.
To try and lessen the performance hit processors use local cache as described above. Figure 6-12 shows
the relationship between memory and CPU in an SMP-based server.

Memory

System Bus

CPU 0 CPU 1 . . . CPU n

Figure 6-12

SMP is an efficient architecture where you have a small number of CPUs but when you start counting
them in dozens the system bus becomes a bottleneck and you have to look at alternatives.

NUMA
Non-Uniform Memory Access (NUMA) is an architecture designed to exceed the scalability
of SMP. The SMP architecture connects the memory to all the processors symmetrically via a shared bus
whereas the NUMA architecture has multiple system buses and limits the number of processors on
each system bus to four. Each group of four processors with its own system bus is called a node and
each node has access to its own pool of local memory. Each node is connected to every other node's
memory through a high-speed interconnect, but access to this ''foreign'' memory is considered to be
four times slower than access to local memory. Figure 6-13 shows the relationship between nodes in a
NUMA-configured server.

The goal on a NUMA system is to maximize the amount of data that you get from the local memory rather
than the memory from another node but applications need to be NUMA-aware to take advantage of this.
Windows 2003 and SQL Server 2005 have been designed to be NUMA-aware but SQL Server 2000 is not.
If you have to run SQL Server 2000 on a NUMA machine it should be configured as interleaved-NUMA,
which I will explain momentarily.