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.
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
4 answers
Sort by: Oldest
-
-
Erland Sommarskog 102.3K Reputation points
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.
-
CathyJi-MSFT 21,096 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.
-
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?