After Migrating to 2019, Some Queries are Slow

SQL Server Administration
1

So we migrated from 2012 to 2019. When the db is set to Comp Level 2019, some queries run much slower.

Is there a way to update all procs and views to get a better execution plan on the 2019 instance?
Would that just involve dropping and recreating each proc and view?
Thanks

2

Don't drop and recreate stored procs. Not only is it unnecessary, it also will cause the permissions on the stored procs to be wiped out.

If you want to clear the cache, you can use DBCC FREEPROCCACHE. But even that, I would use with care, because it clears EVERY execution plan. You can clear the execution plan for specific queries or stored procedures. Described in the documentation for DBCC FREEPROCCACHE.

3
  1. Ensure you updated statistics after restoring the database into SQL2019.
  2. Enjoy some light reading:
    Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator | Microsoft Docs
4

[1] Is there a way to update all procs and views to get a better execution plan on the 2019 instance?

No. When you changed the compatibility to 2019, SQL should recompile the plans.

[2] Would that just involve dropping and recreating each proc and view?

Heavens no, as JamesK has stated. If you really wanted to, you could run sys.sp_refreshsqlmodule on all the procs, but it should not make any difference.

1 Like
5

Already asked