I have a SQL2005 server with 8 procs and 16 GB of ram. When I run a heavy stored procedure it pegs out only one of the procs at 99.9%. How can I spread the load across multiple procs?
Guess what? It's another "depends" answer!
Although parallel execution is automatic (if the correct options are set), it is not as straightforward as you might think. Take a look at this MSDN article titled "Parallel Query Processing" for more information:
Here's a quick quote from the beginning of the article:
The SQL Server query optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:
- The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
- A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
- The query contains scalar or relational operators that cannot be run in parallel. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.
If you still have questions after reading the article, please give us some more information about your specific situation:
Fantastic information posted by Tom.
After you read everything he posted, take a look at your cost threshold for parallelism on the server. You could lower that, but that means that other procedures may also start to run parallel and they might not benefit. In fact, you could hurt the other procedures performance. Messing with the cost threshold for parallelism, especially on an OLTP server, especially if you're thinking about lowering it... that will require serious testing. Keep an eye on your cx_packet wait stats. If they go up a lot, you might be seeing excessive parallel operations.
No one has followed this question yet.