Microsoft Introduces Query Optimizer Additions In SQL Server 2016

In SQL Server 2016, Microsoft introduced several new Query Optimizer improvements.

Compatibility Level Guarantees

“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:

Image Source: blogs.msdn.microsoft.com

Old Plan:

Image Source: blogs.msdn.microsoft.com

New Plan:

Image Source: blogs.msdn.microsoft.com

The first version of the new Referential Integrity Operator has the following

constraints, as per the official blog:

Greater than 253 foreign key references are only supported for DELETE and UPDATE operations.
A table with a foreign key reference to itself is still limited to 253 foreign key references.
Greater than 253 foreign key references are not currently available for column store indexes, memory-optimized or Stretched tables

Parallel Update of Sampled Statistics

Collection of statistics using FULLSCAN can be run in parallel since SQL Server 2005. In SQL Server 2016 under compatibility level 130, Microsoft has enabled the collection of statistics using SAMPLE in parallel (up to 16 degree of parallelism), which decreases the overall stats update elapsed time. Since auto created stats are sampled by default, all such will be updated in parallel under the latest compatibility level.

Sublinear Threshold for Update of Statistics

In the past, the threshold for amount of changed rows that triggers auto update of statistics was 20%, which was inappropriate for large tables. Starting with SQL Server 2016 (compatibility level 130), this threshold is now related to the number of rows in a table – the higher the number of rows in a table, the lower the threshold will become to trigger an update of the statistics.

Additions to the New Cardinality Estimator (New CE)

SQL Server 2014 has introduced a new Cardinality Estimator to address short-comings in the cardinality estimator which had been used in the earlier versions of the product. In the latest release, Microsoft identified and fixed some inefficiency with the new models that could result in bad plans.

Misc. Enhancements

As part of various scenarios like column stores, in-memory OLTP (aka. Hekaton), Microsoft has gone on to introduce several Query Optimizer enhancements which trigger the newly introduced perf improvements. Given below is the list of some of those: