Allocating Threads to a CPU

By default, each instance of SQL Server starts each thread. The operating system then assigns each thread to a specific CPU. The operating system distributes threads from instances of SQL Server evenly among the microprocessors, or CPUs on a computer. Sometimes, the operating system can also move a thread from one CPU with heavy usage to another CPU.

SQL Server administrators can use the affinity mask configuration option to exclude one or more CPUs from being eligible to run threads from a specific instance of SQL Server. The affinity mask value specifies a bit pattern that indicates the CPUs that are used to run threads from that instance of SQL Server. For example, the affinity mask value 13 represents the bit pattern 1101. On a computer that has four CPUs, this indicates that threads from that instance of SQL Server can be scheduled on CPUs 0, 2, and 3, but not on CPU 1. If affinity mask is specified, the instance of SQL Server allocates threads evenly among the CPUs that have not been masked off. Another effect of affinity mask is that the operating system does not move threads from one CPU to another. However, affinity mask is rarely used. Most systems obtain optimal performance by letting the operating system schedule the threads among the available CPUs.