sql server affinity mask setting

Posted on : 23-04-2012 | By : Devi Prasad | In : Configuring SQL Server, SQLServerPedia Syndication

Share:

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:

sp_configure 'show advanced options', 1
GO
sp_configure

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

SQL Server affinity Mask Configuration

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:

sp_configure 'show advanced options', 1
GO
reconfigure
GO
sp_configure 'affinity mask', 3
GO
reconfigure
GO

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

(Visited 357 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

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.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,