April 14, 2025 at 1:47 pm
I migrated DB server from 2017 enterprise to 2019 enterprise. After the migration, every setting on a DB server was kept the same.
The issue I have been seeing is that the batch which used to run for 30 minutes, is now taking an hour. API is called, the data is read and then inserted into 1 table. I changed the compatibility level of a DB to 2019 but the issue remains. I checked resources by using different DMVs and I see no network related issues, some disk latency which is going to be modified (we are using the same Azure offering as we did in 2017. App team doesn't understand why this performance issue).
What should I be checking for? What other settings I can look for to make sure my batch is optimized? Maintenance jobs have been running as they were on the old server.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
April 14, 2025 at 2:42 pm
The database compatibility level determines how SQL Server interprets and optimizes queries based on the version's features. If your database was migrated from SQL Server 2017 to 2019, it may still be running under an older compatibility level, potentially missing out on improvements in SQL Server 2019.
Database Compatibility Level:
Ensure that your database compatibility level is set correctly (ALTER DATABASE SET COMPATIBILITY_LEVEL = 150 for SQL Server 2019).
Check Current Compatibility:
SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
If you need to update it to SQL Server 2019's default (level 150), use:
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 150;
April 14, 2025 at 2:50 pm
Execution Plan Differences: Compare execution plans before and after migration to identify bottlenecks.
Query Store Analysis: Use Query Store to analyze execution times and identify regression cases.
Locking and Blocking: Investigate any contention issues that may have arisen post-migration.
Indexing Issues: Check if your indexes are still effective or if they need to be rebuilt or updated.
April 14, 2025 at 2:50 pm
Make sure statistics for all large tables are updated.
Review query plans to check for potential performance issues.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 14, 2025 at 2:52 pm
It is already set to 150.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
April 14, 2025 at 3:00 pm
Legacy Cardinality Estimation: If performance regressions occur after upgrading, SQL Server 2019 allows enabling the legacy CE model for specific databases or queries. This can be done using the ALTER DATABASE SCOPED CONFIGURATION command.
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
April 14, 2025 at 3:07 pm
Just enabled the Query store this morning.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
April 14, 2025 at 3:09 pm
I'd most want to see the execution plans before & after if you can get them. That's going to give you the most understanding of what is happening. From that you should be able to determine the why and then fix it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy