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

We have a SQL Server 2012 server which far outperforms a SQL Server 2019 database on (as far as I can see) the same infrastructure. We are hosting both databases on a cloud platform with the same SLAs. Both have 180GB RAM and 16 processors.

However there are a few key differences.

• The 2012 database server is Enterprise, the 2019 is Standard. As far as I know, this shouldn't make a difference

• The 2012 database was restored to the 2019 server and it's version changed to 150 (2019)

• MAXDOP on the 2012 server was 0, 2019 server it is set to 8 as recommended by Microsoft and others

• Cost threshold for parallelism = 5 on 2012 server, 20 on 2019 server

EDIT: Another major difference I just realised - one is Windows Server 2008,the other is Windows Server 2019. It could possibly be server side settings as well...

Allocation unit size is set to 64kb for all SQL disks, SQL has the right permissions to be able to control file sizes itself. Server is set to high performance mode. Anything else I should be changing server side?

Other database settings were not changed, so the following settings are default on 2019, I believe:

• Legacy Cardinality Estimation = OFF

• Parameter Sniffing = ON

• Query Optimiser Fixes = OFF

Mainly the type of queries we do are large complex multi join queries performing updates and inserts, with the occasional small selects from users. We load large files to the database and then process the data in large queries, usually one at a time. In between these large "loads" we have users doing selects on other database tables not being loaded/processed in preparation for future load/process steps. Generally we are getting between 30%-50% performance reductions in processing. I figured this was because of the MAXDOP setting, but altering it to 0 made no difference over a series of runs.

Our major symptom is we are getting lock timeouts when we try to connect to the 2019 server while it is busy processing, whereas the 2012 server stil

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.

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;

StackExchange Database Administrators Q#268099, answer score: 12