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 still services connections, just very slowly. I was thinking of setting the connection timeout setting on the server to a high amount, however I suspect we still won't get responses from the server. It's like it's blocking all new connections if its even slightly busy.
Are there other things I should try? Are those database settings worth messing around with?
I could dive in further and start looking at DMVs, however this seems to be close to a "like for like" environment upgrade with considerable drops in performance. Just checking there isn't something else I should check before doing a bigger investigation.
Best Answer
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.
If you have a lot of plan regressions you can keep using the older cardinality estimator at the higher database compatibility level with: