Change the database compatibility level and use the Query Store
Applies to: 
SQL Server - Windows only
In SQL Server 2016 (13.x) and later, some changes are only enabled once the database compatibility level has been changed. This was done for several reasons:
Since upgrade is a one-way operation (it isn't possible to downgrade the file format), there's value in separating the enablement of new features to a separate operation within the database. It's possible to revert a setting to a prior database compatibility level. The new model reduces the number of things that must happen during an outage window.
Changes to the query processor can have complex effects. Even though a "good" change to the system may be great for most workloads - it may cause an unacceptable regression on an important query for others. Separating this logic from the upgrade process, allows for features such as the Query Store, to mitigate plan choice regressions quickly or even avoid them completely in production servers.
The below behaviors are expected for SQL Server 2017 (14.x) when a database is attached or restored, and after an in-place upgrade:
- If the compatibility level of a user database was 100 or higher before the upgrade, it remains the same after upgrade.
- If the compatibility level of a user database was 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2017 (14.x).
- The compatibility levels of the
tempdb,model,msdband Resource databases are set to the current compatibility level after upgrade. - The
mastersystem database retains the compatibility level it had before upgrade.
The upgrade process to enable new query processor functionality is related to the post-release servicing model of the product. Some of those fixes are released under Trace Flag 4199. Customers needing fixes can opt in to those fixes without causing unexpected regressions for other customers. The post-release servicing model for query processor hotfixes is documented here. Beginning with SQL Server 2016 (13.x), moving to a new compatibility level implies that Trace Flag 4199 is no longer needed, because those fixes are now enabled by default in the latest compatibility level. Therefore, as part of the upgrade process, it's important to validate that 4199 isn't enabled once the upgrade process completes.
Note
Trace Flag 4199 is still needed to enable any new query processor fixes released after RTM, if applicable.
The recommended workflow for upgrading the query processor to the latest version of the code is documented in the Keep performance stability during the upgrade to newer SQL Server section of Query Store Usage Scenarios, as seen below.
Starting with SQL Server Management Studio v18, users can be guided through the recommended workflow using the Query Tuning Assistant. For more information, see Upgrading Databases by using the Query Tuning Assistant.