The query above is designed to not be able to take advantage of parallelism. The useless repartition streams step and the spill to tempdb suggest that the query might perform better with a
MAXDOP 1hint. With a
MAXDOP 1hint the query runs with an average time of 2473 ms. There is no longer a spill to tempdb.
What happens if the query is run with
MAXDOP 3? Earlier I said that the hashing function or thread boundaries can change based on DOP. With
MAXDOP 3I get a much more even row distribution on threads:
I think the number of cases where it makes sense to use a specific, non-1 MAXDOP hint is pretty small, but here’s one of them. The problem is that if this data changes regularly, the skewness of the data could change along with it, making your brilliant optimization unnecessary or even harmful.