May 24, 2023 at 3:12 pm
This may be somewhere in the docs, but my Google-fu wasn't able to find it...
We're testing an upgrade from SQL 2012 to SQL 2019, but tests indicate performance in SQL 2019 with compatibility level 110 is worse than native SQL 2012 for some critical queries and switching to compatibility level 150 (native 2019) is even worse. Enabling the legacy cardinality estimator helps, but not enough to allow the system to go live.
We are considering resetting the test environment to SQL 2012 then upgrading to SQL 2016 and comparing performance of compatibility level 110 in 2016 with compatibility level 130. Obviously, we can use query store to collect data while running SQL 2016.
If we then upgrade to SQL 2019, is the query store data collected while running SQL 2016 still available, or does it get wiped?
Derek
May 25, 2023 at 11:48 am
The Query Store information should survive the upgrade.
"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
May 26, 2023 at 9:27 am
There are always performance winners and losers when you upgrade SQL server, but mostly you should have winners.
It is good that you have identified key queries that will be losers. The next stage is what are you going to do about this newly-identified technical debt. You cannot run at a lower compatibility level or force use of the legacy cardinality estimator indefinitely, these are also technical debt.
When we had similar issues on a migration (a few years back, cannot remember the SQL versions involved) the first stage was to ask the users how important response time was to them. This resulted in a few queries we could ignore until after the migration.
The next step was to look at a quick fix of index design. This did deal with most of the problems, and the new index design helped on the old system while it was still in use.
There were still a few queries that performed worse than desired. These all needed to be re-written, the only question was which were essential before the migration and which could wait to later.
In the end we fixed some queries in advance but did migrate with a few DBs in a lower compatibility mode and a few queries forcing legacy cardinality. I think it took about 6 months before all the problem queries were fixed, and the rest of the queries on those DBs could benefit from the full features of the new SQL version.
When we did a SQL2017 to SQL2019 migration there were still a few losers but nothing that needed fixing before the migration. YMMV.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply