Processor or I/O Affinity Set

Setting processor affinity for SQL Server can potentially cause performance issues. Processor affinity determines which CPUs a particular thread or process can run on. If the affinity is set incorrectly, it can result in uneven distribution of processing power and reduced performance. It’s important to carefully consider the implications of setting processor affinity and to monitor performance after making changes.

What Is the Affinity Mask Server Configuration Option?

Processor affinity for SQL Server is the ability to define which processor cores the SQL Server engine will use for multitasking. By assigning specific processor cores, SQL Server only uses those processors for multitasking database operations. This can improve performance by reducing processor reloads and thread migration across processors.

On the other hand, I/O affinity binds Microsoft SQL Server disk I/Os to a specified subset of CPUs. This means that you can specify which CPUs will handle disk input/output operations for SQL Server. This can help improve performance by reducing contention for resources between different processes.

Suggested Action

Ensure that both processor affinity and I/O affinity are set to AUTO.

How to Reset Processor Affinity Back to Auto

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO
GO
RECONFIGURE
GO

How to Reset I/O Affinity Back to Auto

EXEC sys.sp_configure @configname = N'show advanced options',
                      @configvalue = N'1';
RECONFIGURE;
GO
EXEC sp_configure 'affinity I/O mask', 0;
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure @configname = N'show advanced options',
                      @configvalue = N'0';
RECONFIGURE;
GO

Further Reading

​Affinity Mask Server Configuration Option | Microsoft Learn