SQLSERVERLEARNER

table.hovertable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #999999;
border-collapse: collapse;
}
table.hovertable th {
background-color:#c3dde0;
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}
table.hovertable tr {
background-color:#d4e3e5;
}
table.hovertable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}

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:

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