Contents
What happens when you migrate from SQL Server to SQL Server?
Applies to: SQL Server to SQL Server migration. When migrating from an older version of SQL Server to SQL Server 2014 (12.x) or newer, and upgrading the database compatibility level to the latest available, a workload may be exposed to the risk of performance regression.
How to keep performance stable during SQL server migration?
Change the database compatibility level to the source version, and follow the recommended upgrade workflow as shown in the following picture: For more information on this topic, see Keep performance stability during the upgrade to newer SQL Server. Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) to SQL Server migration.
What happens when I upgrade to a new SQL Server?
Once you go live on a newer version, and you start doing deletes/updates/inserts (DUIs) on that new version, your data just flat out now lives in the new version. You can’t restore a database to an earlier SQL Server version.
Can a post migration SQL Server query optimizer?
For SQL Server to SQL Server migrations, if this issue existed in the source SQL Server, migrating to a newer version of SQL Server as-is will not address this scenario. SQL Server Query Optimizer can only account for information that is known at compile time.
What to do when SQL Server is slow?
At the end of this process, if performance regressions are detected, rather than moving back to the previously known good plan, the QTA will actually suggest hint-based improvements that can be deployed for individual queries (using plan guides), without having to necessarily move back to the legacy CE.
What are some common performance scenarios after SQL server migration?
Below are some of the common performance scenarios encountered after migrating to SQL Server Platform and how to resolve them. These include scenarios that are specific to SQL Server to SQL Server migration (older versions to newer versions), as well as foreign platform (such as Oracle, DB2, MySQL and Sybase) to SQL Server migration.
How does the database compatibility level affect performance?
Since SQL Server 2014, the database compatibility level affects the default cardinality estimator that the query optimizer will use. Since SQL Server 2016, the database compatibility level also controls other performance related behavior by default. I have written more about this subject here: