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
>

Interpreting the execution plan results

On Query Analyzer..
I can figure out a little what the information on the execution plan means
but I’m not sure
Where can I find a good description about each item that is show in the
execution plan?
e.g estimated subtree cost etc..
thks
Kenny M. (KennyM@.discussions.microsoft.com) writes:
> On Query Analyzer..
> I can figure out a little what the information on the execution plan means
> but I'm not sure
> Where can I find a good description about each item that is show in the
> execution plan?
>
> e.g estimated subtree cost etc..
SQL Server Books Online. Optimizing Database Performance->Query Tuning->
Analyzing a Query->Logical and Physical Operators.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx