SQL Server 2014 Database restored to 2019 vs. Database created on 2019

Question

If I restore a database from a 2014 server onto a 2019 server and change the compatibility level to 150, is there a possibility that database won't perform as well as one that was created on 2019?

The reason I ask is that we're experiencing across the board poorer performance on the 2019 server when run against the same jobs on the old 2014 server. This is around 10% or so. We've had all sorts working on the issue looking at the specs of the VMs, etc, with not great improvements so I'm exploring outside possibilities, here.

I asked ChatGPT and of course got a woolly answer that effectively said "it depends" but there was enough there to make me wonder.

Is there a definitive answer to this or is it nonsense and they should be exactly the same?

Answer 1

Hi @Nick Ryan

I am afraid there is not a definitive answer. There are many factors that affect performance.

I can only give suggestions for optimization.

As long as you update the statistics and rebuild the index on the restored database at the same time, restoring a database from a 2014 server onto a 2019 server and changing the compatibility level to 150 should not negatively impact performance.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16

Best regards,

Percy Tang