1

We migrated our databases from SQLServer 2012 to SQLServer 2019. Our ETL's are build in Visual Studio and are set up from a master package. The masterpackage calls on different packages, which are not deployed in SSIS. One of the packages calls a stored procedure. This stored procedure call on different stored procedures. On the old server, this SP step took 4 hours. On the new server, this step takes 7 hours. What could we do to speed up this proces? Does the compatibility level of the database affect this proces? And would it help if we deploy the package in SSIS? We are open for any suggestions.

Things we already tried:

  • Rebuilding indexes and updating statistics
  • Improving certain queries
  • Creating 8 files instead of 1 in the tempdb (old sever has one)

Thank you for your help. Esmee

2
  • If you update your question with Paul Randal's SQL Server Wait Statistics query results, it might give us a clue about what is still causing the bad performance of your SP. It's related to David Browne - Microsoft' answer from your other question.
    – Ronaldo
    Mar 24, 2021 at 9:53
  • 3
    As others have mentioned, it's possible that the changes to the Cardinality Estimator are the cause of your issues. But I bet your 4 hour stored procedure probably already had cardinality estimation issues (and could've been faster itself) and the new estimator is making them even more apparent, especially since you were only on the comparability level of 2005. If you could post the actual execution plan of both instances, we likely can pinpoint your performance issue.
    – J.D.
    Mar 24, 2021 at 11:44

2 Answers 2

5

The big issue when upgrading across SQL Server 2014, meaning, going from anything less than 2014 to anything 2014 or greater, is the fact that the 2014 release of SQL Server included a new Cardinality Estimation Engine.

For the majority of queries, the new CE won't affect them in any way. Some queries will run faster. A few queries, usually queries that were already problematic edge cases, will indeed run radically slower.

The best way to deal with this would have been to use Query Store as part of your upgrade, as I outline in this recent blog post. You could, possibly, still do this. Change the compatibility mode of the database back to the old version, then follow the steps outlined. However, that's predicated on the fact that you haven't already started using new coding mechanisms in the database that are 2019 only.

Otherwise, you'll need to look first at the execution plans for the queries involved. Compare the plans from 2012 with the plans from 2019. See where the issues lie. It's likely that what you really need are updates to the code and underlying data structures. However, many can't, or won't, do that work. Instead, read through the details of the CE in this Microsoft article. There, you can see the options available to you from leaving the compatibility mode at the old setting, to changing the CE through Database Scoped Configuration changes, to query hints, and finally, back around to the Query Store.

One of these solutions will help. However, the best one, is to look at the code & structures and adjust them to be more optimal.

2

One potential cause for this is then you now have different execution plans for some queries, compared to what you had earlier. When you see significant performance differences, the reason is frequently plan related.

Compatibility level affects the optimizer. Running with the same compat level as your old SQL Server had is likely to give you higher degree of same execution plan as before. This can be a first step. Then there are a plethora of ways to manage this after your analysis is done - but first step is to find out what causes the perf difference.

There can be other things as well, of course. Looking at wait stats as suggested byu Ronaldo (preferably on both the old and new server) is another piece of information that can be very valuable.

2
  • Thank you for the quick response. I do see that the old compatibility level is 2005. The lowest option is 2012 on the new server. Is this what we should go for?
    – Esmee
    Mar 24, 2021 at 10:43
  • The lowest option is 2008, not 2012. They did some pretty significant changes between 2012 and 2014 so this is the switch where you are most likely to see differences. I'd start with 2012 and then take it from there. Note that these issues is often an indication that the optimizer "struggled" in the first place but you were lucky to end up with a decent plan. I.e., it is plausible that such a query is a good candidate for traditional performance tuning. Mar 25, 2021 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.