How To Configure Processor Affinity Mask in SQL Server 2005

Under heavy system loads, configuring which processor should run a specific thread can improve performance by reducing the number of times the processor cache is reloaded. The association between a processor and a thread is called processor affinity.

 Provision to change the processor affinity mask was introduced in SQL Server 2005. The affinity mask setting controls the number of CPUs used by the SQL Server for query execution.

The default value of affinity mask setting is '0' which indicates parallel execution of all available logical CPUs.

How to set processor affinity:


To configure the affinity mask using GUI, following steps are to be carried out.

1. Open Microsoft SQL Server Management Studio.
2. Right click the server and then click properties.
3. Server properties dialog box opens.
4. In 'Select a Page' section, click the processor tab.
5. Uncheck the 'Automatically set processor affinity mask for all processors'.
6. Now, check boxes under "Processor Affinity" will be enabled.
7. Uncheck the processor you wanted to exclude.

As shown in the figure, CPU1 is selected. The script to enable CPU1 alone will be as follows.

EXEC sys.sp_configure 'show advanced options', '1'; 

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure 'affinity mask', '2';

RECONFIGURE WITH OVERRIDE

GO

The following query will return the value of the affinity mask setting in your SQL Server.

SELECT * FROM sys.configurations

WHERE NAME = 'AFFINITY MASK'

Determining Affinity Value:

Consider for example, if your server have 8 CPUs. If processors 1,2,5 are selected as available then bits 1,2,5 is set to1 and bits 0,3,4,6,7 are set to 0 as shown in the following table.

Processors	Bits	Binary
CPU7	Bit7	0
CPU6	Bit6	0
CPU5	Bit5	1
CPU4	Bit4	0
CPU3	Bit3	0
CPU2	Bit2	1
CPU1	Bit1	1
CPU0	Bit0	0

Therefore the binary would be 0010 0110 and its decimal value can be calculated as follows:

0010 0110
= 0*(2^7) + 0*(2^6) + 1*(2^5) + 0*(2^4) + 0*(2^3) + 1*(2^2) + 1*(2^1) + 0*(2^0)
= 0*(128) + 0*(64) + 1*(32) + 0*(16) + 0*(8) + 1*(4) + 1*(2) + 0*(1)
=0 + 0 + 32 + 0 + 0 + 4 + 2 + 0
=38

Therefore, decimal value '38' will be used with affinity mask command as follows:

EXEC sys.sp_configure 'show advanced options', '1'; 

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure 'affinity mask', '38';

RECONFIGURE WITH OVERRIDE

GO

In actual practice, changing the affinity mask setting only rarely helps performance gains. It may frequently degrade the performance. Hence it is better to leave with the default setting '0'.


Question:

1. You are administering SQL Server 2005 with 8 processors. You are supposed to optimize the server so that longer running queries can run parallel plans. What will you do?

A. Set the max degree of parallelism to 1.
B. Set the cost threshold for parallelism to 0.
C. Set the affinity mask to 0.
D. Set the AWE enabled setting to 0.

Show Answers:

1. (C).

Hide Answers (X).