Fix cardinality estimator problems

Execution plans define the strategy used to run a query, and are built by the query optimizer. That optimizer is cost-based: to do a good job, it has to estimate the volume of data the query will handle, which is called cardinality estimation¹.

Getting that estimate right matters. A bad estimate produces a bad plan, and very poor performance.

Cardinality estimation rests on two pillars:

1. statistics, which must be updated regularly;
2. the Cardinality Estimator (CE), which estimates the complex cases from heuristics — that is, from assumptions.

The CE heuristics were largely implemented in SQL Server 7, in the late 1990s. They were completely reworked in SQL Server 2014, and that reworked version is what people call the new CE.

Problems with the new CE

The new CE improves cardinality estimation in many cases, but it also degrades it in a number of others. Migrations to SQL Server 2014 produced plenty of performance regressions in the field.

If you are migrating across the SQL Server 2014 boundary, watch this closely. The new CE is used as soon as you raise the compatibility level of your database to SQL Server 2014 (120) or later. Keeping the compatibility level below 120 keeps the old CE for every plan generated in that database — but you also give up the other query optimizer improvements shipped in later versions.

This query shows the compatibility level of your databases:

SELECT d.name
      ,d.compatibility_level
FROM sys.databases d
WHERE d.database_id > 4
ORDER BY d.name;

Working around the new CE

If you see performance regressions after a SQL Server upgrade, you can keep the database compatibility level and still force the legacy cardinality estimation engine, with a database scoped configuration:

ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;

To check whether the option is enabled:

SELECT *
FROM sys.database_scoped_configurations dsc
WHERE dsc.name = N'LEGACY_CARDINALITY_ESTIMATION';

This keeps the benefit of a recent compatibility level while restoring the estimation behaviour your plans were tuned for.