Freigeben über

Query Optimizer Additions in SQL Server 2016

  • Artikel

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.

Comments

  • Anonymous
    May 23, 2016
    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).See the example here: https://gist.github.com/taspeotis/29dd509a2022cd43c7c54bc3c4b15162SQL Server is a great product, but I am frequently frustrated by this lack of optimization! (The schema I deal with is heavy on composite keys.)
    • Anonymous
      May 24, 2016
      Thanks for highlighting this possible issue. We are already looking into this and investigating whether/when we can provide an optimization for similar cases.
      • Anonymous
        October 09, 2016
        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!
  • Anonymous
    May 24, 2016
    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.
  • Anonymous
    May 24, 2016
    Glad to see such wonderful set of optimizer improvement.Especially the massive improvements on the 'batch processing' side, and also memory-optimized tables. Great Job!
  • Anonymous
    May 26, 2016
    +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? Thank you!
    • Anonymous
      November 25, 2016
      I wonder where did you find "custom folders for db objects" in SSMS?
  • Anonymous
    May 29, 2016
    The comment has been removed
    • Anonymous
      May 31, 2016
      At this moment, we do not have a way to set the MAXDOP for a UPDATE STATISTICS statement. We are considering addressing this limitation. Please vote on the following Connect to help us prioritize this item accordingly: https://connect.microsoft.com/SQLServer/feedback/details/628971/add-maxdop-parameter-to-update-statsAlso, 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.
  • Anonymous
    June 01, 2016
    The comment has been removed
    • Anonymous
      July 01, 2016
      The comment has been removed
  • Anonymous
    June 07, 2016
    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.Why is that?https://msdn.microsoft.com/en-us/library/ms191158.aspx
    • Anonymous
      July 01, 2016
      Thank you for highlighting this discrepancy. We will update the documentation soon.
  • Anonymous
    June 11, 2016
    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?This operator is not yet listed in the showplan logical/physical operators list here:https://msdn.microsoft.com/en-us/library/ms191158.aspxThanks,-Ed
    • Anonymous
      July 01, 2016
      There are some restrictions (noted above and documented here https://msdn.microsoft.com/en-us/library/ms189049.aspx). If you still have issues, please reach out to GGjorgji@Microsoft.com and we will closely look at your scenario.
    • Anonymous
      July 13, 2016
      You need 254 FK references to the object for the operator to appear. I've noted some really strange behavior with this when you put SET STATISTICS TIME ON and compare between 120 and 130 comp mode.
  • Anonymous
    July 03, 2016
    Am always amazed that query optimization does not include a way to either tell SQL server to materialize subqueries/CTE's temporarily for performance:https://connect.microsoft.com/SQLServer/feedback/details/218968/provide-a-hint-to-force-intermediate-materialization-of-ctes-or-derived-tablesApparently 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:WITH MaxOrderID AS ( SELECT CustomerNumber, MAX(OrderID) as LatestOrder FROM tblOrders GROUP BY CustomerNumber) --MATERIALIZE hint here!SELECT c.CustomerNumber, c.x, c.y, c.z, m.LatestOrderFROM tblCustomers cINNER JOIN MaxOrderID m ON c.CustomerNumber = m.CustomerNumber