affinity mask

The affinity mask in SQL Server is a setting that determines the processor affinity for SQL Server processes, specifying which CPUs (processor cores) SQL Server should use. It helps in controlling the CPU utilization by SQL Server and can be used to optimize performance in certain scenarios. The affinity mask is specified as a bitmask, where each bit represents a CPU core that SQL Server can use.

There are primarily two types of affinity masks in SQL Server:.

1. CPU Affinity Mask:
• This type of affinity mask specifies which individual CPUs SQL Server can use. Each CPU is represented by a bit in the bitmask, where 1 indicates that SQL Server can use that CPU, and 0 indicates that it cannot.
2. Node Affinity Mask:
• In NUMA (Non-Uniform Memory Access) architectures, SQL Server can also use node affinity masks to specify which NUMA nodes it can use. Each node is represented by a bit in the bitmask, similar to CPU affinity.

Configuring the affinity mask involves setting the appropriate bitmask values to specify CPU or node affinity for SQL Server. This configuration can be done using the following methods:

1. Using SQL Server Management Studio (SSMS):

§  Connect to the SQL Server instance using SSMS.

§  Right-click on the server instance in Object Explorer and select "Properties."

§  In the "Server Properties" dialog box, navigate to the "Processor" page.

§  Under "Processor affinity," check or uncheck the CPUs that you want SQL Server to use.

§  Click "OK" to save the changes.

2. Using Transact-SQL (T-SQL):
• Use the sp_configure system stored procedure to set affinity mask options.
• For CPU affinity:

EXEC sp_configure 'affinity mask', <mask_value>; RECONFIGURE;

• For node affinity:

EXEC sp_configure 'affinity64 mask', <mask_value>; RECONFIGURE;

3. Using Windows Task Manager:
• On Windows servers, you can also configure CPU affinity for SQL Server using the Task Manager.
• Open Task Manager, go to the Details tab, right-click on the SQL Server process, and set CPU affinity to specify which CPUs it can use.

Microsoft provides the following recommendations regarding affinity mask settings:

4. Avoid Changing Default Settings:
• In most cases, it's recommended to let SQL Server use all available CPUs by not explicitly setting affinity mask values unless there are specific reasons or performance requirements necessitating CPU affinity configuration.
5. Use with Caution:
• Changing affinity mask settings should be done cautiously and only after thorough testing in a non-production environment.
• Incorrect affinity mask configurations can lead to CPU resource contention, performance issues, and suboptimal SQL Server performance.
6. Consider NUMA Architectures:
• If your server uses NUMA architecture, consider node affinity settings to optimize memory and CPU utilization within NUMA nodes.
7. Consult Microsoft Documentation:
• For detailed guidance and best practices on affinity mask configuration, refer to Microsoft's official documentation and resources, such as the SQL Server documentation and support articles.

The values for affinity mask are as follows:

·        A one-byte affinity mask covers up to 8 CPUs in a multiprocessor computer.

·        A two-byte affinity mask covers up to 16 CPUs in a multiprocessor computer.

·        A three-byte affinity mask covers up to 24 CPUs in a multiprocessor computer.

·        A four-byte affinity mask covers up to 32 CPUs in a multiprocessor computer.

·        To cover more than 32 CPUs, configure a four-byte affinity mask for the first 32 CPUs and up to a four-byte affinity64 mask for the remaining CPUs.

Note

·       Affinity support for servers with 33 to 64 processors is only available on 64-bit operating systems.

·        In SQL Server, the affinity mask option can be configured without requiring a restart of the instance of SQL Server.

With the following query you can also check on which CPU cores a specific query (based on the SPID) can be executed.

SELECT r.session_id, t.affinity FROM sys.dm_exec_requests r

JOIN sys.dm_os_workers w ON w.task_address = r.task_address

JOIN sys.dm_os_threads t ON t.worker_address = w.worker_address

WHERE r.session_id = 53

Example

As an example of setting the affinity mask option, if processors 1, 2, and 5 are selected as available with bits in positions 1, 2, and 5 set to 1 and bits 0, 3, 4, 6, and 7 set to 0, a hexadecimal value of 0x26 or the decimal equivalent of 38 must be used. Number the bit positions from right to left..

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'affinity mask', 38;

RECONFIGURE;

GO

For More details:

affinity mask (server configuration option) - SQL Server | Microsoft Learn