Has anyone seen the following message and if so how did you go about resolving the problem?
Server: Msg 8650, Level 13, State 1, Line 20
Intra-query parallelism caused your server command (process ID #15) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).Can anyone give me a low down on Intra-query parallelism and why a quad server will get this error?
Jim
Originally posted by Deddens
Has anyone seen the following message and if so how did you go about resolving the problem?
Server: Msg 8650, Level 13, State 1, Line 20
Intra-query parallelism caused your server command (process ID #15) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
Showing posts with label intra-query. Show all posts
Showing posts with label intra-query. Show all posts
Wednesday, March 7, 2012
Intra-query parallelism caused deadlock
Hi,
I am running a select query , which used left join on 5 tables. this is
reporting server there are no data updates, only select stmts. it has been
failing at runtime , the same query is running fine with different
parameters.
It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
degree of parallelism is 0 (before i change) , when i set max degree of
parallelism to 1 , then the query did run fine. As I remeber i havent
changed any configuration recently.
The query failed with following message.
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #63) to
deadlock. Rerun the query without intra-query parallelism by using the query
hint option (maxdop 1).
Can somebody explain what caused it.
Thanks,
Subbu.
Hi!
Do please check the article at http://support.microsoft.com/?kbid=837983.
You will find possible reasons & workarounds there.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
> Hi,
> I am running a select query , which used left join on 5 tables. this is
> reporting server there are no data updates, only select stmts. it has been
> failing at runtime , the same query is running fine with different
> parameters.
> It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
> degree of parallelism is 0 (before i change) , when i set max degree of
> parallelism to 1 , then the query did run fine. As I remeber i havent
> changed any configuration recently.
>
> The query failed with following message.
> Server: Msg 8650, Level 13, State 127, Line 1
> Intra-query parallelism caused your server command (process ID #63) to
> deadlock. Rerun the query without intra-query parallelism by using the
query
> hint option (maxdop 1).
>
> Can somebody explain what caused it.
> Thanks,
> Subbu.
>
>
|||Thank you. I found it later.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:OUAafLChEHA.2916@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi!
> Do please check the article at http://support.microsoft.com/?kbid=837983.
> You will find possible reasons & workarounds there.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
been
> query
>
I am running a select query , which used left join on 5 tables. this is
reporting server there are no data updates, only select stmts. it has been
failing at runtime , the same query is running fine with different
parameters.
It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
degree of parallelism is 0 (before i change) , when i set max degree of
parallelism to 1 , then the query did run fine. As I remeber i havent
changed any configuration recently.
The query failed with following message.
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #63) to
deadlock. Rerun the query without intra-query parallelism by using the query
hint option (maxdop 1).
Can somebody explain what caused it.
Thanks,
Subbu.
Hi!
Do please check the article at http://support.microsoft.com/?kbid=837983.
You will find possible reasons & workarounds there.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
> Hi,
> I am running a select query , which used left join on 5 tables. this is
> reporting server there are no data updates, only select stmts. it has been
> failing at runtime , the same query is running fine with different
> parameters.
> It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
> degree of parallelism is 0 (before i change) , when i set max degree of
> parallelism to 1 , then the query did run fine. As I remeber i havent
> changed any configuration recently.
>
> The query failed with following message.
> Server: Msg 8650, Level 13, State 127, Line 1
> Intra-query parallelism caused your server command (process ID #63) to
> deadlock. Rerun the query without intra-query parallelism by using the
query
> hint option (maxdop 1).
>
> Can somebody explain what caused it.
> Thanks,
> Subbu.
>
>
|||Thank you. I found it later.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:OUAafLChEHA.2916@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi!
> Do please check the article at http://support.microsoft.com/?kbid=837983.
> You will find possible reasons & workarounds there.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
been
> query
>
Intra-query parallelism caused deadlock
Hi,
I am running a select query , which used left join on 5 tables. this is
reporting server there are no data updates, only select stmts. it has been
failing at runtime , the same query is running fine with different
parameters.
It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
degree of parallelism is 0 (before i change) , when i set max degree of
parallelism to 1 , then the query did run fine. As I remeber i havent
changed any configuration recently.
The query failed with following message.
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #63) to
deadlock. Rerun the query without intra-query parallelism by using the query
hint option (maxdop 1).
Can somebody explain what caused it.
Thanks,
Subbu.Hi!
Do please check the article at http://support.microsoft.com/?kbid=837983.
You will find possible reasons & workarounds there.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
> Hi,
> I am running a select query , which used left join on 5 tables. this is
> reporting server there are no data updates, only select stmts. it has been
> failing at runtime , the same query is running fine with different
> parameters.
> It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
> degree of parallelism is 0 (before i change) , when i set max degree of
> parallelism to 1 , then the query did run fine. As I remeber i havent
> changed any configuration recently.
>
> The query failed with following message.
> Server: Msg 8650, Level 13, State 127, Line 1
> Intra-query parallelism caused your server command (process ID #63) to
> deadlock. Rerun the query without intra-query parallelism by using the
query
> hint option (maxdop 1).
>
> Can somebody explain what caused it.
> Thanks,
> Subbu.
>
>|||Thank you. I found it later.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OUAafLChEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Hi!
> Do please check the article at http://support.microsoft.com/?kbid=837983.
> You will find possible reasons & workarounds there.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
been[vbcol=seagreen]
> query
>
I am running a select query , which used left join on 5 tables. this is
reporting server there are no data updates, only select stmts. it has been
failing at runtime , the same query is running fine with different
parameters.
It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
degree of parallelism is 0 (before i change) , when i set max degree of
parallelism to 1 , then the query did run fine. As I remeber i havent
changed any configuration recently.
The query failed with following message.
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #63) to
deadlock. Rerun the query without intra-query parallelism by using the query
hint option (maxdop 1).
Can somebody explain what caused it.
Thanks,
Subbu.Hi!
Do please check the article at http://support.microsoft.com/?kbid=837983.
You will find possible reasons & workarounds there.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
> Hi,
> I am running a select query , which used left join on 5 tables. this is
> reporting server there are no data updates, only select stmts. it has been
> failing at runtime , the same query is running fine with different
> parameters.
> It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
> degree of parallelism is 0 (before i change) , when i set max degree of
> parallelism to 1 , then the query did run fine. As I remeber i havent
> changed any configuration recently.
>
> The query failed with following message.
> Server: Msg 8650, Level 13, State 127, Line 1
> Intra-query parallelism caused your server command (process ID #63) to
> deadlock. Rerun the query without intra-query parallelism by using the
query
> hint option (maxdop 1).
>
> Can somebody explain what caused it.
> Thanks,
> Subbu.
>
>|||Thank you. I found it later.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OUAafLChEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Hi!
> Do please check the article at http://support.microsoft.com/?kbid=837983.
> You will find possible reasons & workarounds there.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
been[vbcol=seagreen]
> query
>
Intra-query parallelism caused deadlock
Hi,
I am running a select query , which used left join on 5 tables. this is
reporting server there are no data updates, only select stmts. it has been
failing at runtime , the same query is running fine with different
parameters.
It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
degree of parallelism is 0 (before i change) , when i set max degree of
parallelism to 1 , then the query did run fine. As I remeber i havent
changed any configuration recently.
The query failed with following message.
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #63) to
deadlock. Rerun the query without intra-query parallelism by using the query
hint option (maxdop 1).
Can somebody explain what caused it.
Thanks,
Subbu.Hi!
Do please check the article at http://support.microsoft.com/?kbid=837983.
You will find possible reasons & workarounds there.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
> Hi,
> I am running a select query , which used left join on 5 tables. this is
> reporting server there are no data updates, only select stmts. it has been
> failing at runtime , the same query is running fine with different
> parameters.
> It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
> degree of parallelism is 0 (before i change) , when i set max degree of
> parallelism to 1 , then the query did run fine. As I remeber i havent
> changed any configuration recently.
>
> The query failed with following message.
> Server: Msg 8650, Level 13, State 127, Line 1
> Intra-query parallelism caused your server command (process ID #63) to
> deadlock. Rerun the query without intra-query parallelism by using the
query
> hint option (maxdop 1).
>
> Can somebody explain what caused it.
> Thanks,
> Subbu.
>
>|||Thank you. I found it later.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OUAafLChEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Hi!
> Do please check the article at http://support.microsoft.com/?kbid=837983.
> You will find possible reasons & workarounds there.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I am running a select query , which used left join on 5 tables. this is
> > reporting server there are no data updates, only select stmts. it has
been
> > failing at runtime , the same query is running fine with different
> > parameters.
> >
> > It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
> > degree of parallelism is 0 (before i change) , when i set max degree of
> > parallelism to 1 , then the query did run fine. As I remeber i havent
> > changed any configuration recently.
> >
> >
> >
> > The query failed with following message.
> >
> > Server: Msg 8650, Level 13, State 127, Line 1
> >
> > Intra-query parallelism caused your server command (process ID #63) to
> > deadlock. Rerun the query without intra-query parallelism by using the
> query
> > hint option (maxdop 1).
> >
> >
> >
> > Can somebody explain what caused it.
> >
> > Thanks,
> >
> > Subbu.
> >
> >
> >
> >
>
I am running a select query , which used left join on 5 tables. this is
reporting server there are no data updates, only select stmts. it has been
failing at runtime , the same query is running fine with different
parameters.
It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
degree of parallelism is 0 (before i change) , when i set max degree of
parallelism to 1 , then the query did run fine. As I remeber i havent
changed any configuration recently.
The query failed with following message.
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #63) to
deadlock. Rerun the query without intra-query parallelism by using the query
hint option (maxdop 1).
Can somebody explain what caused it.
Thanks,
Subbu.Hi!
Do please check the article at http://support.microsoft.com/?kbid=837983.
You will find possible reasons & workarounds there.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
> Hi,
> I am running a select query , which used left join on 5 tables. this is
> reporting server there are no data updates, only select stmts. it has been
> failing at runtime , the same query is running fine with different
> parameters.
> It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
> degree of parallelism is 0 (before i change) , when i set max degree of
> parallelism to 1 , then the query did run fine. As I remeber i havent
> changed any configuration recently.
>
> The query failed with following message.
> Server: Msg 8650, Level 13, State 127, Line 1
> Intra-query parallelism caused your server command (process ID #63) to
> deadlock. Rerun the query without intra-query parallelism by using the
query
> hint option (maxdop 1).
>
> Can somebody explain what caused it.
> Thanks,
> Subbu.
>
>|||Thank you. I found it later.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OUAafLChEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Hi!
> Do please check the article at http://support.microsoft.com/?kbid=837983.
> You will find possible reasons & workarounds there.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:ewpo438gEHA.3264@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I am running a select query , which used left join on 5 tables. this is
> > reporting server there are no data updates, only select stmts. it has
been
> > failing at runtime , the same query is running fine with different
> > parameters.
> >
> > It is Dell machine with 4cpu/6gig /sql ent 2000/windows 2000 adv. max
> > degree of parallelism is 0 (before i change) , when i set max degree of
> > parallelism to 1 , then the query did run fine. As I remeber i havent
> > changed any configuration recently.
> >
> >
> >
> > The query failed with following message.
> >
> > Server: Msg 8650, Level 13, State 127, Line 1
> >
> > Intra-query parallelism caused your server command (process ID #63) to
> > deadlock. Rerun the query without intra-query parallelism by using the
> query
> > hint option (maxdop 1).
> >
> >
> >
> > Can somebody explain what caused it.
> >
> > Thanks,
> >
> > Subbu.
> >
> >
> >
> >
>
Intra-query parallelism bug fix for SQL Server 2000 SP4?
Hello,
We are running SQL Server 2000 SP4 on a server with dual-core CPU.
Recently we have noticed the following error message:
"Intra-query parallelism caused your server command (process ID #52) to
deadlock. Rerun the query ..."
I wonder if there is a fix available for that problem.
Thanks.You could try adding OPTION (MAXDOP 1) to the offending query.
--
Aaron Bertrand
SQL Server MVP
"miline" <miline@.discussions.microsoft.com> wrote in message
news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
> Hello,
> We are running SQL Server 2000 SP4 on a server with dual-core CPU.
> Recently we have noticed the following error message:
> "Intra-query parallelism caused your server command (process ID #52) to
> deadlock. Rerun the query ..."
> I wonder if there is a fix available for that problem.
> Thanks.
>|||Thank you for response.
Unfortunately I can not modify the application query. I can disable
parallelism for the whole server but that can decrease performance of the
production server. I am specifically looking for a fix from Microsoft if it
is available.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> You could try adding OPTION (MAXDOP 1) to the offending query.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "miline" <miline@.discussions.microsoft.com> wrote in message
> news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
> > Hello,
> > We are running SQL Server 2000 SP4 on a server with dual-core CPU.
> > Recently we have noticed the following error message:
> >
> > "Intra-query parallelism caused your server command (process ID #52) to
> > deadlock. Rerun the query ..."
> >
> > I wonder if there is a fix available for that problem.
> >
> > Thanks.
> >
>
>|||> Unfortunately I can not modify the application query. I can disable
> parallelism for the whole server but that can decrease performance of the
> production server. I am specifically looking for a fix from Microsoft if
> it
> is available.
Some other workarounds here:
http://support.microsoft.com/kb/837983
Also see
http://support.microsoft.com/kb/317821
(Just don't try to follow the three KB articles mentioned at the bottom;
they've all been pulled.)
--
Aaron Bertrand
SQL Server MVP
> Regards,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> You could try adding OPTION (MAXDOP 1) to the offending query.
>> --
>> Aaron Bertrand
>> SQL Server MVP
>>
>>
>> "miline" <miline@.discussions.microsoft.com> wrote in message
>> news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
>> > Hello,
>> > We are running SQL Server 2000 SP4 on a server with dual-core CPU.
>> > Recently we have noticed the following error message:
>> >
>> > "Intra-query parallelism caused your server command (process ID #52) to
>> > deadlock. Rerun the query ..."
>> >
>> > I wonder if there is a fix available for that problem.
>> >
>> > Thanks.
>> >
>>|||Thanks. I already saw that.
According to it, the only remaining option would be "enable the pre-SQL
Server 2000 SP3 parallel scan behavior" by setting trace flag 683 on (I would
not like to disable multi-processor support unless very necessary). It is not
quite clear what does it mean "pre-SQL Server 2000 SP3"; I do have SQL Server
2000 SP4. And the trace flag 683 is not mentioned in BOL, not sure if it will
help with SP4. That error message does not appear very often; I can not test
it immediatelly.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Unfortunately I can not modify the application query. I can disable
> > parallelism for the whole server but that can decrease performance of the
> > production server. I am specifically looking for a fix from Microsoft if
> > it
> > is available.
> Some other workarounds here:
> http://support.microsoft.com/kb/837983
> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> >
> > Regards,
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> You could try adding OPTION (MAXDOP 1) to the offending query.
> >>
> >> --
> >> Aaron Bertrand
> >> SQL Server MVP
> >>
> >>
> >>
> >>
> >>
> >> "miline" <miline@.discussions.microsoft.com> wrote in message
> >> news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
> >> > Hello,
> >> > We are running SQL Server 2000 SP4 on a server with dual-core CPU.
> >> > Recently we have noticed the following error message:
> >> >
> >> > "Intra-query parallelism caused your server command (process ID #52) to
> >> > deadlock. Rerun the query ..."
> >> >
> >> > I wonder if there is a fix available for that problem.
> >> >
> >> > Thanks.
> >> >
> >>
> >>
> >>
>
>|||> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
Actually the URLs were just broken due to a misplaced space. The article
will be corrected shortly.|||Thanks.
It is similar, they recommend to supress parallel execution as a workaround
there.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Also see
> > http://support.microsoft.com/kb/317821
> > (Just don't try to follow the three KB articles mentioned at the bottom;
> > they've all been pulled.)
> Actually the URLs were just broken due to a misplaced space. The article
> will be corrected shortly.
>
>
We are running SQL Server 2000 SP4 on a server with dual-core CPU.
Recently we have noticed the following error message:
"Intra-query parallelism caused your server command (process ID #52) to
deadlock. Rerun the query ..."
I wonder if there is a fix available for that problem.
Thanks.You could try adding OPTION (MAXDOP 1) to the offending query.
--
Aaron Bertrand
SQL Server MVP
"miline" <miline@.discussions.microsoft.com> wrote in message
news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
> Hello,
> We are running SQL Server 2000 SP4 on a server with dual-core CPU.
> Recently we have noticed the following error message:
> "Intra-query parallelism caused your server command (process ID #52) to
> deadlock. Rerun the query ..."
> I wonder if there is a fix available for that problem.
> Thanks.
>|||Thank you for response.
Unfortunately I can not modify the application query. I can disable
parallelism for the whole server but that can decrease performance of the
production server. I am specifically looking for a fix from Microsoft if it
is available.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> You could try adding OPTION (MAXDOP 1) to the offending query.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "miline" <miline@.discussions.microsoft.com> wrote in message
> news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
> > Hello,
> > We are running SQL Server 2000 SP4 on a server with dual-core CPU.
> > Recently we have noticed the following error message:
> >
> > "Intra-query parallelism caused your server command (process ID #52) to
> > deadlock. Rerun the query ..."
> >
> > I wonder if there is a fix available for that problem.
> >
> > Thanks.
> >
>
>|||> Unfortunately I can not modify the application query. I can disable
> parallelism for the whole server but that can decrease performance of the
> production server. I am specifically looking for a fix from Microsoft if
> it
> is available.
Some other workarounds here:
http://support.microsoft.com/kb/837983
Also see
http://support.microsoft.com/kb/317821
(Just don't try to follow the three KB articles mentioned at the bottom;
they've all been pulled.)
--
Aaron Bertrand
SQL Server MVP
> Regards,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> You could try adding OPTION (MAXDOP 1) to the offending query.
>> --
>> Aaron Bertrand
>> SQL Server MVP
>>
>>
>> "miline" <miline@.discussions.microsoft.com> wrote in message
>> news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
>> > Hello,
>> > We are running SQL Server 2000 SP4 on a server with dual-core CPU.
>> > Recently we have noticed the following error message:
>> >
>> > "Intra-query parallelism caused your server command (process ID #52) to
>> > deadlock. Rerun the query ..."
>> >
>> > I wonder if there is a fix available for that problem.
>> >
>> > Thanks.
>> >
>>|||Thanks. I already saw that.
According to it, the only remaining option would be "enable the pre-SQL
Server 2000 SP3 parallel scan behavior" by setting trace flag 683 on (I would
not like to disable multi-processor support unless very necessary). It is not
quite clear what does it mean "pre-SQL Server 2000 SP3"; I do have SQL Server
2000 SP4. And the trace flag 683 is not mentioned in BOL, not sure if it will
help with SP4. That error message does not appear very often; I can not test
it immediatelly.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Unfortunately I can not modify the application query. I can disable
> > parallelism for the whole server but that can decrease performance of the
> > production server. I am specifically looking for a fix from Microsoft if
> > it
> > is available.
> Some other workarounds here:
> http://support.microsoft.com/kb/837983
> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> >
> > Regards,
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> You could try adding OPTION (MAXDOP 1) to the offending query.
> >>
> >> --
> >> Aaron Bertrand
> >> SQL Server MVP
> >>
> >>
> >>
> >>
> >>
> >> "miline" <miline@.discussions.microsoft.com> wrote in message
> >> news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
> >> > Hello,
> >> > We are running SQL Server 2000 SP4 on a server with dual-core CPU.
> >> > Recently we have noticed the following error message:
> >> >
> >> > "Intra-query parallelism caused your server command (process ID #52) to
> >> > deadlock. Rerun the query ..."
> >> >
> >> > I wonder if there is a fix available for that problem.
> >> >
> >> > Thanks.
> >> >
> >>
> >>
> >>
>
>|||> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
Actually the URLs were just broken due to a misplaced space. The article
will be corrected shortly.|||Thanks.
It is similar, they recommend to supress parallel execution as a workaround
there.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Also see
> > http://support.microsoft.com/kb/317821
> > (Just don't try to follow the three KB articles mentioned at the bottom;
> > they've all been pulled.)
> Actually the URLs were just broken due to a misplaced space. The article
> will be corrected shortly.
>
>
Intra-query parallelism bug fix for SQL Server 2000 SP4?
Hello,
We are running SQL Server 2000 SP4 on a server with dual-core CPU.
Recently we have noticed the following error message:
"Intra-query parallelism caused your server command (process ID #52) to
deadlock. Rerun the query ..."
I wonder if there is a fix available for that problem.
Thanks.You could try adding OPTION (MAXDOP 1) to the offending query.
Aaron Bertrand
SQL Server MVP
"miline" <miline@.discussions.microsoft.com> wrote in message
news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
> Hello,
> We are running SQL Server 2000 SP4 on a server with dual-core CPU.
> Recently we have noticed the following error message:
> "Intra-query parallelism caused your server command (process ID #52) to
> deadlock. Rerun the query ..."
> I wonder if there is a fix available for that problem.
> Thanks.
>|||Thank you for response.
Unfortunately I can not modify the application query. I can disable
parallelism for the whole server but that can decrease performance of the
production server. I am specifically looking for a fix from Microsoft if it
is available.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> You could try adding OPTION (MAXDOP 1) to the offending query.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "miline" <miline@.discussions.microsoft.com> wrote in message
> news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
>
>|||> Unfortunately I can not modify the application query. I can disable
> parallelism for the whole server but that can decrease performance of the
> production server. I am specifically looking for a fix from Microsoft if
> it
> is available.
Some other workarounds here:
http://support.microsoft.com/kb/837983
Also see
http://support.microsoft.com/kb/317821
(Just don't try to follow the three KB articles mentioned at the bottom;
they've all been pulled.)
Aaron Bertrand
SQL Server MVP
[vbcol=seagreen]
> Regards,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Thanks. I already saw that.
According to it, the only remaining option would be "enable the pre-SQL
Server 2000 SP3 parallel scan behavior" by setting trace flag 683 on (I woul
d
not like to disable multi-processor support unless very necessary). It is no
t
quite clear what does it mean "pre-SQL Server 2000 SP3"; I do have SQL Serve
r
2000 SP4. And the trace flag 683 is not mentioned in BOL, not sure if it wil
l
help with SP4. That error message does not appear very often; I can not test
it immediatelly.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> Some other workarounds here:
> http://support.microsoft.com/kb/837983
> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
>
>|||> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
Actually the URLs were just broken due to a misplaced space. The article
will be corrected shortly.|||Thanks.
It is similar, they recommend to supress parallel execution as a workaround
there.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> Actually the URLs were just broken due to a misplaced space. The article
> will be corrected shortly.
>
>
We are running SQL Server 2000 SP4 on a server with dual-core CPU.
Recently we have noticed the following error message:
"Intra-query parallelism caused your server command (process ID #52) to
deadlock. Rerun the query ..."
I wonder if there is a fix available for that problem.
Thanks.You could try adding OPTION (MAXDOP 1) to the offending query.
Aaron Bertrand
SQL Server MVP
"miline" <miline@.discussions.microsoft.com> wrote in message
news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
> Hello,
> We are running SQL Server 2000 SP4 on a server with dual-core CPU.
> Recently we have noticed the following error message:
> "Intra-query parallelism caused your server command (process ID #52) to
> deadlock. Rerun the query ..."
> I wonder if there is a fix available for that problem.
> Thanks.
>|||Thank you for response.
Unfortunately I can not modify the application query. I can disable
parallelism for the whole server but that can decrease performance of the
production server. I am specifically looking for a fix from Microsoft if it
is available.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> You could try adding OPTION (MAXDOP 1) to the offending query.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "miline" <miline@.discussions.microsoft.com> wrote in message
> news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
>
>|||> Unfortunately I can not modify the application query. I can disable
> parallelism for the whole server but that can decrease performance of the
> production server. I am specifically looking for a fix from Microsoft if
> it
> is available.
Some other workarounds here:
http://support.microsoft.com/kb/837983
Also see
http://support.microsoft.com/kb/317821
(Just don't try to follow the three KB articles mentioned at the bottom;
they've all been pulled.)
Aaron Bertrand
SQL Server MVP
[vbcol=seagreen]
> Regards,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Thanks. I already saw that.
According to it, the only remaining option would be "enable the pre-SQL
Server 2000 SP3 parallel scan behavior" by setting trace flag 683 on (I woul
d
not like to disable multi-processor support unless very necessary). It is no
t
quite clear what does it mean "pre-SQL Server 2000 SP3"; I do have SQL Serve
r
2000 SP4. And the trace flag 683 is not mentioned in BOL, not sure if it wil
l
help with SP4. That error message does not appear very often; I can not test
it immediatelly.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> Some other workarounds here:
> http://support.microsoft.com/kb/837983
> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
>
>|||> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
Actually the URLs were just broken due to a misplaced space. The article
will be corrected shortly.|||Thanks.
It is similar, they recommend to supress parallel execution as a workaround
there.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> Actually the URLs were just broken due to a misplaced space. The article
> will be corrected shortly.
>
>
Intra-query parallelism bug fix for SQL Server 2000 SP4?
Hello,
We are running SQL Server 2000 SP4 on a server with dual-core CPU.
Recently we have noticed the following error message:
"Intra-query parallelism caused your server command (process ID #52) to
deadlock. Rerun the query ..."
I wonder if there is a fix available for that problem.
Thanks.
You could try adding OPTION (MAXDOP 1) to the offending query.
Aaron Bertrand
SQL Server MVP
"miline" <miline@.discussions.microsoft.com> wrote in message
news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
> Hello,
> We are running SQL Server 2000 SP4 on a server with dual-core CPU.
> Recently we have noticed the following error message:
> "Intra-query parallelism caused your server command (process ID #52) to
> deadlock. Rerun the query ..."
> I wonder if there is a fix available for that problem.
> Thanks.
>
|||Thank you for response.
Unfortunately I can not modify the application query. I can disable
parallelism for the whole server but that can decrease performance of the
production server. I am specifically looking for a fix from Microsoft if it
is available.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> You could try adding OPTION (MAXDOP 1) to the offending query.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "miline" <miline@.discussions.microsoft.com> wrote in message
> news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
>
>
|||> Unfortunately I can not modify the application query. I can disable
> parallelism for the whole server but that can decrease performance of the
> production server. I am specifically looking for a fix from Microsoft if
> it
> is available.
Some other workarounds here:
http://support.microsoft.com/kb/837983
Also see
http://support.microsoft.com/kb/317821
(Just don't try to follow the three KB articles mentioned at the bottom;
they've all been pulled.)
Aaron Bertrand
SQL Server MVP
[vbcol=seagreen]
> Regards,
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Thanks. I already saw that.
According to it, the only remaining option would be "enable the pre-SQL
Server 2000 SP3 parallel scan behavior" by setting trace flag 683 on (I would
not like to disable multi-processor support unless very necessary). It is not
quite clear what does it mean "pre-SQL Server 2000 SP3"; I do have SQL Server
2000 SP4. And the trace flag 683 is not mentioned in BOL, not sure if it will
help with SP4. That error message does not appear very often; I can not test
it immediatelly.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> Some other workarounds here:
> http://support.microsoft.com/kb/837983
> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
>
>
|||> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
Actually the URLs were just broken due to a misplaced space. The article
will be corrected shortly.
|||Thanks.
It is similar, they recommend to supress parallel execution as a workaround
there.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> Actually the URLs were just broken due to a misplaced space. The article
> will be corrected shortly.
>
>
We are running SQL Server 2000 SP4 on a server with dual-core CPU.
Recently we have noticed the following error message:
"Intra-query parallelism caused your server command (process ID #52) to
deadlock. Rerun the query ..."
I wonder if there is a fix available for that problem.
Thanks.
You could try adding OPTION (MAXDOP 1) to the offending query.
Aaron Bertrand
SQL Server MVP
"miline" <miline@.discussions.microsoft.com> wrote in message
news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
> Hello,
> We are running SQL Server 2000 SP4 on a server with dual-core CPU.
> Recently we have noticed the following error message:
> "Intra-query parallelism caused your server command (process ID #52) to
> deadlock. Rerun the query ..."
> I wonder if there is a fix available for that problem.
> Thanks.
>
|||Thank you for response.
Unfortunately I can not modify the application query. I can disable
parallelism for the whole server but that can decrease performance of the
production server. I am specifically looking for a fix from Microsoft if it
is available.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> You could try adding OPTION (MAXDOP 1) to the offending query.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "miline" <miline@.discussions.microsoft.com> wrote in message
> news:E8AAEB66-94B8-4A9E-BC5D-49EC773543B1@.microsoft.com...
>
>
|||> Unfortunately I can not modify the application query. I can disable
> parallelism for the whole server but that can decrease performance of the
> production server. I am specifically looking for a fix from Microsoft if
> it
> is available.
Some other workarounds here:
http://support.microsoft.com/kb/837983
Also see
http://support.microsoft.com/kb/317821
(Just don't try to follow the three KB articles mentioned at the bottom;
they've all been pulled.)
Aaron Bertrand
SQL Server MVP
[vbcol=seagreen]
> Regards,
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Thanks. I already saw that.
According to it, the only remaining option would be "enable the pre-SQL
Server 2000 SP3 parallel scan behavior" by setting trace flag 683 on (I would
not like to disable multi-processor support unless very necessary). It is not
quite clear what does it mean "pre-SQL Server 2000 SP3"; I do have SQL Server
2000 SP4. And the trace flag 683 is not mentioned in BOL, not sure if it will
help with SP4. That error message does not appear very often; I can not test
it immediatelly.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> Some other workarounds here:
> http://support.microsoft.com/kb/837983
> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
>
>
|||> Also see
> http://support.microsoft.com/kb/317821
> (Just don't try to follow the three KB articles mentioned at the bottom;
> they've all been pulled.)
Actually the URLs were just broken due to a misplaced space. The article
will be corrected shortly.
|||Thanks.
It is similar, they recommend to supress parallel execution as a workaround
there.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:
> Actually the URLs were just broken due to a misplaced space. The article
> will be corrected shortly.
>
>
Intra-query parallelism ???
Hi,
Im using SQL 7.0 with FrontPage sending code to the dB.
Im getting the following error and dont understand what it means.
------
Database Results Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Intra-query parallelism caused your server command (process ID #13) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
-----
The code is long and has several sub-queries.
All are looking to the same rows in the dB.
Im passing a parameter from FP. If I remove the parameter and hardcode the value I dont get this error.
Is this problem SQL or FP? If SQL what am I looking at?
Thanks for you help
MottyThis does happen if you're on a multi-processor server. The query is being splitted up for parallel processing. Unfortunately, they happen to block each other.
As the error suggests, you either rerun or use option hint (maxdop 1) to limit the processing to one processor.
Im using SQL 7.0 with FrontPage sending code to the dB.
Im getting the following error and dont understand what it means.
------
Database Results Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Intra-query parallelism caused your server command (process ID #13) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
-----
The code is long and has several sub-queries.
All are looking to the same rows in the dB.
Im passing a parameter from FP. If I remove the parameter and hardcode the value I dont get this error.
Is this problem SQL or FP? If SQL what am I looking at?
Thanks for you help
MottyThis does happen if you're on a multi-processor server. The query is being splitted up for parallel processing. Unfortunately, they happen to block each other.
As the error suggests, you either rerun or use option hint (maxdop 1) to limit the processing to one processor.
intra-query parallelism
Hi,
I am running sql 7 with sp3.
I got an error in running a sp.
The error message says, :Intra-query parallelism caused your server
command(process ID# 30) to deadlock. Rerun the query without intra-query
parallelism by using the query hint option (Maxdop 1).
Any ideas for this.
Thanks,
Can you upgrade to Service Pack 4 and see if that remedies the issue?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am running sql 7 with sp3.
> I got an error in running a sp.
> The error message says, :Intra-query parallelism caused your server
> command(process ID# 30) to deadlock. Rerun the query without intra-query
> parallelism by using the query hint option (Maxdop 1).
> Any ideas for this.
> Thanks,
>
|||I do have sp4 installed
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eXWhKH35EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Can you upgrade to Service Pack 4 and see if that remedies the issue?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
>
|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> I do have sp4 installed
Okay, your original post said SP3...
Did you try the MAXDOP hint? If it doesn't decrease performance
significantly, go with that. Otherwise, you're going to have to call PSS.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||What I did was increase the value for intra-query-Paralellism.
Is that ok instead of trying try the MAXDOP hint?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#H3rPc35EHA.4004@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
>
> Okay, your original post said SP3...
> Did you try the MAXDOP hint? If it doesn't decrease performance
> significantly, go with that. Otherwise, you're going to have to call PSS.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> What I did was increase the value for intra-query-Paralellism.
> Is that ok instead of trying try the MAXDOP hint?
Well, that will affect your entire server. If you're okay with that then I
guess it's not a problem, but it could have performance implications on
other queries that aren't having issues with parallelism. The MAXDOP hint
would only affect the one query.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Thanks,
How do I do for The MAXDOP hint
>that would only affect the one query.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#DluLl35EHA.1632@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
>
> Well, that will affect your entire server. If you're okay with that then
I
> guess it's not a problem, but it could have performance implications on
> other queries that aren't having issues with parallelism. The MAXDOP hint
> would only affect the one query.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Thanks,
> How do I do for The MAXDOP hint
SELECT ...
FROM ...
WHERE ...
OPTION (MAXDOP 1)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Thanks,
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#zTbWH45EHA.3644@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
>
> SELECT ...
> FROM ...
> WHERE ...
> OPTION (MAXDOP 1)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
I am running sql 7 with sp3.
I got an error in running a sp.
The error message says, :Intra-query parallelism caused your server
command(process ID# 30) to deadlock. Rerun the query without intra-query
parallelism by using the query hint option (Maxdop 1).
Any ideas for this.
Thanks,
Can you upgrade to Service Pack 4 and see if that remedies the issue?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am running sql 7 with sp3.
> I got an error in running a sp.
> The error message says, :Intra-query parallelism caused your server
> command(process ID# 30) to deadlock. Rerun the query without intra-query
> parallelism by using the query hint option (Maxdop 1).
> Any ideas for this.
> Thanks,
>
|||I do have sp4 installed
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eXWhKH35EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Can you upgrade to Service Pack 4 and see if that remedies the issue?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
>
|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> I do have sp4 installed
Okay, your original post said SP3...
Did you try the MAXDOP hint? If it doesn't decrease performance
significantly, go with that. Otherwise, you're going to have to call PSS.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||What I did was increase the value for intra-query-Paralellism.
Is that ok instead of trying try the MAXDOP hint?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#H3rPc35EHA.4004@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
>
> Okay, your original post said SP3...
> Did you try the MAXDOP hint? If it doesn't decrease performance
> significantly, go with that. Otherwise, you're going to have to call PSS.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> What I did was increase the value for intra-query-Paralellism.
> Is that ok instead of trying try the MAXDOP hint?
Well, that will affect your entire server. If you're okay with that then I
guess it's not a problem, but it could have performance implications on
other queries that aren't having issues with parallelism. The MAXDOP hint
would only affect the one query.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Thanks,
How do I do for The MAXDOP hint
>that would only affect the one query.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#DluLl35EHA.1632@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
>
> Well, that will affect your entire server. If you're okay with that then
I
> guess it's not a problem, but it could have performance implications on
> other queries that aren't having issues with parallelism. The MAXDOP hint
> would only affect the one query.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Thanks,
> How do I do for The MAXDOP hint
SELECT ...
FROM ...
WHERE ...
OPTION (MAXDOP 1)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Thanks,
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#zTbWH45EHA.3644@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
>
> SELECT ...
> FROM ...
> WHERE ...
> OPTION (MAXDOP 1)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
Intra-query parallelism
I'm saw this error for the first time this AM from a process which runs
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?
Intra-query parallelism uses multiple threads to service a single query.
You can just add the OPTION (MAXDOP 1) to your SELECT/INSERT/UPDATE/DELETE
statement to suppress parallelism for that one query.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron Hinds" <billg@.microsoft.com> wrote in message
news:OViL%23ns2HHA.4184@.TK2MSFTNGP06.phx.gbl...
I'm saw this error for the first time this AM from a process which runs
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?
Intra-query parallelism uses multiple threads to service a single query.
You can just add the OPTION (MAXDOP 1) to your SELECT/INSERT/UPDATE/DELETE
statement to suppress parallelism for that one query.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron Hinds" <billg@.microsoft.com> wrote in message
news:OViL%23ns2HHA.4184@.TK2MSFTNGP06.phx.gbl...
I'm saw this error for the first time this AM from a process which runs
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?
Intra-query parallelism
I'm saw this error for the first time this AM from a process which runs
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?Intra-query parallelism uses multiple threads to service a single query.
You can just add the OPTION (MAXDOP 1) to your SELECT/INSERT/UPDATE/DELETE
statement to suppress parallelism for that one query.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron Hinds" <billg@.microsoft.com> wrote in message
news:OViL%23ns2HHA.4184@.TK2MSFTNGP06.phx.gbl...
I'm saw this error for the first time this AM from a process which runs
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?Intra-query parallelism uses multiple threads to service a single query.
You can just add the OPTION (MAXDOP 1) to your SELECT/INSERT/UPDATE/DELETE
statement to suppress parallelism for that one query.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron Hinds" <billg@.microsoft.com> wrote in message
news:OViL%23ns2HHA.4184@.TK2MSFTNGP06.phx.gbl...
I'm saw this error for the first time this AM from a process which runs
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?
intra-query parallelism
We have:
SQL7-sp4/w2k
4 Processors
use all available processors
Minimum query plan threshold for considering queries for parallel execution Set to 4
And the query next:
---
select a.id_distribuidora, a.id_cliente, a.Monedas, b.BeautiKit
from (
select p.id_distribuidora, p.id_cliente, sum(d.cantidad) Monedas
from Table1 p
inner join Table2 d
on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
where año=2003 and p.semana>=43 and d.clave_catalogo in ('n10429','n10430','n10431')
group by p.id_distribuidora, p.id_cliente
)a inner join (
select p.id_distribuidora, p.id_cliente, sum(d.cantidad) BeautiKit
from Table1 p
inner join Table2 d
on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
where año=2003 and p.semana>=43 and d.clave_catalogo ='n10432'
group by p.id_distribuidora, p.id_cliente
) b
on a.id_distribuidora=b.id_distribuidora and a.id_cliente=b.id_cliente
order by b.BeautiKit desc, a.Monedas desc
---
the Minimum query plan threshold for considering queries for parallel execution we set to 1,2,3,4,5
and have the error: Intra-query parallelism caused your server command (process ID #19) to deadlock
who need set?
p.d. the same query over SQL7/NT and one processor no have problem.
thank, for your helpTry adding "OPTION (MAXDOP 1)" to the query to force the optimizer to NOT
use parallel execution for that particular query. If that works, then wait
for MS to fix it.
Or you can rewrite the query to get around the problem. It looks like the
two derived tables are the same query using different catalog values. You
should be able to create one derived table that generates two sums - you
would also need to determine if data exists for both catalog value sets in
order to get the same result set (data is returned only if matching rows
exists in both derived tables).
"FcoResendiz" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB80CFD-F586-42B3-BCCA-196FC2F2BA9A@.microsoft.com...
> We have:
> SQL7-sp4/w2k
> 4 Processors
> use all available processors
> Minimum query plan threshold for considering queries for parallel
execution Set to 4
> And the query next:
> ---
> select a.id_distribuidora, a.id_cliente, a.Monedas, b.BeautiKit
> from (
> select p.id_distribuidora, p.id_cliente, sum(d.cantidad) Monedas
> from Table1 p
> inner join Table2 d
> on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
> where año=2003 and p.semana>=43 and d.clave_catalogo in
('n10429','n10430','n10431')
> group by p.id_distribuidora, p.id_cliente
> )a inner join (
> select p.id_distribuidora, p.id_cliente, sum(d.cantidad) BeautiKit
> from Table1 p
> inner join Table2 d
> on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
> where año=2003 and p.semana>=43 and d.clave_catalogo ='n10432'
> group by p.id_distribuidora, p.id_cliente
> ) b
> on a.id_distribuidora=b.id_distribuidora and a.id_cliente=b.id_cliente
> order by b.BeautiKit desc, a.Monedas desc
> ---
> the Minimum query plan threshold for considering queries for parallel
execution we set to 1,2,3,4,5
> and have the error: Intra-query parallelism caused your server command
(process ID #19) to deadlock
> who need set?
> p.d. the same query over SQL7/NT and one processor no have problem.
> thank, for your help
>
SQL7-sp4/w2k
4 Processors
use all available processors
Minimum query plan threshold for considering queries for parallel execution Set to 4
And the query next:
---
select a.id_distribuidora, a.id_cliente, a.Monedas, b.BeautiKit
from (
select p.id_distribuidora, p.id_cliente, sum(d.cantidad) Monedas
from Table1 p
inner join Table2 d
on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
where año=2003 and p.semana>=43 and d.clave_catalogo in ('n10429','n10430','n10431')
group by p.id_distribuidora, p.id_cliente
)a inner join (
select p.id_distribuidora, p.id_cliente, sum(d.cantidad) BeautiKit
from Table1 p
inner join Table2 d
on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
where año=2003 and p.semana>=43 and d.clave_catalogo ='n10432'
group by p.id_distribuidora, p.id_cliente
) b
on a.id_distribuidora=b.id_distribuidora and a.id_cliente=b.id_cliente
order by b.BeautiKit desc, a.Monedas desc
---
the Minimum query plan threshold for considering queries for parallel execution we set to 1,2,3,4,5
and have the error: Intra-query parallelism caused your server command (process ID #19) to deadlock
who need set?
p.d. the same query over SQL7/NT and one processor no have problem.
thank, for your helpTry adding "OPTION (MAXDOP 1)" to the query to force the optimizer to NOT
use parallel execution for that particular query. If that works, then wait
for MS to fix it.
Or you can rewrite the query to get around the problem. It looks like the
two derived tables are the same query using different catalog values. You
should be able to create one derived table that generates two sums - you
would also need to determine if data exists for both catalog value sets in
order to get the same result set (data is returned only if matching rows
exists in both derived tables).
"FcoResendiz" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB80CFD-F586-42B3-BCCA-196FC2F2BA9A@.microsoft.com...
> We have:
> SQL7-sp4/w2k
> 4 Processors
> use all available processors
> Minimum query plan threshold for considering queries for parallel
execution Set to 4
> And the query next:
> ---
> select a.id_distribuidora, a.id_cliente, a.Monedas, b.BeautiKit
> from (
> select p.id_distribuidora, p.id_cliente, sum(d.cantidad) Monedas
> from Table1 p
> inner join Table2 d
> on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
> where año=2003 and p.semana>=43 and d.clave_catalogo in
('n10429','n10430','n10431')
> group by p.id_distribuidora, p.id_cliente
> )a inner join (
> select p.id_distribuidora, p.id_cliente, sum(d.cantidad) BeautiKit
> from Table1 p
> inner join Table2 d
> on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
> where año=2003 and p.semana>=43 and d.clave_catalogo ='n10432'
> group by p.id_distribuidora, p.id_cliente
> ) b
> on a.id_distribuidora=b.id_distribuidora and a.id_cliente=b.id_cliente
> order by b.BeautiKit desc, a.Monedas desc
> ---
> the Minimum query plan threshold for considering queries for parallel
execution we set to 1,2,3,4,5
> and have the error: Intra-query parallelism caused your server command
(process ID #19) to deadlock
> who need set?
> p.d. the same query over SQL7/NT and one processor no have problem.
> thank, for your help
>
Friday, February 24, 2012
intra-query parallelism
Hi,
I am running sql 7 with sp3.
I got an error in running a sp.
The error message says, :Intra-query parallelism caused your server
command(process ID# 30) to deadlock. Rerun the query without intra-query
parallelism by using the query hint option (Maxdop 1).
Any ideas for this.
Thanks,Can you upgrade to Service Pack 4 and see if that remedies the issue?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am running sql 7 with sp3.
> I got an error in running a sp.
> The error message says, :Intra-query parallelism caused your server
> command(process ID# 30) to deadlock. Rerun the query without intra-query
> parallelism by using the query hint option (Maxdop 1).
> Any ideas for this.
> Thanks,
>|||I do have sp4 installed
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eXWhKH35EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Can you upgrade to Service Pack 4 and see if that remedies the issue?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I am running sql 7 with sp3.
> >
> > I got an error in running a sp.
> > The error message says, :Intra-query parallelism caused your server
> > command(process ID# 30) to deadlock. Rerun the query without intra-query
> > parallelism by using the query hint option (Maxdop 1).
> >
> > Any ideas for this.
> >
> > Thanks,
> >
> >
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> I do have sp4 installed
Okay, your original post said SP3...
Did you try the MAXDOP hint? If it doesn't decrease performance
significantly, go with that. Otherwise, you're going to have to call PSS.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What I did was increase the value for intra-query-Paralellism.
Is that ok instead of trying try the MAXDOP hint?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#H3rPc35EHA.4004@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > I do have sp4 installed
>
> Okay, your original post said SP3...
> Did you try the MAXDOP hint? If it doesn't decrease performance
> significantly, go with that. Otherwise, you're going to have to call PSS.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> What I did was increase the value for intra-query-Paralellism.
> Is that ok instead of trying try the MAXDOP hint?
Well, that will affect your entire server. If you're okay with that then I
guess it's not a problem, but it could have performance implications on
other queries that aren't having issues with parallelism. The MAXDOP hint
would only affect the one query.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks,
How do I do for The MAXDOP hint
>that would only affect the one query.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#DluLl35EHA.1632@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > What I did was increase the value for intra-query-Paralellism.
> > Is that ok instead of trying try the MAXDOP hint?
>
> Well, that will affect your entire server. If you're okay with that then
I
> guess it's not a problem, but it could have performance implications on
> other queries that aren't having issues with parallelism. The MAXDOP hint
> would only affect the one query.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
> Thanks,
> How do I do for The MAXDOP hint
> >that would only affect the one query.
SELECT ...
FROM ...
WHERE ...
OPTION (MAXDOP 1)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks,
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#zTbWH45EHA.3644@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
> > Thanks,
> >
> > How do I do for The MAXDOP hint
> > >that would only affect the one query.
>
> SELECT ...
> FROM ...
> WHERE ...
> OPTION (MAXDOP 1)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
I am running sql 7 with sp3.
I got an error in running a sp.
The error message says, :Intra-query parallelism caused your server
command(process ID# 30) to deadlock. Rerun the query without intra-query
parallelism by using the query hint option (Maxdop 1).
Any ideas for this.
Thanks,Can you upgrade to Service Pack 4 and see if that remedies the issue?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am running sql 7 with sp3.
> I got an error in running a sp.
> The error message says, :Intra-query parallelism caused your server
> command(process ID# 30) to deadlock. Rerun the query without intra-query
> parallelism by using the query hint option (Maxdop 1).
> Any ideas for this.
> Thanks,
>|||I do have sp4 installed
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eXWhKH35EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Can you upgrade to Service Pack 4 and see if that remedies the issue?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I am running sql 7 with sp3.
> >
> > I got an error in running a sp.
> > The error message says, :Intra-query parallelism caused your server
> > command(process ID# 30) to deadlock. Rerun the query without intra-query
> > parallelism by using the query hint option (Maxdop 1).
> >
> > Any ideas for this.
> >
> > Thanks,
> >
> >
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> I do have sp4 installed
Okay, your original post said SP3...
Did you try the MAXDOP hint? If it doesn't decrease performance
significantly, go with that. Otherwise, you're going to have to call PSS.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What I did was increase the value for intra-query-Paralellism.
Is that ok instead of trying try the MAXDOP hint?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#H3rPc35EHA.4004@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > I do have sp4 installed
>
> Okay, your original post said SP3...
> Did you try the MAXDOP hint? If it doesn't decrease performance
> significantly, go with that. Otherwise, you're going to have to call PSS.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> What I did was increase the value for intra-query-Paralellism.
> Is that ok instead of trying try the MAXDOP hint?
Well, that will affect your entire server. If you're okay with that then I
guess it's not a problem, but it could have performance implications on
other queries that aren't having issues with parallelism. The MAXDOP hint
would only affect the one query.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks,
How do I do for The MAXDOP hint
>that would only affect the one query.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#DluLl35EHA.1632@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > What I did was increase the value for intra-query-Paralellism.
> > Is that ok instead of trying try the MAXDOP hint?
>
> Well, that will affect your entire server. If you're okay with that then
I
> guess it's not a problem, but it could have performance implications on
> other queries that aren't having issues with parallelism. The MAXDOP hint
> would only affect the one query.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
> Thanks,
> How do I do for The MAXDOP hint
> >that would only affect the one query.
SELECT ...
FROM ...
WHERE ...
OPTION (MAXDOP 1)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks,
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#zTbWH45EHA.3644@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
> > Thanks,
> >
> > How do I do for The MAXDOP hint
> > >that would only affect the one query.
>
> SELECT ...
> FROM ...
> WHERE ...
> OPTION (MAXDOP 1)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
Intra-query parallelism
I'm saw this error for the first time this AM from a process which runs
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?Intra-query parallelism uses multiple threads to service a single query.
You can just add the OPTION (MAXDOP 1) to your SELECT/INSERT/UPDATE/DELETE
statement to suppress parallelism for that one query.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron Hinds" <billg@.microsoft.com> wrote in message
news:OViL%23ns2HHA.4184@.TK2MSFTNGP06.phx.gbl...
I'm saw this error for the first time this AM from a process which runs
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?Intra-query parallelism uses multiple threads to service a single query.
You can just add the OPTION (MAXDOP 1) to your SELECT/INSERT/UPDATE/DELETE
statement to suppress parallelism for that one query.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron Hinds" <billg@.microsoft.com> wrote in message
news:OViL%23ns2HHA.4184@.TK2MSFTNGP06.phx.gbl...
I'm saw this error for the first time this AM from a process which runs
nightly:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #55) to deadlock. Rerun the query without
intra-query parallelism by using the query hint option (maxdop 1).
I read KB 837983 "You may receive error message 8650 when you run a query
that uses intra-query parallelism" and KB 315662 "FIX: Parallel query may
encounter undetected deadlock with itself" referenced by that article, as
well as the article mentioned by a MVP in this group in a message dated
07/26/2007. I also read up on Degrees of parallelism in BOL. What isn't
clear to me is, what exactly is meant by parallel "query" execution? Is this
referring to a single "query" being executed by multiple threads
simultaneously, or multiple threads executing separate "queries" (even
though the statements may be identical)? If it is the former then I see no
reason why I shouldn't go ahead and disable it, since only this one process
which runs after hours has generated the error. If it is the latter, then
that would seem to indicate that disabling parallel query execution
effectively would make my four CPU server a one CPU server. What I have read
seems to suggest the former, but nowhere is it stated outright. Can anyone
clear this up for me?
Intra-query Paralleism caused your query to deadlock . . .
We often get the above message from our Sql Server 2000 Transactional Replication Distribution service. How do we implement the OPTION (MAXDOP 1) hint as directed? It seems I would have to modify the MS procedures. Can't do that. Any suggestions?
Michael
I originally posted this in the Replication forum but was ignored so I thought that perhaps a more "general" forum would attract a different kind of contributor . . .
If you cant give the OPTION (MAXDOP 1) query hint, you can change the Max Degree of Parallelism setting to 1, which in effect gives the same result.|||Are you suggesting that the max parallism for the entire server be changed to 1? Is that the solution? Will setting this option affect other queries in the system? What are other DBA's doing to solve this problem? I am concerned about the performance impact on the other queries in the server.
Thanks a lot Roji.
Subscribe to:
Posts (Atom)