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?

  • Please consider reading this advice – mustaccio Apr 18 '19 at 2:19
  • I have just read it. The query is huge and I cannot paste the execution plan because of confidencially reasons. I'm trying to get an answer with general information about this issue. I don't think I'm doing only person dealing with huge queries where the optimizer gives up on providing a good enough execution plan. – Francisco Goldenstein Apr 18 '19 at 13:21
  • Quantify "super slow". And this query is showing up in the Query Store as a "Top Resource Consuming Query"? – jl01 Apr 18 '19 at 13:30
  • It's taking more than 20m but when I remove one of the joins that changes the execution plan, it takes less than 2m. – Francisco Goldenstein Apr 18 '19 at 13:44

Just gonna throw this out there: if a query is big and confusing to you, it's probably big and confusing to the optimizer, too.


No, because those hints might not always be the right choice.

It would probably make more sense to take the portion of the query that you're getting a good plan for, and dump it to a #temp table.

From there, add on your other joins to the temp table. That way, the optimizer has fewer bad choices to make. Sure, you may need to stick an index on the temp table, but that's a lot less painful than stapling a bunch of brittle hints on.

| improve this answer | |
  • I did what you said about the temp table using a memory optimized table. So, basically I inserted the records of an intermiedate result. That improved the query but then, when I had to join the memory optimized table to another tables, I had issues. I got to a point when joining to any table makes it bad. I cannot use table variable or temp tables because this query will finally be called from Tableau and that product has its limitations. – Francisco Goldenstein Apr 18 '19 at 13:14
  • @FranciscoGoldenstein these are limitations you really need to mention up front. – Erik Darling Apr 18 '19 at 13:25
