- 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).
This problem doesn't occur if you compile the query by using the Legacy CE.
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:
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.