Enabling engine-to-CPU affinity

By default, there is no affinity between CPUs and engines in Adaptive Server. You may see slight performance gains in high-throughput environments by establishing affinity of engines to CPUs.

Not all operating systems support CPU affinity; on such systems, the dbcc tune command is silently ignored. You must reissue dbcc tune each time Adaptive Server is restarted. Each time CPU affinity is turned on or off, Adaptive Server prints a message in the error log indicating the engine and CPU numbers affected:

Engine 1, cpu affinity set to cpu 4.
Engine 1, cpu affinity removed.

The syntax is:

dbcc tune(cpuaffinity, start_cpu [, on | off])

start_cpu specifies the CPU to which engine 0 is to be bound. Engine 1 is bound to the CPU numbered (start_cpu + 1). The formula for determining the binding for engine n is:

((start_cpu + n) % number_of_cpus

Valid CPU numbers are 0 – the number of CPUs minus 1.

On a four-CPU machine (with CPUs numbered 0 – 3) and a four-engine Adaptive Server, this command:

dbcc tune(cpuaffinity, 2, "on")

Gives this result:

Engine

CPU

0

2

(the start_cpu number specified)

1

3

2

0

3

1

On the same machine, with a three-engine Adaptive Server, the same command causes the following affinity:

Engine

CPU

0

2

1

3

2

0

CPU 1 is not used by Adaptive Server.

To disable CPU affinity, use -1 instead of start_cpu, and specify off for the setting:

dbcc tune(cpuaffinity, -1, "off") 

Enable CPU affinity without changing the value of start_cpu by using -1 and on for the setting:

dbcc tune(cpuaffinity, -1, "on")

The default value for start_cpu is 1 if CPU affinity has not been previously set.

To specify a new value of start_cpu without changing the on/off setting, use:

dbcc tune (cpuaffinity, start_cpu)

If CPU affinity is currently enabled, and the new start_cpu differs from its previous value, Adaptive Server changes the affinity for each engine.

If CPU affinity is off, Adaptive Server notes the new start_cpu value, and the new affinity takes effect the next time CPU affinity is turned on.

To see the current value and whether affinity is enabled, use:

dbcc tune(cpuaffinity, -1) 

This command prints only current settings to the error log and does not change the affinity or the settings.