In this article, we will discuss about the performance impact after change in compatibility level of your databases.
Please allow me to shed some light about the concept of Compatibility Level, as you know prior to SQL Server 2014, the database compatibility level of your user databases was not typically an important property that you had to be concerned with, at least from a performance perspective. Unlike the database file level (which gets automatically upgraded when you restore or attach a down-level database to an instance running a newer version of SQL Server, and which can never go back to the lower level), the database compatibility level can be changed to any supported level with a simple ALTER DATABASE SET COMPATIBILITY LEVEL = xxx command.
With SQL Server 2012 and older, the database compatibility level was mainly used to control whether new features introduced with a particular version of SQL Server were enabled or not and whether non-supported old features were disabled or not. The database compatibility level was also used as a method to maintain better backwards application compatibility with old versions of SQL Server. If you didn’t have time to do full regression testing with the newest compatibility level, you could just use the previous compatibility level until you could test and modify your applications if needed.
Now when you create a new user database in SQL Server, the database compatibility level will be set to the default compatibility level for that version of SQL Server. So for example, a new user database that is created in SQL Server 2017 will have a database compatibility level of 140. The exception to this is if you have changed the compatibility level of the model system database to a different supported database compatibility level, then a new user database will inherit its database compatibility level from the model database.
But Database Compatibility Level 120, this was when the “new” cardinality estimator (CE) was introduced. In many cases, most of your queries ran faster when using the new cardinality estimator, but it was fairly common to run into some queries that had major performance regressions with the new cardinality estimator. Using database compatibility level 120 means that you will be using the “new” CE unless you use an instance-wide trace flag or a query-level query hint to override it.
You can refer the below article from MS on the Differences between lower compatibility levels and level 120; https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017#differences-between-lower-compatibility-levels-and-level-120
Also there classic whitepaper wrote by Joe Sack on “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator” that explains the background and behavior of this change back in April of 2014. ; https://docs.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN
Microsoft’s recommended upgrade process is :
[+] Change the Database Compatibility Level and use the Query Store; https://docs.microsoft.com/en-us/sql/database-engine/install-windows/change-the-database-compatibility-mode-and-use-the-query-store?view=sql-server-2017
- Upgrade to the latest SQL Server version and keep the source (legacy) database compatibility level.
- Enable Query Store, and let it collect a baseline of your workload.
- Change the database compatibility level to the native level for the new version of SQL Server.
- Use Query Store to fix performance regressions by forcing the last known good plan.
Please refer the below articles on the same :
[+] https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017
Microsoft also introduce the new tool Query Tuning Assistant; Starting with SQL Server Management Studio v18, the new Query Tuning Assistant (QTA) feature will guide users through the recommended workflow to keep performance stability during upgrades to newer SQL Server versions, as documented in the section Keep performance stability during the upgrade to newer SQL Server of Query Store Usage Scenarios. However, QTA doesn't roll back to a previously known good plan as seen in the last step of the recommended workflow. Instead, QTA will track any regressions found in the Query Store Regressed Queries view, and iterate through possible permutations of applicable optimizer model variations so that a new better plan can be produced.
Please have a look into the below article for more details on The Query Tuning Assistant workflow; https://docs.microsoft.com/en-us/sql/relational-databases/performance/upgrade-dbcompat-using-qta?view=sql-server-2017#the-query-tuning-assistant-workflow
