SQL Server Performance

Assign instances to specific cores?

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by Kai, Oct 15, 2007.

  1. Kai New Member

    Hello, everyone. With multiple instances, I wanted to know if it is possible to assign a specific SQL instance to utilize a specific core on a multi-core processor system?
    Thanks in advance.
  2. satya Moderator

    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.
    BOL clearly states that:SQL Server administrators can use the /msdn.microsoft.com/mshelp" />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.
  3. NorthHinkle New Member

    As the previous poster stated, you can assign an instance to use particular processors by setting the SQL Affinity mask. However, this means that instance will never take advantage of the other processors, even if they are idle.
    With multiple instances, if you do not set affinity mask, nor "light weight threads", nor "boost priority", then you can control CPU utilization through Windows System Resource Manager (WSRM) which is freely downloadable from the MS web site. The advantage of WSRM is it implements soft caps; that is, an instance is only limited to using a specific amount when there are other active threads (i.e. it will allow an instance to use more processing time if the processors would be idle otherwise).

Share This Page