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.
- Open Microsoft SQL Server Management Studio.
- Right click the server and then click properties.
- Server properties dialog box opens.
- In 'Select a Page' section, click the processor tab.
- Uncheck the 'Automatically set processor affinity mask for all processors'.
- Now, check boxes under "Processor Affinity" will be enabled.
- 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
EXEC sys.sp_configure 'affinity mask', '38';
RECONFIGURE WITH OVERRIDE
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:
