Sql-server – Unable to run union in parallel on SQL Server

sql serverunion

I am unable to get this fairly simple query to parallelize the union operation:

select va.ObjectId, 0 as IsFlag
  from Oav.ValueArray va     
 where va.PropertyId = @pPropertyId                
   and va.value in (select value from #MatchValues)                  
 group by va.ObjectId
having count(distinct va.Value) = (select count(*) from #MatchValues)

union all    

select odv.ObjectId, 1 as IsFlag
  from Pub.OtherTable codv
 where PropertyId = 2551
   and Id in (select value from #Ids) 
   and Flag = @pFlag
   and Value in (select value from #MatchValues)
 group by codv.ObjectId
having count(distinct codv.Value) = (select count(*) from #MatchValues)

Running with MAXDOP 1 gives an expected .8s (.5 + .3). I was hoping that increasing MAXDOP to 2 would optimize for the biggest gain by using one processor for each side but that is not the case. Maxdop zero on a lightly loaded 12 Cpu machine all ~4% only results in parallel execution about 10% of the time.

Is there a way to weight the hints so that parallelization at the union point is the most important? Does the syntax support separate MAXDOP's for each side?

I have tried (concat/hash/merge union) with little change.

Match values is usually a small table (~10 rows).

Best Answer

There is no separate MAXDOP for each side. But you could play with:

OPTION (QUERYTRACEON 8649)

This sets the cost threshold of parallelism to 0, meaning it will consider a parallel plan even if the costs are very low. You can also play with DBCC SETCPUWEIGHT, which Paul White describes here or other techniques he has for forcing parallel plans here. or even play with DBCC OPTIMIZER_WHATIF - which really should just be for playing.

There has been a suggestion on Connect to allow for a MINDOP syntax or something similar.

In any case, I'm not convinced that parallelism will necessarily help you in this case. Sure, you might get a parallel plan, but is it really going to reduce the runtime of the query? With all those GROUP BY and DISTINCT (why would you ever need both?) I think you should focus your optimization elsewhere (such as pre-aggregating some of this information perhaps). Or even something simple, like perhaps assign the COUNT(*) FROM #MatchValues to a variable instead of trying to evaluate it twice (not sure if SQL Server will do that in this case, but it can't hurt to remove the temptation).

Related Question