Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mallikarjuna_
Starting Member
20 Posts |
 Posted - 2004-02-27 : 09:49:11
|
| Hi, Can someone help me on the Intra Query Paralellism problem we are facing. The query is a dynamically generated query, so we cannot use option maxdop(1). The query is likeselect table1.col1,table1.col2, table1.col3..table1.colnfrom table1, table2, table3where table1.col1 = table2.col1and table1.col3=table3.col2and table1.col5 in ('200301','200302','200303'....)When I just specify the last condition astable1.col5 in ('200301') the query returns the output but from when the conditions is table1.col5 in ('200301','200302') the query starts throwing the Intra Query Paralellism error.Table1 is actually a view.Any one please help on this.Thnks & Rgds,Mallik |
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2004-02-28 : 01:09:03
|
| You can set the max degree of parallelism using sp_configure.The topic is covered in BOL. You set it 1 to suppress parallel plan generation. You can set it it a value greater than 1 to limit the number of processors used for a single query. i.e If you have a 4 processor system you could set it to 2 and any single query will use only 2 processors.I recommend you read BOL to make sure you understand all the ramifications of changing this parameter.Lance Harra |
 |
|
|
mallikarjuna_
Starting Member
20 Posts |
Posted - 2004-02-28 : 06:16:34
|
| Well, we want to use all the four processors. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-28 : 15:58:39
|
| Can you send the exact error. Also, why are you generating the query dynamically? If it's just for the multiple values in the where clause, you can use a table function that accepts a comma-delimited string. You can then just join to that. It would give you the best of both worlds. You would have your dynamic capabilities. At the same time, you would have compiled code with a good execution plan, and be able to control the MAXDOP().MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-28 : 16:11:51
|
| Setting max degree of parallelism through sp_configure doesn't mean you won't use all processors, it only means that no more than "X" processors will be used in parallel for any single operation or query. Other queries can make use of the other CPUs at the same time. |
|
|
|
mallikarjuna_
Starting Member
20 Posts |
Posted - 2004-03-03 : 02:19:32
|
| Thanks for the help robvolk. Was under the impression that setting max degree of parallelism to 2 would make sql server to use only 2 CPUs. |
|
|
|
|
|
|
|
|
Intra Query Paralellism