Join containment assumption in the New Cardinality Estimator degrades query performance in SQL Server 2014 and later

Applies to: SQL Server 2016 EnterpriseSQL Server 2016 DeveloperSQL Server 2016 Web


Consider the following scenario:

  • You are using Microsoft SQL Server 2014 or a later version.
  • You run a query that contains joins and non-join filter predicates.
  • You compile the query by using the new Cardinality Estimator (New CE).
In this scenario, you experience query performance degradation.

This problem doesn't occur if you compile the query by using the Legacy CE.


In SQL Server 2014 and later versions, you can use trace flag 9476 to force the New CE to use the Simple Containment assumption instead of the default Base Containment assumption. (See the "More Information" section.)

Enabling this trace flag may improve query plan choice without having to fully revert to the Legacy CE model if the following conditions are true:

  • You experience a suboptimal query plan choice that causes an overall degraded performance for queries that contain joins and non-join filter predicates.
  • You can verify a significant inaccuracy in a "join cardinality" estimation (that is, the actual versus estimated number of rows that differ significantly).
  • This inaccuracy does not exist when you compile queries by using the Legacy CE.

You can enable this trace flag globally, at the session level, or at the query level.

Note Using trace flags incorrectly can degrade your workload performance. For more information, see the "Introduction" section of the following Microsoft Knowledge Base article:

2801413 Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level

More Information

From SQL Server 2014, the New Cardinality Estimator was introduced for database compatibility level 120 and greater. The New CE changes several assumptions from the legacy CE in the model that is used by the Query Optimizer when it estimates cardinality for different operators and predicates.

One of these changes is related to join containment assumption.

The Legacy CE model assumes that users always query for data that exists. This means that, for a join predicate that involves an equijoin operation for two tables, the joined columns exist on both sides of the join. In the presence of additional non-join filter predicates against the join table, the Legacy CE assumes some level of correlation for the join predicates and non-join filter predicates. This implied correlation is called Simple Containment.

Alternatively, the New CE uses Base Containment as the correlation. The New CE model assumes that users might query for data that does not exist. This means that the filter predicates on separate tables may not be correlated with each other. Therefore, we use a probabilistic approach.

For many practical scenarios, using the Base Containment assumption creates better estimates. This, in turn, creates more efficient query plan choices. However, in some situations, using the Simple Containment assumption may provide better results. If this occurs, you may experience less efficient query plan choice when you use the New CE instead of the Legacy CE.