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.

  • Both machines are virtual and as far as I can see, the same specs. – blobbles May 29 at 23:03
  • Can you share the counter metrics for batch req/sec when same load use to run on 2012 vs 2019? Also how's the performance when you keep legacy cardinality estimation = ON? – KASQLDBA May 29 at 23:04
  • Hi sorry @KASQLDBA - I don't have metrics to that level of detail as the old server is now gone. We only have the timing information left. This change was done under urgency - the old servers were unpatched and in a bit of a state. I was expecting higher performance straight away, but looks like David Browne's answer below shows that there is a upgrade path I wasn't aware of. – blobbles Jun 1 at 20:50
10

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;
| improve this answer | |
  • 1
    OK, thanks for this David. I wasn't aware I would need to go through such a rigmarole in order to obtain the same performance on a more modern version of the software. :-( ... with over 200 databases to migrate, this might be a much bigger headache than imagined. – blobbles Jun 1 at 20:52
  • You can leave the databases at the same database compatibility level indefinitely if you want. – David Browne - Microsoft Jun 1 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 at 2:44
  • You can also opt out of the new cardinality estimator at the higher compatibility level. – David Browne - Microsoft Jun 18 at 13:24
3

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/

| improve this answer | |
  • 1
    Thanks dbakerry, yes I might try this next :-) – blobbles Jun 3 at 6:14
  • 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 at 7:34
  • Happy to report, we got the same results. Setting the legacy carnality estimator to on gives us dramatically better performance. – blobbles Jun 19 at 22:18
2

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
| improve this answer | |
  • Yes, this also worked for us, with a similar slowdown in query speeds, thanks btb. It makes me wonder if anyone has done a test by creating a new database in each version of SQL Server and tested performance. Would be influenced by the use case of the database I am sure, but obviously the old CE is significantly better than the new one given some cases. – blobbles Jun 19 at 22:21
0

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

| improve this answer | |

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.