14,503 questions
About every version of SQL Server comes with improvements to the optimizer. Here are a few that you get when going from compatibility level 120 to 150:
- Batchmode execution.
- Interleaved execution for table-valued functions.
- Inlining of scalar function.
- Deferred compilation of statements with table variables.
- Adaptive joins.
- Memory-grant feedback.
Some of these enhancements are only available in Enterprise Edition.
However, we need to recall how the process of optimisation works: The optimizer works from statistics about the data, and the stats are often based on samples of the data. From this information the optimizer makes an estimate of which is the most efficient plan. Furthermore, the optimizer does not evaluate all possible plans, but may terminate a search because it finds a plan which it thnks is good enough. It may also run of time entirely, and take what it gets.
Thus, there is no surprise that in this process what generally can be seen as an enhancement and can backfire for one reason or another. And typically, customers scream about these cases. I rarely see posts like "why is my running so fast?".
There are a couple of options in a situation like this. A simple quick way out if you have Query Store enabled. Then you can forst the parallel plan, and then switch back to compat level 150. This may not really be a long-term solution, because if you alter the query every so slightly, then it will not match what's in Query Store, and you need to redo the forcing. Which includes setting compat level to 120 to get the parallel plan into Query Store. So more a long-term solution is to work with the query.
In my experience, when this happens, there is often something problematic wiht the query. Maybe the set of indexes is not the best. Maybe the query is written in such a way that good index cannot be used.
And, no, there is no supported and documented way to force a parallel plan.