Using parallelism selectively

Not all queries benefit from parallelism. In general, the optimizer determines which queries will not benefit from parallelism and attempts to run them serially. When the query processor makes errors in such cases, it is usually because of skewed statistics or incorrect costing as a result of imperfect modeling. Experience will show you whether queries are running better or worse, and you can decide to keep parallel on or off.

If you keep parallel on, and have identified the queries you want to run in serial mode, you can attach an abstract plan hint, as follows:

select count(*) from sysobjects
plan “(use parallel 1)”

The same effect is achieved by creating a query plan:

create plan “select count(*) from sysobjects”
“use parallel 1”

If, however, you notice that parallelism is resource-intensive or that it does not generate query plans that perform well, use it selectively. To enable parallelism for selected complex queries:

Set the number of worker processes to a number greater than zero, based on the guidelines in “number of worker processes”. For example, to configure 10 worker processes, execute:
```
sp_configure “number of worker processes”, 10
```
Set max query parallel degree to a value greater than 1. As a starting point, set it to what you would have used for max parallel degree:
```
sp_configure “max query parallel degree”, 10
```
The preferred way to force a query to use a parallel plan is to use the abstract plan syntax:
```
use parallel N
```
where N is less than the value of max query parallel degree.

To write a query that uses a maximum of 5 threads, use:
```
select count (*), S1.id from sysobjects S1, sysindexes S2
where S1.id = S2.id
group by S1.id
plan
“(use parallel 5)”
```
This query tells the optimizer to use 5 worker processes, if it can. the only drawback to this approach is that you must alter the actual queries in the application. To avoid this, use create plan:
```
create plan
“select count(*), S1.id from sysobjects S1, sysindexes S2
where S1.id = S2.id
group by S1.id”
“(use parallel 5)”
```
To turn the abstract plan load option on globally, enter:
```
sp_configure “abstract plan load”, 1
```
See “Creating and Using Abstract Plans” for more information about using abstract plans.