1

I've migrated my database from a SQL Server 2012R2 to a new SQL Server 2019. I've just backupped the database and restored it on new sql server. After that I've imported and updated some additional data (quotes) with IMPORT, INSERT, UPDATE or MERGE. And in between some testers have used the system for some testing. Now performance is on some actions slower than on old server.

  1. First I've done some index tuning. Here are some questions regarding this topic:

    Questions on updating statistics and index maintenance jobs

    Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? [closed]

  2. Second I've done the same import done on our test environemnt with SQL Server 2012R2. And there I don't have the performance issue.

  3. Query (deleted)

  4. Execution Plans

[Execution plan on Server 2012R2] (deleted)

[Execution plan on Server 2019] (deleted)

6
  • It owuld help if u used brentozar.com/pastetheplan and pasted the query too. I just gonna do a hail marry and put my bet right now on that you are using datetime2 with dateadd which causes the row estimate to go to 1, leading to mass key lookups. Jan 13, 2021 at 8:22
  • @Vladislav Zalesak Ok thanks. I've added the plan on brentozar + the query. Jan 13, 2021 at 9:22
  • Try adding query hint OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
    – Dan Guzman
    Jan 13, 2021 at 12:29
  • As others have indicated your new execution plan has some Cardinality Estimate issues, and overall definitely a different plan than on your old instance. I also a RID LOOKUP operation indicative that you're possibly missing a clustered index somewhere. I'd start by testing with the legacy cardinality estimator per Dan's comment.
    – J.D.
    Jan 13, 2021 at 12:54
  • The first thing to do when migrating up to a higher version, is to realize an update statistics on all table with FULLSCAN. Stats computed in a version (2012) is not in adequation to the 2019 edition. Every version computes stats differently...
    – SQLpro
    Jan 13, 2021 at 13:22

2 Answers 2

1

@user1673665 ok, this definitelly looks like cardinality issue, prolly statistics. You have SQL Server expecting few row, ie for scMat_MaterialText 4, but you get 62000. Thats way off. Try to run a select on this table, then review estimates vs actual rows read. And try to update statistics. I would say thats the culprint

4
  • I update statistics every night with Olas script. But I've activated OnlyModified Statistics. Could this play a role? Jan 14, 2021 at 7:55
  • 1
    try to run only subqueries selecting from cMat_MaterialText and post the plan again. Thats one table where the estimates are off that much. If it is still of or not. Jan 14, 2021 at 8:44
  • 1
    maybe add table definition scripts... Jan 14, 2021 at 8:48
  • ok thanks I understand Jan 15, 2021 at 8:18
0

Apparently changing the compatibility level from 2019 to 2012 has solved the issue. I don't know if I have any disadvantages by doing this. Anyone knows what this change exactly does or means?

For now the described issue is gone. Thanks for your help: Vladislav Zalesak. Dan Guzman. J.D. SQLpro. Charlieface.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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