Share via

Example of simple Containment vs Base Containment

sakuraime 2,341 Reputation points
2020-11-12T16:19:45.687+00:00

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.

https://support.microsoft.com/en-us/help/3189675/join-containment-assumption-in-the-new-cardinality-estimator-degrades

what's the example of the above and really show statement is improved?

SQL Server Other

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2020-11-12T22:58:49.403+00:00

    I seem to recall that you have asked about this before, and I seem to recall that I linked you to Joe Sack's white paper on the new cardinality estimator. Here it goes again: https://learn.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN

    It was a while since I read it, but I would expect it to be covered there.

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.