data-konzept

'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.'...

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/04/27/using-db-compatibility-level-130-with-old-ce-in-sql-server-2016/