As SQL Server started evolving and improving its cardinality estimation (CE) model from version to version separating models, it would be nice to have an explicit way to tell the query optimizer which version it should use. So far we have trace flags (TF), compatibility_level and database scope configuration setting to manage CE version. However, as the number of SQL Server version grows, it becomes harder to remember: under which setting which TF produces which result.
It would be much easier to write something like this:
select [something] from [table] option(CardinalityEstimationModelVersion 120);
I see the following advantages:
- More readable and understandable query code
- Removed tricky business with user rights when using querytraceon not under sa
As a second step it will be also nice to force particular CE model version of the whole module. Typically a lot of real production systems have complex procedures containing a lot of queries. To test the procedure under new CE you should apply query hint to every single query, and if tests are unsuccessful, remove it back. It would be nice if we could manage CE version of the whole module in one single step.
The other cool improvement I see, is to apply this to mechanism, similar to plan forcing in Query Store. This will give an opportunity to force particular CE model version even without touching the query, that is crucial in some situations. It would be nice to have something like sp_query_store_force_ce @query_id, @ce_version (analogy with sp_query_store_force_plan).