Intra-query parallelism

Question

Post reply

Intra-query parallelism

EBrownIT

Grasshopper

Points: 14
More actions
September 24, 2003 at 8:22 am

#101683

When I run a particular stored procedure I get this error message "Intra-query parallelism caused your server command (process ID #35) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1)"
I have added the option(maxdop 1) to the end of the stored procedure, but now it times out.
Does anyone out there know what to do?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

NPeeters SSChampion Points: 12536 More actions · Answer 1

Are you running your query on a SMP machine? How many processors are you using (without the maxdop 1 hint)?

Can you post the query? Maybe there are some optimisations that one of the guru's here can point out...

smills Grasshopper Points: 10 More actions · Answer 2

Your server probably has dual processor.

A fix is to change a setting:

In Enterprise manager select server and right click to properties.

On the processor tab look at the parallelism section.

Change option to use only 1 processor.

This should fix it. I will spare the details.

NPeeters SSChampion Points: 12536 More actions · Answer 3

Is the effect of maxdop 1 not the same as setting the parallelism to 1 processor?

Or do you suspect that there are problems with other queries that use multiple processors?

EBrownIT Grasshopper Points: 14 More actions · Answer 4

We worked around the problem by changing the stored procedure. I will have to check the setting on the server parallelism. Thanks for the advice.

smills Grasshopper Points: 10 More actions · Answer 5

In my situation an exe was calling a function. Only on some machines running the exe did I get the error message and they did not receive the error 100% of the time. more-over the problem could not be duplicated consistently on all of our test machine. With over 100 functions the safe bet was to change the setting.

Checking the profile shows that SQl Server was changing the plan it was using to run the function.

I am aware of any ways to force MS SQL to use a specific plan to avoid this whole mess.

James Darnell SSC-Addicted Points: 452 More actions · Answer 6

We had an issue with this today at work as well. Does anyone happen to know if there is any specific cause for this error? It just seems odd to me that the error is thrown with some queries but not with others. Is there a specific part of a query, (join, index usage, etc) that can set this off? We ended up using the query hint option (maxdop 1) for now.