Share via

Using DB Compatibility Level 130 with Old CE in SQL Server 2016

  • Article

In SQL Server 2014 we introduced a revamped Cardinality Estimator (CE), which we further improved in SQL Server 2016 and Azure SQL Database. With the new CE most workloads are seeing better query plans and thus improved performance.

However, there are some workloads that experience plan regressions and thus performance degradation under new CE. To keep using the old CE you would either use the DB compatibility level 110,  or fiddle with trace flags, which you probably don't want or can do.

In SQL Server 2016 there are a lot of goodies under Compatibility Level 130, such as performance improvements with Columnstore and In-Memory OLTP and all the Query Optimizer enhancements done over the years under Trace Flag 4199. So we are faced with a situation where there are workloads that can benefit from some enhancements that come with Compatibility Level 130, but that cannot use the new CE.

The solution is to use one of the new database-scoped configuration options, namely the Legacy Cardinality Estimation option. You can enable this using the following ALTER DATABASE command:

 ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON

If you set this option, and you set Compatibility Level to 130, you get all the performance benefits for query execution that come with SQL Server 2016, while still using old CE.

Warning: only use this option if the performance of your workload in a specific database scope is significantly worse with new CE, when compared with old CE.

Comments

  • Anonymous
    April 27, 2016
    I'm disappointed by SQL Server 2016's query planning capabilities. It still doesn't take into account trusted foreign keys when they're composite. I tested RC2 with the database at the highest compatibility level. I haven't tested RC3, but I can't imagine it changing before release.
    • Anonymous
      May 05, 2016
      The comment has been removed
      • Anonymous
        May 11, 2016
        Hi,Sorry for the delay.I've got a demo of the behaviour below. I don't have access to RC3 right now but it demonstrates the problem on RC2. I'm holding out for RTM but I don't expect it will be improved by then.https://gist.github.com/taspeotis/29dd509a2022cd43c7c54bc3c4b15162We have a schema that's pretty heavy on composite keys. Anything that eliminates redundant joins is helpful.Thanks for the interest.
  • Anonymous
    February 14, 2018
    I don't understand how the new CE setting should impact performance while operating at the 130 CL. We've upgraded from 2005 to 2016 and noticed significant performance issues in several areas of our applications. The estimated plan looks quite different in the upgraded environment. Query times have gone up from a second or two to 30+ in 2016. After enabling the Legacy CE, the performance is back on. However, I would like to know if this is a setting we have to keep permanently?
    • Anonymous
      February 14, 2018
      You do not need this setting permanently. I would recommend you enable query store and run with old CE. After a while you can switch to new CE, and you can consult the query store reports to find any regressions, and even force old plans. You can then address the regressed queries on-by-one. For these individual queries that have regressed with new CE, you can either refactor the queries/indexes/stats such that the queries perform well with new CE, using the query hint FORCE_LEGACY_CARDINALITY_ESTIMATION, or continue to use the plan forcing of query store, though the latter is not recommended in the long term.