Upgrading from SQL Server 2016 to 2022

Question

Post reply

Upgrading from SQL Server 2016 to 2022

zoggling

SSCrazy

Points: 2253
More actions
February 27, 2025 at 2:52 pm

#4547302
When we upgraded from SQL Server 2008 R2 to 2016 (Enterprise Edition) many years back, research found that it was advisable for us to run a number of steps due to changes in the cardinality estimation process, and to cater for any other changes due to databases being upgraded to the latest compatibility level:
- Run DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY to check data for values that are no longer valid for column datatypes.
- Run DBCC UPDATEUSAGE(<database_name>) to fix any page count inaccuracies that could result in sp_spaceused returning incorrect results.
- Run UPDATE STATISTICS <table_name> WITH FULLSCAN for every table in every database, to optimise statistics for the new cardinality estimator.
- EXEC sp_refreshview @viewname = <view_name> for every view in every database, to update the metadata for all non-schema-bound views. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.
Are all these steps advisable for an upgrade from SQL Server 2016 to 2022 (Enterprise Edition)? Are there any other steps we should potentially take as part of the upgrade process?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80342 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004561 More actions · Answer 2

Yes... but it won't help... Stand up your 2022 server and do some restores and then do test runs against your old server and the new one. The tests should include rapid-fire OLTP and large runs with lots of data and joins. Hopefully you won't have the same issues as I had/have where the code runs 30 to 400 percent slower (and sometimes worse, in my case).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

zoggling SSCrazy Points: 2253 More actions · Answer 3

Thank you, have you managed to get past the performance issues as a result of the version upgrade? Do you possibly have any links you can share on why this is happening and what can be done to try to resolve them?

Jeff Moden SSC Guru Points: 1004561 More actions · Answer 4

zoggling wrote:

Thank you, have you managed to get past the performance issues as a result of the version upgrade? Do you possibly have any links you can share on why this is happening and what can be done to try to resolve them?

We haven't totally gotten past the performance issues even after we changed the comparability level to 2016 and re-enabled the Legacy Cardinality Estimator. I do have a couple of simple tests that you can run that will demonstrate some of the slowness that you'll probably encounter.

Since the performance issues started with 2019 and still exist for us today at the latest CU of 2022, I'll have to say "No" to getting past the performance issues enough to say "we're back to where we were before the supposed 'upgrade'"

It's also clearly apparent when you have both 2016 or 2017 and 2022 on the same physical box with the same hard disks, etc, etc, for a true one-to-one comparison.

Also, 2022 is pretty old. You might want to wait for 2025 to come out this year.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EdVassie SSC Guru Points: 60505 More actions · Answer 5

When upgrading to a new version of almost anything there are likely to be winners and losers. SQL Server is sadly no exception. New code that improves a majority of cases often leaves behind some edge cases that now perform worse.

My experience has been to always run a test version of you application on the new SQL version and identify the winners and losers. For the losers do a parent analysis to identify which ones hurt you the most and focus on these. Look at what you need to do to improve these. I have sometimes seen a new index on the new SQL can help greatly, but the same index on the old SQL gets ignored. The solution may be different for each query. Eventually you will fix enough so that the remaining losers can be added to the technical debt pile to be fixed later.

Also check you are making best advantage of the new SQL version. SQL 2019 introduced in-line optimisation for many SQL user functions via the use of Schemabinding. This needed some work to implement but the performance gain was worth it, sometimes reducing a query from minutes to seconds.

Back in the day we did initially force the use of legacy cardinality on some of our DBs, but treated this as technical debt as it prevented the use of some other optimisations. Eventually we fixed enough of the painful queries to allow us to drop legacy cardinality. Sadly there is seldom all gain without some pain, but by keeping pushing forward the gains do arrive.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Upgrading from SQL Server 2016 to 2022

Cookies on SQLServerCentral