Using DB Compatibility Level 130 with Old CE in SQL Server 2016
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.