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:
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.
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.
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.
The first version of the new Referential Integrity Operator has the following constraints:
Please refer to this article for more details.
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.
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.
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.
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:
This blog post has more details for the mentioned In-Memory OLTP improvements.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.