Online Documentation for SQL Manager for SQL server

Processor options

The Processors section of the Server Properties dialog allows you to configure the instance of Microsoft® SQL Server by setting options pertaining to processors usage in groups: Enable processors, Threads, Parallelism.

Enable processors

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 set processor affinity mask (also known as CPU affinity mask) and Automatically set I/O affinity mask for all processors.

Threads

Use the Maximum worker 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.

Use the Boost SQL Server priority option to specify whether SQL Server should run at a higher Windows 2000 or Windows 2003 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 or Windows Server 2003 scheduler. The default is 0, which is a priority base of 7.

Select the Use Windows fibers (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.

Parallelism

Use the Cost threshold for parallelism option to specify the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in Cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. Only set Cost threshold for parallelism on symmetric multiprocessors.

Use the Max number of locks option to set the maximum number of available locks, thereby limiting the amount of memory the Database Engine uses for them. The default setting is 0, which allows the Database Engine to allocate and deallocate lock structures dynamically, based on changing system requirements. When the server is started with locks set to 0, the lock manager acquires sufficient memory from the Database Engine for an initial pool of 2,500 lock structures. As the lock pool is exhausted, additional memory is acquired for the pool. Allowing SQL Server to use locks dynamically is the recommended configuration.

Use the Query wait option to specify the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, or if –1 is specified, then the time-out is calculated as 25 times of the estimated query cost.

In Microsoft SQL Server, memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. The query times out after a set time calculated by SQL Server (25 times the estimated cost of the query) or the time specified by the nonnegative value of the query wait.

When SQL Server 2005 (and higher) runs on a computer with more than one CPU, it detects the best degree of parallelism for each instance of a parallel query execution. You can use the Max degree of parallelism option to limit the number of processors to use in parallel plan execution. The default value of 0 indicates that all available processors are used. Set Max degree of parallelism to 1 to suppress parallel plan generation. Set the value to a number greater than 1 (up to a maximum of 64) to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, or in case the Use all processors option is selected, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.