Slow Query Performance in SQL Server 2019 After Database Migration

Question

Hello i am shanks from Lean IT recently migrated a database to Microsoft SQL Server 2019 from SQL Server 2016, and I’m experiencing significant slowdowns in query performance after the migration.

• SQL Server Version: SQL Server 2019
• Environment: Windows Server 2019
• Problem/Issue:
  • Queries that previously executed in under 2 seconds now take over 10 seconds to complete.
    • No changes were made to the queries or indexes during migration.
      • The execution plan shows more I/O operations, and CPU usage has increased.

I’ve tried the following troubleshooting steps:

1. Rebuilding indexes.
2. Updating statistics on all tables.
3. Running queries with different isolation levels.

Regretfully, the problem still exists despite these efforts. I'd appreciate some advice on why my performance might have declined and how to fix it.

Best regards, Shanks

Answer 1

I assumed that you changed the compatibility level from 130 to 150 when you did the upgrade. Microsoft makes changes to the optimizer in about every version of SQL Server, and you only get these changes if you change the compatibility level. These changes are meant to improve the overall performance, but the nature of a cost-based optimizer that works with estimates from statistics means that things can go wrong, and it is not unheard of plan regressions after upgrades.

I would recommend that you switch back the compat level to 130. That should remove the urgent problems. Then enable Query Store for the database. Run with this config for a week or two. Then go back to compat level 150 again. You can now use the Regressed Queries report in the Query Store folder in Object Explorer to see which queries they are. You can force the plans from compat level 130, while you work with analysing more closely why the query regressed. Often when this happens it is due to that the query has some imperfection - poor query construct, lack of a well-supporting index.

Rather than running this process as I described above, you can select the database in Object Explorer, and from the Tasks submenu select Database Upgrade at the very bottom. It will do the same process as I described, but help you with the steps.