Sql 2005 runs my query on a single CPU and thus is slow

I have a query which joins 6 tables, produces 800,000 rows, and inserts them into a table.

I'm running this query on Sql 2005 Standard on an 8-core machine, on which there is no other workload running. The Sql service uses only one CPU core while running this query (using that CPU for 100%), and this way the query runs for almost 4 minu开发者_如何学JAVAtes.

How could I make my query use all the available CPUs?

Rebuilding the statistics did not help. And there were times, when this query used all the CPU-s and run considerably faster.

(The query also uses two scalar TSQL functions, but this should not be a problem, since it was not before. These functions only use their parameters in the calculations, and they do not access any tables from the database. So using these functions should not prevent the parallelization of the query.)

(Could turning the "read committed snapshot isolation" on on the database cause this behavior? The query ran all right before I turned this on, and it is running on a single CPU since then.)

Have a look at the query plan, probably it will give you some insight on what's happening. You could also post the query and its plan (in text mode) here for others to analyze.

Try forcing another transaction isolation level (SET TRANSACTION ISOLATION LEVEL READ COMMITTED) and comparing query plans - it will teel you whether changing default isolation caused the problem.

Just in case, you might want to check max degree of parallelism option (it could be set as a server-wide option or as a MAXDOP hint for a particular query).