I finally figured it out!

So to the previous questions first and foremost...yes the Select statements were exact.

I did run the new stats with FULLSCAN on both systems. I even rebuilt the indexes on all tables on both systems just in case 🙂

So in the end the problem WAS due to a change I had made in test I had forgotten about over a week ago. I won't get into the gritty details but suffice to say, the sales order table has a clustered index on it that was not unique. I had change it to unique as I thought that was rather odd that I had put a clustered index on there without the unique identifier (which I understood improves performance as well). So the end result was...the queries run faster on the sales order table WITHOUT the clustered index being unique. The test did not have it as unique, and production did. Which is why test was running faster. I did some testing and moved the changes to production and all was well again. Now I have to figure out WHY that is because that goes against what common sense would dictate lol.

In regards to this SQLRNNR, I have seen a lot of 'it depends' and 'the data needs to support the physical join operator' in quite a few places without an actual explanation. Is there anywhere I can go to get a more in depth analysis on this? For instance what are common cases for a merge and a hash working better? Also, when you say data needs to support it, does that mean data types? Data volume? Structure?

Sorry to pester with so many questions, but that is one aspect I do feel that I do not understand very well. I just know most of my queries run faster with the hash operator unless they are against much smaller tables.