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
MERGE. And in between some testers have used the system for some testing. Now performance is on some actions slower than on old server.
First I’ve done some index tuning. Here are some questions regarding this topic:
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.
[Execution plan on Server 2012R2]
[Execution plan on Server 2019]
@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
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.