Sometimes the ultimate fix is a rewrite of the queries to a more optimal pattern.

I'm just still not getting it.

So, let's try this. You can't have a dynamic join. You have to define a join. Period. To dynamically change whether or not you're joining...

This needs pretty detailed analysis, and I'm sorry, I've been busy with work. I'll try to get into it again as soon as I can.

Short answer, I suspect you're dealing...

I'm still not understanding what you're going for. Apologies.

However, I think what you want is two different LEFT JOINs to the two tables, then use COALESCE to get the column...

As you've defined it, a simple UNION query will get you what you want.

SELECT a.id_a as Id_Obj,
a.customer_a as Customer,
a.Name_a as Name
FROM dbo.db_a as a
UNION
SELECT b.id_b as Id_Obj,
b.customer_b...

So, you're not looking for tuning then, just management?

What about index compression? That's a huge win for some people, depending on your data. Also, columnstore indexes may, emphasize that word,...

I have admin access, so I went ahead and removed them for you.

Try...

If you want to, get a copy of SentryOne Plan Explorer. It's free. It has a way to anonymize the plans. Post those and no code will be visible.

Two things. First, the query is in the plan. I sure don't see anything that's even remotely proprietary, but if there's a concern, you should remove those from the post.

Second,...

I don't think 'interim table' is the correct term...

Did I misread your compatibility mode? If so, my bad, apologies. Yes, you can have the new compatibility mode enabled and then disable the new cardinality estimator.

As to the rest,...

I could try to guess at what you're going for, but instead, could you put the table names in with the tables. Show some of the relationships, primary & foreign...

If you have tables with fewer than 200 rows, the statistics won't be changing very often unless you're doing lots of replacements of that data, so I don't see how...

Development is where development and testing occurs. No production information should be stored there. You certainly shouldn't have weird cross server queries from production to development. This is a fundamentally...

I don't see anything in what you posted to suggest that it's a cache problem. If you have two instances on the server, make sure each one has a max...