Hyper-Threading and SQL Server's Affinity Mask Option

If you use the affinity mask option on a machine that uses Hyper-Threading technology, you might get unanticipated behavior.

Brian Moran

May 19, 2003

1 Min Read
ITPro Today logo

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.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like