Query Optimizer Additions in SQL Server 2016

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 Guarantees

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 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 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 guarantee, 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.

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.

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.

Tags

Join the conversation

Always disappointed to see these posts about query planning improvements when it seems that SQL Server 2016 still can’t optimize basic things like redundant joins based on composite foreign keys. I have tried up to at least RC2 (I haven’t tested RC3 and obviously not RTM).

It is unfortunate. This issue has been on going for years. It is very dissapointing that all you hear from Microsoft is the old “We are already looking into this and investigating” which means “We are not going to do anything about it”

This seems like a BUG to me. This seems like a very basic and necessary optimization. After all this is NOT some “special case”; it is actually something happening on a large number of queries.

This is not an “exceptional” situation.

Been wating for this for several versionf of Sql Server….. and always get the same answer “We are already looking into this and investigating”

I wich I could tell my clients the same thing without them running off to another company!

How about improving sql management studio? The query grid reminds me of visicalc. Much improvement with your existing tools in Microsoft’s arsenal. How about a dde or some type of excel live link to the query results. When building complex queries – data analysis helps a lot. even a simple sort up/down on the column heads would help.

How about different colors of the sql window for each newly opened sql project or tie it to a sql connect string. I have sometimes 5 sql management studios open connected to a variety of test/dev/production servers.

SSMS is getting love these days and since it is decoupled from the actual SQL server product it will be getting updates every month. If you have a suggestion you can go below link and and enter your ideas

+1 from me about improving sql management studio. An improvement as to allow organize db objects (tables, store procs, views, functions, types) into custom folders im sure that would make life of developers and DBA’s a lot more easier!
Im not speaking for something too complex or sophisticate (at least to it’s first edition) : Just the ability to organize my db objects in logical custom units(folders) without apply any extra security layers upon each folder.
Why now we speaking – the year 2016 – many professionals need to pay for a questionable effectiveness third party tools to get such an essential ability like this?

on SQL 2016 instances where the sp_configure ‘max degree of parallelism’ set to =1; – how do I get the DOP > 1 for Update statistics with sample and with fullscan commands? For other statements there is explicit DOP=n but I do not see one for Update Statistics and I do not see how the auto-update statistics would use DOP in such a case. Please enlighten me? Thank you.

Also, the statistics are updated with the maximum available DOP, regardless of the specified ‘degree of parallelism’. We are working on addressing this issue and hope to provide a fix in a future major release. If this is an important scenario for you, please contact our CSS to request an accelerated fix.

About the, on the face of it, excellent Compatibility Level Guarantees enhancement. In practical terms, does this mean that when the host instance is 2016 but the DB compatibility level is, say 100, any 2012+ T-SQL syntax in procedural code will result in a compile failure at best, or an execution failure at least?

Why would this matter to me? We develop commercial software that is certified to run on SQL208R2 upwards. If we can consolidate development and QA onto a SQL 2016 host instance and differentiate databases by compatibility level, we can nuke small VMs targeting individual SQL Versions and reallocate them to two larger VM instances. The big question is, “When we deploy to a native SQL2008R2 instance, can we be confident that having developed on a SQL2008R2 compatibility level database under SQL 2016, we will not start erroring out the moment the upgrade script’s ALTERs and CREATEs of SPs are executed?”

A reference to any detailed MS docs on this new feature, and particularly any limitations we need to be aware of would be very much appreciated.

The guarantee is that after upgrading from the latest previously released version to SQL 2016 (i.e. staying in the same compatibility level when upgrading SQL Server), you will not experience any plan changes, unless you do an explicit action (like changing compatibility level to a newer one, adding new indexes, using a new feature).

Many new operators are not documented in “Showplan Logical and Physical Operators Reference”.
– Foreign Key References Check,
– Window Aggregate,
– the fact that these operators work in row and batch mode and what’s the difference.

Do you have any additional documentation on the new foreign key reference operator? I am unable to make use of it in SQL Server 2016 RTM, even using the example query provided in the above example. Testing a variety of other inserts, updates, and deletes, both to referenced and referencing tables has not resulted in the use of this operator.

I am testing on databases created in SQL Server 2016, using compatibility mode 130. There appears to be no differences between these plans and those in previous versions of SQL Server (both compatibility modes 120 and 110 yield the same results).

Is it only chosen under very specific circumstances, or is it not yet available in the current release of SQL Server 2016?

Apparently it’s been a good idea for 10 years, but still not implemented. I have seen soooo much use of long and near unreadable queries that derive temporary tables from other temporary tables then query the final temp table just to work around this large performance bottleneck. In 3 workplaces, have found the same problem over and over again! Allow us to materialize subqueries and CTE’s please!

Looking forward to the day when a simple query like this won’t run once for every row in the Customer table: