question

SaurabhTyagi-5579 avatar image
1 Vote"
SaurabhTyagi-5579 asked ErlandSommarskog commented

Deprecation plan for LEGACY_CARDINALITY_ESTIMATION?

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-generalsql-server-migration
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered Cathyji-msft edited

Hi @SaurabhTyagi-5579,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SaurabhTyagi-5579 avatar image
0 Votes"
SaurabhTyagi-5579 answered ErlandSommarskog commented

Thanks @TomPhillips-1744 & @ErlandSommarskog 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?


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There have been many updates since this was introduced. Make sure you are testing with the most current version.

https://docs.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level

Eventually, this will not be needed. I do not recommend using this option in Production, unless the performance is unusable. Just keep up with the patches.

0 Votes 0 ·

Beside deprecation one thing to keep in mind that there may be future enhancement in the optimizer that will not eligible to you if you go with the legacy cardinality estimator.

My experience is that when you get a better plan with the old estimator there is often something problematic with the query or the available indexes. But obviously, since estimation is a guessing game, the old estimator may have a model that fits better for your database.

0 Votes 0 ·