SQL Server 2019 performance worse than 2012... am I missing something?

I believe you have just discovered why the recommended upgrade process is to to upgrade your database, enable the Query Store, and test before increasing the database compatibility level.

enter image description here Change the Database Compatibility Level and use the Query Store

If you have a lot of plan regressions you can keep using the older cardinality estimator at the higher database compatibility level with:

ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;

We had a similar issue upgrading from Sql Server 2012.

Our issues were due to Cardinality Estimator changes introduced in SQL Server 2014

Try changing the Legacy Cardinality setting to ON in a test environment and compare the performance of workloads

https://blog.sqlauthority.com/2019/02/09/sql-server-enabling-older-legacy-cardinality-estimation/


We had a similar issue upgrading from SQL Server 2008 R2 to SQL Server 2019(compatibility level 150).

Some of our nightly update jobs suddenly took 6-7 times as long to run(from 4 min to 39 min, and from 1 hour to 6 hours).

Setting Legacy Cardinality setting to ON brought us back to our usual update speed.

This is what we did(source: https://blog.sqlauthority.com/2019/02/09/sql-server-enabling-older-legacy-cardinality-estimation/):

USE [YourDB]
GO
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO