Why do I get a parallel plan in 2014 but not in 2019?

Nick Ryan 261 Reputation points
2023-11-01T21:28:39.8733333+00:00

I am migrating from SQL 2014 to SQL 2019. I have one job running an SSIS import that went from minutes to hours. I narrowed it down to one particular query. It's very complex with several layers of sub-query and using a couple of views with CTEs and other horrors.
When I used Compare ShowPlans, I found one area that took 7 seconds in 2014 compatibility mode and 22 minutes in 2019 and the obvious difference is that 2014 has gone parallel and 2019 has not. It's exactly the same query - I simply change the compatibility level of the 2 databases and run it again.
I think I may be able to force a parallel plan for 2019 but my question is, why is 2019 so much worse and choosing whether to go parallel or not than 2014?
This is the 2014 compatibility mode plan bit.
User's image

And this is the 2019 version.

User's image

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2023-11-01T22:02:25.44+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Nick Ryan 261 Reputation points
    2023-11-01T22:35:12.8466667+00:00

    I found a Pinal Dave article, https://blog.sqlauthority.com/2018/04/22/how-to-force-a-parallel-execution-plan-for-a-query-interview-question-of-the-week-170/. I tried the query hint OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) and boom, 52 minutes down to 57 seconds.

    Erland said no supported or documented way to force a parallel plan but this hint helped the optimiser pick the better plan for my situation.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.