Configuring Processor and I/O Affinity [SQL SERVER 2012]

Configuring Processor and I/O Affinity

       Processor affinity assigns specific server processors to specific threads. This eliminates processor reloads and reduces thread migration across processors.

         I/O affinity binds an instance’s disk I/O to a specific set of CPUs. You configure processor and I/O affinity on the Processors page of an instance’s Server Properties dialog box.The default setting for each instance is to configure processor affinity mask and I/O affinity mask automatically for all processors on the host server.

           When configuring affinity through SQL Server Management Studio, you cannot configure both processor affinity and I/O affinity for the same processor.

      It is possible to configure processor affinity and I/O affinity for the same processor by using Transact-SQL, although this is not recommended and will decrease performance.

             To distribute SQL worker threads across CPUs 2 and 3 based on server workload, use the following Transact-SQL statement:

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3

      To distribute SQL worker threads across all CPUs based on server workload, use the following command:

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO

You can’t configure I/O affinity by using the ALTER SERVER CONFIGURATION statement
and must instead use sp_configure with the affinity I/O mask option. Microsoft recommends
that you do not modify affinity I/O mask from the default setting.