Allocating Threads to a CPU

SQL Server Architecture

SQL Server Architecture

Allocating Threads to a CPU

By default, each instance of Microsoft® SQL Server™ 2000 starts each thread, and then Microsoft Windows NT® or Windows® 2000 assigns each thread to a specific CPU. Windows NT or Windows 2000 distribute threads from instances of SQL Server evenly among the microprocessors, or CPUs on a computer. At times, Windows NT or Windows 2000 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 with four CPUs, this indicates 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 Windows NT and Windows 2000 do not move threads from one CPU to another. affinity mask is rarely used; most systems get optimal performance by letting Windows NT or Windows 2000 schedule the threads among the available CPUs.