HP ProLiant DL980 G7 Server - Manual (Page 50)

Figure 40 CPU page
The following subsections describe parameters pertaining to CPU affinity and performance
optimization.
NOTE: HP ESO calculates CPU allocation recommendations for all active instances. If you have
not provided valid credentials for certain instances, HP ESO displays the recommendations for
these instances only after you have logged in to them with valid credentials.
Database Engine Processor Affinity
The Database Engine Processor Affinity parameter allows you to specify the affinity mode for CPUs
on multi-processor systems: automatic affinity or manual affinity. Normally, automatic affinity is in
effect, where Microsoft Windows controls the assignment of threads to CPUs, dynamically migrating
threads among processors based on the current workload. Although this is efficient from an OS
point of view, this activity can reduce SQL Server performance under heavy system loads, as each
processor cache is repeatedly reloaded with data. Under these conditions, you can give the SQL
Server greater control over the assignment of threads, helping confine threads to specific CPUs
(the CPUs are given higher priority among other resources for these threads). This mode is referred
to as manual affinity. This improves performance by eliminating processor reloads and reducing
thread migration across processors (thereby reducing context switching).
In general, HP recommends automatic affinity (the default). However, if there is a significant
discrepancy among workloads of several instances, such as one heavily-loaded instance among
two or more other instances that are idling (lightly-loaded), you can enable manual affinity for
under-utilized CPUs on the lightly-loaded instances. This allows the OS to distribute the processor
load over a greater number of CPUs on the heavily-loaded instance. (On heavily-loaded instances,
the OS automatically manages CPU affinities.) Over all, this strategy results in less conflict for CPU
resources among under- and highly-utilized instances.
NOTE: In general, you need not determine or evaluate the workload and CPU utilization of
instances and individual CPUs. The CPU page guides you, indicating which CPUs should be
manually affinitized, if any.
In SQL Server 2008 R2, you can set Database Engine Processor Affinity (manual affinity) for a
maximum of 256 CPUs. In SQL Server 2012, you can set manual affinitization for a maximum of
640 CPUs. In older versions of SQL Server (SQL Server 2008, for example), manual affinitization
is limited to only 64 CPUs. In general, the maximum number of CPUs depends on the number of
logical processors on the system.
50 Using HP ESO