Below is the complete guide to setting SQL Server affinity:

SQL Servers affinity tells the server to use specific processors to perform a certain task.
For example, affinity I/O mask can be used to configure sql server processors affinity to Input output operations.

How to set the affinity in sql server?

SQL Servers affinity on a multiprocessor system can be set by using sp_configure.

Sample script:
[sql]
sp_configure ‘show advanced options’, 1
GO
sp_configure
[/sql]

When you execute the above script you will be able to find 5 items for affinity:

• affinity I/O mask – For setting sql server disk I/O affinity to cpu’s on 32bit processor
• affinity mask – For SQL Server affinity to cpu’s on 32bit processor
• affinity64 I/O mask – For setting sql server disk I/O affinity to cpu’s on 64bit processor
• affinity64 mask – For SQL Server affinity to cpu’s on 64bit processor

Changing the values using sp_cofigure

Below table gives you the details of the values that can be set and the processors used by sql server:

Configured value	Allow SQL Server on processors number
1	0
3	0 , 1
7	0, 1, 2
15	0, 1, 2, 3
31	0, 1, 2, 3, 4
63	0, 1, 2, 3, 4, 5
127	0, 1, 2, 3, 4, 5, 6

Below piece of code changes the affintiy mask value to 3:
[sql]
sp_configure ‘show advanced options’, 1
GO
reconfigure
GO
sp_configure ‘affinity mask’, 3
GO
reconfigure
GO
[/sql]
Message: Configuration option ‘affinity mask’ changed from 0 to 3. Run the RECONFIGURE statement to install

If you have set an invalid value as per your processor configuration you get the error below error:

Msg 5832, Level 16, State 2, Line 1
The affinity mask specified does not match the CPU mask on this system.

Link

Reference : Devi Prasad (sqlserverlearner.com)