double threading a procedure?

Over some time now, I've been developing a fairly hefty stored procedure, that does a lot of computations, and fairly few table lookups.

When I look at the performance on my server (a dual Xeon HT) I can see that it only uses 1 out of 4 possible "cpus" to work on the calculations, while the three others idle out, and was wondering if I can somehow force it to use max available CPU power?The optimizer is usually pretty good about choosing the execution plan. But it can be influenced.

Have you checked your MAXDOP setting? If it is set to 1, you can bump it up. But as with all advice, caveat executor!|||Me personally, I would have compartmentalized the sproc into smaller units of work, i.e., I would have made many sprocs instead of a mongo one.

To get the to run independantly, we have in the past set these many sprocs to be executed as jobs, then have a sproc that launches all of the jobs. The launch will be serial, but the execution would not be.

With one big sproc, everything is serial, and you are not pushing the CPU hard enough anyway. If you want additional ways to thread, you should set up your tables as partitioned views. then the CPU can thread out.

Other than that, with your methodology, there is no way where it will thread, or use additional cpus

MOO