Learn About Manually Tweaking Parallel Processing Settings

Article
08/31/2016

Tip: Learn About Manually Tweaking Parallel Processing Settings

Follow Our Daily Tips

• facebook.com/TechNetTips
• twitter.com/TechNetTips
• blogs.technet.com/tnmag

A lot of calculations are required to determine whether parallel processing should be used. Generally, SQL Server processes queries in parallel in the following cases:

When the number of CPUs is greater than the number of active connections
When the estimated cost for the serial execution of a query is higher than the query plan threshold (The estimated cost refers to the elapsed time in seconds required to execute the query serially)

However, certain types of statements cannot be processed in parallel unless they contain clauses. For example, UPDATE, INSERT, and DELETE operations are not normally processed in parallel even if the related query meets the criteria. But if the UPDATE or DELETE statements contain a WHERE clause, or an INSERT statement contains a SELECT clause, WHERE and SELECT can be executed in parallel. Changes are applied serially to the database in these cases.

You can configure parallel processing by completing the following steps:
1. In the Server Properties dialog, go to the Advanced page.
2. By default, the Max Degree Of Parallelism setting has a value of 0, which means that the maximum number of processors used for parallel processing is controlled automatically. Essentially, SQL Server uses the actual number of available processors, depending on the workload. To limit the number of processors used for parallel processing to a set amount (up to the maximum supported by SQL Server), change the Max Degree Of Parallelism setting to a value greater than 1. A value of 1 tells SQL Server not to use parallel processing.
3. Large, complex queries can usually benefit from parallel execution. However, SQL Server performs parallel processing only when the estimated number of seconds required to run a serial plan for the same query is higher than the value set in the cost threshold for parallelism. Set the cost estimate threshold by using the Cost Threshold For Parallelism box on the Advanced page of the Server Properties dialog box. You can use any value from 0 through 32,767. On a single CPU, the cost threshold is ignored.
4. Click OK. These changes are applied immediately. You do not need to restart the server.

You can use the stored procedure sp_configure to configure parallel processing. The commands are as follows:

T-SQL

exec sp_configure "max degree of parallelism", <integer value> 
    exec sp_configure "cost threshold for parallelism", <integer value>

Windows PowerShell

Invoke-Sqlcmd -Query "exec sp_configure 'max degree of parallelism',
    <integer value> exec sp_configure 'cost threshold for parallelism',
    <integer value>" -ServerInstance "Server\Instance"

From the Microsoft Press book Microsoft SQL Server 2008 Administrator’s Pocket Consultant, Second Edition by William R. Stanek.

Looking for More Tips?

For more tips on Microsoft products and technologies, visit the TechNet Tips library.

Additional resources