From an operating
system point of view, the ability of Windows to move process threads among
different processors is efficient, but this activity can reduce SQL Server
performance because each processor cache is reloaded with data repeatedly.
By
setting the Affinity Mask option, you can allow SQL Server to assign processors
to specific threads and thus improve performance under heavy load conditions by
eliminating processor reloads and reducing thread migration and context
switching across processors. Setting an affinity mask to a non-0 value not only
controls the binding of schedulers to processors, but it also allows you to
limit which processors are used for executing SQL Server requests.
The value of an affinity mask is a 4-byte integer, and each
bit controls one processor. If you set a bit representing a processor to 1,
that processor is mapped to a specific scheduler. The 4-byte affinity mask can
support up to 32 processors. For example, to configure SQL Server to use
processors 0 through 5 on an eight-way box, you would set the affinity mask to
63, which is equivalent to a bit string of 00111111. To enable processors 8
through 11 on a 16-way box, you would set the affinity mask to 3840, or
0000111100000000. You might want to do this on a machine supporting multiple
instances, for example. You would set the affinity mask of each instance to use
a different set of processors on the computer.
To cover more
than 32 CPUs, you configure a 4-byte affinity mask for the first 32 CPUs and up to
a 4-byte Affinity64 mask for the remaining CPUs. Note that affinity support for
servers with 33 to 64 processors is available only on 64-bit operating systems.
You can configure the affinity mask to use all the available
CPUs. For an eight-way machine, an Affinity Mask setting of 255 means that all
CPUs will be enabled. This is not exactly the same as a setting of 0 because
with the nonzero value, the schedulers are bound to a specific CPU, and with
the 0
value, they are not.
