Online Documentation for SQL Administrator for SQL Server

Processor

The Processor section of the Server Properties dialog allows you to configure the instance of Microsoft® SQL Server by setting options pertaining to processors usage.

To carry out multitasking, it is possible to distribute process threads among different processors. Although efficient from an operating system point of view, this activity can reduce SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads and reducing thread migration across processors (thereby reducing context switching). Such an association between a thread and a processor is called processor affinity.

You can enable any or both two affinity mask options supported by SQL Server 2005 (and higher): Automatically  configure CPU affinity mask and Automatically configure IO affinity mask.

Use the Maximum number of working threads option to configure the number of working threads available to Microsoft® SQL Server processes. SQL Server uses the native thread services of the Microsoft® Windows 2000 and Windows Server 2003 operating systems so that one or more threads support each network that SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users.

To enable editing this field uncheck Self-configuring option.

Use the Run SQL Server at a higher scheduling priority option to specify whether Microsoft SQL Server should run at a higher Microsoft Windows 2000, Windows Server 2003, Windows 2008, or Windows 2008 R2 scheduling priority than other processes on the same computer. If you set this option to 1, SQL Server runs at a priority base of 13 in the Windows 2000, Windows Server 2003, Windows 2008, or Windows 2008 R2 scheduler. The default is 0, which is a priority base of 7.

Select the Windows fiber mode (lightweight pooling) option to provide a means of reducing the system overhead associated with the excessive context switching sometimes seen in symmetric multiprocessing (SMP) environments. When excessive context switching is present, lightweight pooling can provide better throughput by performing the context switching inline, thus helping to reduce user/kernel ring transitions.