In SQL Server 2016, Microsoft introduced several new Query Optimizer improvements.
Compatibility Level Guarantees
Microsoft states,
“Starting SQL Server 2016, we promise that after upgrades there will be no plan changes if you stick with the old compatibility levels, like 120 or 110. New features and improvements will be available under the latest compatibility level only.”
This will help in making the upgrade experience much smoother, for example, while upgrading from the database from SQL Server 2104 (compatibility level 120) to SQL Server 6, the workload would continue to get the same query plans which it used to. In a similar manner, when the company makes enhancements to Azure SQL DB capabilities, they won't affect the query plans of your workloads as long as you do not change the compatibility level.
As a result of this guarantee, the new Query Optimizer improvements will now only be available in the latest compatibility level (130). The company encourages users to upgrade to the latest compatibility level in order to benefit from all the enhancements.
Query Optimizer Improvements under Trace Flag 4199
In order to prevent unwanted plan changes, all Query Optimizer hotfixes from the previous release which results in plan changes have been put under a specific Trace Flag (4199).
Microsoft states,
“The model going forward is that all improvements to the Query Optimizer will be released and on by default under successive database compatibility levels. As a result, we have enabled the improvements previously available only under trace flag 4199 by default under compatibility level 130.”
New Referential Integrity Operator
SQL Server 2016 introduces a new Referential Integrity Operator (under compatibility level 130) which increases the limit on the number of other tables with foreign key references to a primary or unique key of a given table (incoming references), from 253 to 10,000. The new query execution operator does the referential integrity checks in place, by comparing the modified row to the rows in the referencing tables, to verify that the modification will not break the referential integrity. This results in much lower compilation times for such plans and comparable execution times.
Example:
