Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Mar 22 '13 at 22:40

This question came from our site for professional and enthusiast programmers.

1  
In the 10% of the cases where you see parallel execution, do you actually see improvements in runtime? Depending on the size of these tables, .8s seems pretty reasonable for the aggregating you're doing. –  Aaron Bertrand Mar 22 '13 at 22:16
4  
To answer your question about setting parallel execution at the union point, no, that is not how parallelism works in SQL Server –  Paul White Mar 22 '13 at 23:49
    
It would help to see the query plan. As Aaron says below, it's likely there are other optimization opportunities than simply trying to sledge-hammer it with parallelism. –  Jon Seigel Mar 23 '13 at 13:53
    
Aaron, I saw some runtimes come in at .45. I wasn't generating exec plans at the time so can't guarantee that it was actually going parallel and not some other mechanism (such as caching?). However I also commented on your answer. –  crokusek Mar 25 '13 at 4:42

1 Answer 1

up vote 7 down vote accepted

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

share|improve this answer
    
Great tips, I tried the 8649 option and it did add much parallelization, unfortunately too much such that the queries came in slower than with maxdop(1). I tried maxdop(2) and the optimizer did not choose to put the parallel operations at the union point but rather in one of the subqueries so I it looks like I would need more control to achieve this. Such as being able to specify maxdop(1) for each side of the union and a hint like "concat parallel union". The extra "distinct" with the group by is just a mistake. I will edit it out. Too big to pre-calc all combinations of possible inputs. –  crokusek Mar 25 '13 at 4:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.