Deprecation plan for LEGACY_CARDINALITY_ESTIMATION?

Saurabh Tyagi 26 Reputation points
2021-09-13T17:51:46.523+00:00

Hi all,

We are planning to turn on LEGACY_CARDINALITY_ESTIMATION on our SQL Database to fix some performance issue and we want to consider this as permanent solution. Does anyone know any plan from Microsoft to deprecate this feature in future SQL Server releases or this will be always available?

Thank you

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,697 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
494 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2021-09-14T02:18:39.487+00:00

    Hi @Saurabh Tyagi ,

    No, there is no plan from Microsoft official document that announce to deprecate this feature till this moment.

    If a feature will be deprecated, MS will announce this in MS document Cardinality Estimation (SQL Server) or Deprecated database engine features in SQL server or Discontinued database engine functionality in SQL Server.

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

    1 person found this answer helpful.
    0 comments
  2. Tom Phillips 17,716 Reputation points
    2021-09-13T18:55:41.08+00:00

    Using LEGACY_CARDINALITY_ESTIMATION is not a "permanent solution". This is only meant to help diagnose the issue and try to fix the new estimator. At any point this could be removed, even though it has not been marked as depreciated yet.

    0 comments
  3. Erland Sommarskog 100.9K Reputation points MVP
    2021-09-13T21:23:33.457+00:00

    I agree with Tom. Sooner or later, you will run into situations where you will get better plans with the new cardinality estimator.

    Possibly you can set it for a specific query with help of hint, but I would not consider that a long-term solution either.

    That said, I have not seen any indications that Microsoft plan to remove the old cardinality estimator any time soon.

    0 comments
  4. Saurabh Tyagi 26 Reputation points
    2021-09-14T12:30:04.607+00:00

    Thanks @Tom Phillips & @Erland Sommarskog for suggestion. In our case, not a single query is impacted but multiple areas throughout the database that is why we are going with this option. Also, Microsoft recommended the same in point two under "How to activate the best query plan". Can you think of any other reason except Microsoft can deprecate this feature in future?