Query Optimizer Additions in SQL Server 2016

  • Article

In SQL Server 2016 we have introduced a number of new Query Optimizer improvements. This article summarizes some them and explains you can leverage the benefits of the new enhancements. Expect deep dive follow up articles for some of the enhancements. Here is the short list:

  • Compatibility Level Protections
  • Query Optimizer Improvements under Trace Flag 4199
  • New Referential Integrity Operator
  • Parallel Update of Sampled Statistics
  • Sublinear Threshold for Update of Statistics
  • Additions to the New Cardinality Estimator (New CE)
  • Misc. Enhancements

Compatibility Level Protections

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 make the upgrade experience much smoother. For example, when upgrading from a database from SQL Server 2014 (compatibility level 120) to SQL Server 2016, the workload will continue getting the same query plans that it used to. Similarly, when we make enhancements to Azure SQL DB capabilities, we will not affect the query plans of your workloads, as long as you don’t change the compatibility level.

As a result of this protection, the new Query Optimizer improvements will only be available in the latest compatibility level (130). You are encouraged to upgrade to the latest compatibility level to benefit from all the enhancements. To ensure that you mitigate any unintended consequences of plan changes by such, please refer to the upgrade suggestions in this article.

Note that there could be few corner case exceptions to this guarantee. Fixes for severe issues, like result correctness, will be done in all compatibility levels, regardless of their impact on query plans.

Query Optimizer Improvements under Trace Flag 4199

Traditionally, to prevent unwanted plan changes, all Query Optimizer hotfixes from previous releases that result in plan changes have been put under a specific Trace Flag (4199) only. Details about this trace flag can be found here. 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) that 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:

 CREATE TABLE Customer(Id INT PRIMARY KEY, CustomerName NVARCHAR(128))
CREATE TABLE ReferenceToCustomer1(CustomerId INT FOREIGN KEY REFERENCES Customer(Id))
CREATE TABLE ReferenceToCustomer2(CustomerId INT FOREIGN KEY REFERENCES Customer(Id))
CREATE TABLE ReferenceToCustomer3(CustomerId INT FOREIGN KEY REFERENCES Customer(Id))
...
DELETE Customer WHERE Id = 1

Old Plan:

FK Old Plan
New Plan:

FK New Plan

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

  • 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

Please refer to this article for more details.

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, we have enabled 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 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. Note that this behavior was available under Trace Flag 2371 in previous releases.

For example, if a table had 1 billion rows, under the old behavior it would have taken 200 million rows to be changed before auto-stats update kicks in. In SQL Server 2016, it would take only 1 million rows to trigger auto stats update.

Additions to the New Cardinality Estimator (New CE)

SQL Server 2014 introduced a new Cardinality Estimator to address short-comings in the cardinality estimator that was used in previous versions of the product. In the latest release, we have identified and fixed some inefficiencies 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), we have introduced a number of Query Optimizer enhancements that trigger newly introduced perf improvements. Below is a list of some of those:

  • Batch query processing in serial queries
  • Sort operators in batch mode
  • Window aggregates in batch mode
  • Distinct aggregates in batch mode
  • Parallel INSERT SELECT into heaps and CCI
  • Heap scans for memory-optimized tables
  • Parallel scans for memory-optimized tables
  • Sampled and auto-update stats for memory-optimized tables

This blog post has more details for the mentioned In-Memory OLTP improvements.