Join containment assumption in the New Cardinality Estimator degrades query performance

This article helps you resolve performance problems that can occur in SQL Server 2014 and later versions when you compile your queries using the new cardinality estimator.

Original product version:   SQL Server
Original KB number:   3189675

Symptoms

Consider the following scenario:

  • You are using 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 Estimation (SQL Server) (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.

Resolution

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 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 Hints (Transact-SQL) - Query.

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.