6

This is the description from Microsoft TechNet explaining Trace Flag 342 (emphasis added):

Disables the costing of pseudo-merge joins, thus significantly reducing time spent on the parse for certain types of large, multi-table joins. One can also use SET FORCEPLAN ON to disable the costing of pseudo-merge joins because the query is forced to use the order specified in the FROM clause.

Does any of you know what is a pseudo-merge join? As far as I know, SQL Server has 3 Join Algorithms (Nest Loop Join, Merge Join, and Hash Join - which encompass Bitmap Join). So what is a pseudo-merge join, and what is the difference between it and a regular Merge Join or any other join for that matter?

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
DVT
  • 3,014
  • 1
  • 13
  • 19
  • 2
    Would be interesting to know what date this flag originates from. Maybe some ancient thing that's no longer relevant. This repository of trace flags https://sqlcrossjoin.files.wordpress.com/2015/06/sqlcrossjoin_traceflagrepository_v5.pdf points to http://smallbusiness.support.microsoft.com/en-us/kb/169630 but that page doesn't seem to exist anymore (or doesn't show for me, I get redirected to the GB site) – Martin Smith Jul 21 '16 at 17:36
  • 2
    @MartinSmith Nice find. Here's that linked KB article: [BUG: 6.5 Parse/Compile Time Is Greater than 6.0 on Large Join](https://support.microsoft.com/en-us/kb/169630) – Tim Lehner Jul 21 '16 at 19:02
  • @TimLehner nice find yourself! I did Google but didn't find that. Proper merge joins weren't introduced until SQL Server 7 AFAIK. I'm not sure what that 6.5 functionality was. – Martin Smith Jul 21 '16 at 19:11

1 Answers1

1

I know this is a kind of old question but I will try to answer it as specific as I can.

Pseudo-merge is not a type of Join used as a T-SQL language operator, my interpretation of Microsoft's explanation that using the Trace Flag 342 is as folows:

Disables the costing of pseudo-merge joins, thus significantly reducing time spent on the parse for certain types of large, multi-table joins.

Pseudo-merge is the concept to represent that the query optimiser is trying to calculate a better query execution plan, trying to obtain the best way to join the several tables.

One can also use SET FORCEPLAN ON to disable the costing of pseudo-merge joins because the query is forced to use the order specified in the FROM clause.

This option prevents the optimizer from trying to calculate and simply execute the joins as they are listed in the query.

An article on SET FORCEPLAN ON for reference.

Pimenta
  • 1,029
  • 2
  • 13
  • 32
  • I like your answer, and according to intense Google, I could not find any mention of a pseudo-merge join (other than the Trace Flag 342), so I guess your interpretation is most likely the right one. – DVT Oct 26 '16 at 18:48