Sunday, November 27, 2011

Tips: How to configure Parallel Processing in SQL Server 2008

In order to use the Parallel Processing, it is required to have a lot of calculations to determine it. Usually, the 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, we can’t use the certain types of statements for paralleling. For example, UPDATE, INSERT, and DELETE are not normally processed in parallel even if the related query meets the criteria. At the same time, 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.

Follow the following steps in order to configure the parallel processing:

1.    First, go to the Advanced page in the Server Properties dialog box.
2.    The value is 0 for the Max Degree Of Parallelism setting. It means that the maximum number of processors used for parallel processing is controlled automatically. The SQL Servers use the actual number of available processors, depending on the workload.
3.    If you wish to limit the number of processors used for parallel processing to a set amount (up to the maximum supported by SQL Server), it is required to change the Max Degree Of Parallelism setting to a value greater than 1.
4.    A value of 1 informs the SQL Server not to use the parallel processing.
5.    The large and complex queries usually can benefit from the parallel execution. However, the SQL Server performs a 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.
6.    Set the cost estimate threshold 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.
7.    After that, you can click OK.
8.    Now, these changes will be applied immediately and it isn’t required to restart the server for the settings to take the effect.
exec sp_configure "max degree of parallelism",
exec sp_configure "cost threshold for parallelism",

The above are Transact-SQL commands for using the stored procedure sp_configure to configure parallel processing.

B  y