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

Nick Ryan 221 Reputation points
2023-11-21T18:11:56.13+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,272 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-11-23T07:45:59.35+00:00

    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

    0 comments No comments

0 additional answers

Sort by: Most helpful