Hp ProLiant DL980 G7 Server User Manual download pdf (Page 41)

CPU page: Viewing and enabling CPU assignment optimization recommendations

The CPU page (see Figure 36 (page 41)) displays SQL Server affinity settings and recommendations

for each SQL instance in the system. To carry out multitasking, Microsoft Windows sometimes

moves process threads among different processors. 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. Assigning processors to specific threads can improve

performance under these conditions by eliminating processor reloads and reducing thread migration

across processors (thereby reducing context switching). Such an association between a thread and

a processor is called processor affinity.

HP ESO generally recommends that you confine SQL instances with relatively light workloads to

under-utilized CPUs. This results in some SQL instance affinities being assigned an unusually high

order, or non-contiguous logical CPU number. Similarly, SQL Server and the operating system

automatically manage the CPU affinities of highly-utilized SQL instances. This logic results in less

conflict over CPU resources among under- and highly-utilized SQL instances.

Figure 36 CPU page

The following subsections describe parameters pertaining to CPU affinity and performance

optimization.

SQL Server affinity

On multi-processor systems, SQL Server interaction with CPU resources can be controlled by setting

the affinity mask options. Configuring these options results in SQL Server threads being scheduled

to run on the same processor each time they run. If you do not configure these options, the threads

can migrate among processors. In SQL Server 2008 R2, you can set SQL Server Affinity to a

maximum of 256 CPUs. In older versions of SQL Server, this setting is limited to only 64 CPUs.

Optimizations pages 41

1 2 ... 36 37 38 39 40 41 42 43 44 45 46 ... 56 57

No comments

Hp ProLiant DL980 G7 Server User Manual Page 41