Partition Wise Joins - by geoff patterson

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 759266 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 8/23/2012 10:53:04 AM
Access Restriction Public


When joining two tables that are both partitioned on the same column and partition function, SQL Server does not take advantage of the fact that each partition could be processed independently and the results concatenated or aggregated at the end.

In such cases, a query plan that allocates one thread to each partition and performs a MERGE JOIN within that partition--with that thread not touching data from other partitions at all--would likely be a highly efficient way of parallelizing the join such that performance scales linearly with the number of CPU cores used (so long as there are enough partitions involved).

The attached test file builds a data set and includes test queries and statistics that highlight the current inefficiency of SQL Server in this particular case.  The test file can be run in any environment, but it is likely that the most meaningful results will be seen on machines with 8+ cores.

For reference, here is an Oracle blog entry describing functionality similar to this request in Oracle:

In addition, here is a relevant StackExchange question on the topic:
Sign in to post a comment.
Posted by Paul White NZ on 6/15/2013 at 8:34 AM
Hi Gus,

It is curious that collocated hash join has been implemented (presumably because the scenario was common enough) but this suggestion to add collocated merge join has been rejected.

Posted by Microsoft on 4/30/2013 at 1:00 PM

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.

If you have more supporting information and details or feel strongly that we should reconsider, please do resubmit this item for review.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by Microsoft on 8/29/2012 at 2:52 PM
Thanks for your feedback. We are looking into this request