I'm using Azure SQL Server v12 and I have big, complex query that runs ok but when I add more joins, it doesn't matter which table I'm joining to, it gets super slow. The important thing to point out is that by adding a simple join it's choosing a different execution for the whole query and I receive warning on sort operations that are not performed in the original query.
I cannot post the query or the execution plan but I want to know if someone faced a similar case. My intuition tells me that SQL Server is choosing a bad execution plan because the query is so big that it times out when it tries to pick one.
I was able to improve the query by saving intermediate results to a memory optimized table. That way I could "guarantee" the execution plan until that point and then I do some joins to the memory optimized table. If I add more than 10 joins approx to memory optimized table, I get a bad execution plan. This happens even if I join to small tables.
Should I indicate the join type and index name for each join to keep the original execution plan? is that the best way to go?