Upgrade to SQL2019 from 2012 Query is taking time, Need Query Optimization steps

Question

Post reply

Upgrade to SQL2019 from 2012 Query is taking time, Need Query Optimization steps

ralahari

Old Hand

Points: 301
More actions
June 14, 2024 at 11:51 pm

#4428586

Upgrade to SQL2019 from 2012 Query is taking time, Need Query Optimization steps and Pre requisites to optimize Query Performance and CPU Usage

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

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

Site Owners SSC Guru Points: 80325 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.

Lowell SSC Guru Points: 323518 More actions · Answer 2

change the database compatibility to SQL2019 prior to the items below.

the optimizer is different between versions, so it's pretty standard to have to, at a minimum, rebuild statistics with full scan, or if you can spare the time, rebuild all indexes.

to test quickly, just rebuild stats with full scan on the specific tables involved in your query, then work on the whole database.

with that, you should be able to zoom in and see better performance and pain poins.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Lowell wrote:

change the database compatibility to SQL2019 prior to the items below.
the optimizer is different between versions, so it's pretty standard to have to, at a minimum, rebuild statistics with full scan, or if you can spare the time, rebuild all indexes.
to test quickly, just rebuild stats with full scan on the specific tables involved in your query, then work on the whole database.
with that, you should be able to zoom in and see better performance and pain poins.

I have to tell you that the updgrade even from 2016 to 2022 just killed us for performance. Changing the Compatabilty Level back to 2016 only helped a little. The testing I've done at home show some issues with CPU usage. MS said their aware of that issue but hasn't done a thing about it since 2019 came out.

Also, if you are upgrading from 2012 to anything else, you may have to enable the Legacy Cardinality Estimator. We had to enable that when we upgraded 2012 to 2016 and, without it, 2022 just about won't run for us even with the 2016 compatibility level enabled.

--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)

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 4

ralahari wrote:

Upgrade to SQL2019 from 2012 Query is taking time, Need Query Optimization steps and Pre requisites to optimize Query Performance and CPU Usage

Is it just one query that is slow? If so can you post the query here.

I would first try running the query in SSMS's Tools\Database Engine Tuning Advisor and see if there are any recommended statistics or indexes that can be added.