sql server affinity mask setting
Posted on : 23-04-2012 | By : Devi Prasad | In : Configuring SQL Server, SQLServerPedia Syndication
0

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.
Reference : Devi Prasad (sqlserverlearner.com)
Do you like my blog?
If you liked reading this blog, please help spread the word by sharing this blog with your friends.
Need Help On SQL Server?
Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.

Tags: affinity I/O mask, affinity mask, affinity mask sql server, affinity mask sql server 2000, affinity mask sql server 2005, affinity mask sql server 2008, affinity mask sql server 2012, affinity mask sql server management studio, affinity64 i o mask option, affinity64 i o mask sp_configure, affinity64 i o mask sql server, affinity64 i o mask sql server 2005, affinity64 i o mask sql server 2008, affinity64 i o mask sql server 2012, affinity64 I/O mask, affinity64 mask, affinity64 mask option, affinity64 mask sql server 2008, how to configure sql server to use specific processors on a multi processor system, sql server aaffinity I/O mask, sql server affinity, sql server affinity mask, sql server affinity mask 2000, sql server affinity mask 2005, sql server affinity mask 2008, sql server affinity mask 2012, sql server affinity mask affinity i o mask, sql server affinity mask option, sql server affinity mask specified, sql server affinity mask value, sql server affinity processor, sql server affinity64 I/O mask, sql server affinity64 mask