question

Robert L Cole avatar image
Robert L Cole asked

Threading and Stored Procedures

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?

stored-proceduresparallelism
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

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:

http://msdn.microsoft.com/en-us/library/ms178065.aspx

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:

  • stored procedure code
  • execution plan
  • current average execution time
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.