0

I have a [SQL query] for SQL Server 2019.

It works fine with option

(USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')) and very bad without this hint.

I found out, that SQL Server 2014 Cardinality Estimator estimate final number of rows after OUTER JOIN is less than number of rows from initial table without any WHERE predicates and with option (recompile).

New CE Plan

Legacy Cardinality Estimator estimate final number of rows after OUTER JOIN is equal or more number of rows from initial table (correct).

Old CE Plan

Is this a bug of SQL Server 2014 Cardinality Estimator or I do something wrong?

1 Answer 1

3

Cardinality Estimation is just an estimation. Many times it won't be the exact number of rows, for a multitude of reasons. But if it is, great, that means the SQL Engine was able to do its job properly.

A cardinality under-estimate is essentially just as a detrimental as an over-estimate by the same proportional amount. In either case, it's not usually a problem until the difference is off by an order of magnitude or more.

An over-estimate may result in too many resources (e.g. Memory) being requested to serve the query. In bad enough cases, this will detract resources from the server that could've been used by other queries, and may take longer to acquire for the current query itself, which results in some waiting on those resources. As Sranda points out, a single query can request up to 25% of the SQL Server instance's Max Server Memory setting, just to hog it all to itself for that execution.

An under-estimate has the opposite effect. Not enough resources will be requested, and in bad enough cases, will cause the operations of the execution plan for that query to run slower, because they don't have enough resources to process. This becomes a bottleneck of executing the query then.


By the way, you should use Paste The Plan to upload your execution plans to. It's a much more reliable resource than whatever you're currently using.

6
  • 2
    I appreciate the length of your answer, but it doesn’t do much to explain cardinality estimation for joins, particularly under the different CE models. SQL Server Join Estimation Using Histogram Coarse Alignment May 22, 2023 at 17:37
  • 1
    @ErikDarling I appreciate your comment and will take a look at that resource. 🙂
    – J.D.
    May 22, 2023 at 18:07
  • 1
    I would just like to second to J.D.'s answer and add that one query request to use up to 25% of SQL server's memory and as J.D. states, elbow out other queries' resources from the RAM.
    – Sranda
    May 23, 2023 at 9:11
  • @Sranda Good point. I added that bit in to the answer. 🙂
    – J.D.
    May 23, 2023 at 12:03
  • 1
    @J.D. I appreciate your comment May 31, 2023 at 4:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.