So first things first before I bombard with Jargons on SQL to a non-SQL ConfigMgr admin.
What is CE or Cardinality Estimation?
The CE predicts how many rows your query will likely return. The cardinality prediction is used by the Query Optimizer to generate the optimal query plan. With more accurate estimations, the Query Optimizer can usually do a better job of producing a more optimal query plan.
If you are reading this, I hope you might have already come across scenarios in ConfigMgr where you have had to manually change the Cardinality Estimator in SQL to a lower level or Legacy version which makes the performance better at times.
Why does this happen?
The ConfigMgr Provider queries have been written and tested with older version of SQL. So it is possible that a provider query on a new SQL version chooses not so optimal execution plans.
Given the nature of the issue, It was technically not feasible to test innumerable SQL queries for each SQL version and correct the code for each of them.
So what did we do then?
ConfigMgr team decided to simply run the Provider queries on a lower CE level (110) on which they are supposed to be performing well.
How is it implemented?
We simply want ConfigMgr Admins not touching those CE levels and hence we let ConfigMgr code do the best for us.
On the remote providers you will now see a registry