Hyper-Threading and SQL Server's Affinity Mask Option

The affinity mask option, available through sp_configure, lets you control which processors SQL Server uses. If you use the affinity mask option on a machine that uses Intel's Hyper-Threading technology, you might get unanticipated behavior. If your machine has four physical processors and you enable Hyper-Threading, the OS will see eight logical processors. SQL Server assigns a separate User Mode Scheduler (UMS) for each processor in the system. The UMS works with the OS to schedule SQL Server threads. Normally, each processor has one UMS.

Table 1 shows a map of logical processor number to physical processor. The execution context on physical processor 0 serves logical processors 0 and 4. Setting affinity mask equal to a value of 00010001 tells SQL Server to use logical processors 0 and 4, which are using the same physical processor. In this case, you might think you were setting affinity mask to use two processors when you're in fact using a single physical processor. Affinity mask is an advanced setting that you don't need to change for most installations. However, recognizing the implications of Hyper-Threading is important if you take advantage of the affinity mask option.