6

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.

  1. The 2012 database server is Enterprise, the 2019 is Standard. As far as I know, this shouldn't make a difference
  2. The 2012 database was restored to the 2019 server and it's version changed to 150 (2019)
  3. MAXDOP on the 2012 server was 0, 2019 server it is set to 8 as recommended by Microsoft and others
  4. 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.

0
11

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;
3
  • You can leave the databases at the same database compatibility level indefinitely if you want. – David Browne - Microsoft Jun 1 '20 at 23:38
  • Yep, but then we don't get the new features! Am eyeing up the Scalar UDF inlining feature as our databases heavily use Scalar UDFs. Unfortunately the bugs in that feature have stopped us from implementing it: social.msdn.microsoft.com/Forums/sqlserver/en-US/… – blobbles Jun 2 '20 at 2:44
  • You can also opt out of the new cardinality estimator at the higher compatibility level. – David Browne - Microsoft Jun 18 '20 at 13:24
4

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/

1
  • 1
    Our application uses Hibernate ORM and produces really inefficient queries - we suffered badly with plan regression when we upgraded - switching to legacy cardinality worked for us - Microsoft confirmed this was the best approach in our scenario - we have since used Query Store in SQL Server 2017 on another environment and the option to force a known good plan is really good and gives us some degree of control-pleaae post an update to advise how you are getting on 👍 – dbakerry987 Jun 3 '20 at 7:34
3

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
0
-1

Another possibility is enabling global trace flag 9481 and all the workload in your instance will use legacy CE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.