Online Documentation for SQL Administrator for SQL Server

Parallelism

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

Use the Query plan 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 Query plan 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 Query plan threshold for parallelism on symmetric multiprocessors.

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 Maximum degree of parallelism in query execution 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 Maximum degree of parallelism in query execution 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 Unlimited option is selected, the actual number of available processors is used. If the computer has only one processor, the Maximum degree of parallelism in query execution value is ignored.

Use the Maximum number of available 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 by checking the Unlimited option is the recommended configuration.

Use the Query wait for resources before timing out in seconds 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. The Use time-out calculated as 25 times of the estimated query cost option has the same effect.

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.