Performance issues after migration

Question

Performance issues after migration

ramana3327

SSCoach

Points: 19722
More actions
March 19, 2023 at 3:25 am

#4162414
Hi all,
We installed SQL 2017 standard edition. Taken the 2008r2 server database backup and restored into new 2017 server. Didn’t change the compatibility level. Same memory allocated as 2008r2 and did the update stats but seeing performance issues.
- This topic was modified 1 year, 8 months ago by ramana3327.

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

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

Start with the "Key Configuration Options" in the following article. Every single thing there can make a pretty good difference.

https://glennsqlperformance.com/2020/08/20/preventing-sql-server-upgrade-performance-issues/

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

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Thanks Jeff. According to the link performance regression happens with LEGACY_CARDINALITY ESTIMATION, when changed the database comparability level from old version(100) to native/new version(140). Here we didn’t change the compatibility level so LEGACY_CARDINALITY ESTIMATION is off.

This reply was modified 1 year, 8 months ago by ramana3327.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

Have you ever investigated MS Query Tuning Assistant ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rothj Hall of Fame Points: 3407 More actions · Answer 4

You might try DBCC Updateusage.

I've had to run that coming off some old servers to fix performance issues if it seems storage is your related wait types.

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-ver16