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).