SQL Server 2014 - some queries very slow (cardinality estimator)

Question

In our production environment we had several servers with SQL server 2012 SP2+Windows Server 2008R2. 3 month ago we migrate all the servers to the SQL Server 2014 SP1+Windows Server 2012 R1. We created new servers with new configuration (more RAM, more CPU, more Disk space) and backup our databases from SQL Server 2012 --> restore to the new SQL Server 2014 servers. After restore we changed compatibility level from 110 to the 120+Rebuild Index+Update statistics.

But now we have problems with several queries which running very slow when compatibility level 120. If we change compatibility level to the old 110 it is running very fast.

I search a lot about this issue, but did not find anything.

Answer 1

SQL Server 2014 introduces new cardinality estimator

One of the performance improvement in SQL Server 2014 is the redesign of cardinality estimation. The component which does cardinality estimation (CE) is called cardinality estimator. It is the essential component of SQL query processor for query plan generation. Cardinality estimates are predictions of final row count and row counts of intermediate results (such as joins, filtering and aggregation). These estimates have direct impact on plan choices such as join order, join type etc. Prior to SQL Server 2014, cardinality estimator was largely based on SQL Server 7.0 code base. SQL Server 2014 introduces new design and the new cardinality estimator is based on research on modern workloads and learning from past experience.

Trace flags 9481 and 2312 can be used to control which version of Cardinality Estimator is used.

Check queries which cause problem and compare execution plans properties estimated number of rows vs actual number of rows values in 2008 and 2014.

Cardinality Estimates in Microsoft SQL Server 2014

---

From SQL Server 2016+ you could set old cardinality estimator per database without using traceflags or changing DB compatibility level to 110.

ALTER DATABASE SCOPED CONFIGURATION

This statement enables the configuration of a number of database configuration settings at the individual database level, independent of these settings for any other database.

LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. This is equivalent to Trace Flag 9481. To set this at the instance level, see Trace Flags (Transact-SQL). To accomplish this at the query level, add the QUERYTRACEON query hint.

ON

Sets the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version of the cardinality estimation model.

OFF

Sets the query optimizer cardinality estimation model based on the compatibility level of the database.

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;