affinity mask Option

In Microsoft® Windows NT® 4.0 and Windows® 2000, an activity (thread) in a process can migrate from processor to processor, with each migration reloading the processor cache. Under heavy system loads, specifying which processor should run a specific thread can improve performance by reducing the number of times the processor cache is reloaded. The association between a processor and a thread is called processor affinity.

Use the affinity mask option to increase performance on symmetric multiprocessor (SMP) systems (with more than four microprocessors) operating under heavy load. You can associate a thread with a specific processor and specify which processors Microsoft SQL Server™ will use. You can exclude SQL Server activity from processors given specific workload assignments by the Windows NT 4.0 or Windows 2000 operating system.

If you set a bit representing a processor to 1, that processor is selected for thread assignment. When you set affinity mask to 0 (the default), the Windows NT 4.0 or Windows 2000 scheduling algorithms set the thread's affinity. When you set affinity mask to any nonzero value, SQL Server affinity interprets the value as a bit mask that specifies those processors eligible for selection. Excluding SQL Server threads from running on particular processors helps evaluate the system's handling of processes specific to Windows NT 4.0 or Windows 2000. For example, you can use affinity mask to evaluate whether an additional network interface card (NIC) increases performance or assess NIC performance with increasing loads.

Because using SQL Server processor affinity is a specialized operation, it is recommended that SQL Server processor affinity be used only when necessary. In most cases, the Windows NT 4.0 or Windows 2000 default affinity provides the best performance.

Before you change the setting of affinity mask, keep in mind that Windows NT 4.0 and Windows 2000 assign deferred process call (DPC) activity associated with NICs to the highest numbered processor in the system. In systems with more than one installed and active NIC, each additional card's activity is assigned to the next highest numbered processor. For example, an eight-processor system with two NICs has DPCs for each NIC assigned to processor 7 and to processor 6.

Note  You can use System Monitor (Performance Monitor in Windows NT 4.0) to view and analyze individual processor usage.

For example, if processors 1, 2, and 5 are selected as available with bits 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 is specified. Number the bits from the right to left. The rightmost bit is bit 0. Set bits 1, 2, and 5 (the third, fifth, and sixth bits) to 1. The number calculated from setting the specified bits is binary 00100110, which is decimal 38 or hexadecimal 0x26.

These are affinity mask values for an eight-processor system.

affinity mask is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change affinity mask only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.

To configure the affinity mask