We have recently migrated from SQL Server 2012 Enterprise edition to SQL Server 2019 Standard edition. We don't use any of Enterprise edition features, so no problem to restore the DB backup to 2019 server.
The new and old server hardware specs are almost same, 128GB RAM, 32CPU cores. only windows and SQL Server versions are different.
Just after the 2012 db backup restored on 2019 server, changed the db compatibility level to 150. Then thigs are happening.
We found lots of performance degradation in several Views, SPs and agent job scripts.
Especially a query has several UNION or CTE the speed like 100-200 times slower than earlier.
But if I set the DB compatibility level to 110, all the performances are back to normal.!
I am quite confusing how it happens. Any opinions are welcome.