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
Best Answer
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.