The query optimizer’s purpose is to select the best strategy for answering a query. It attempts to achieve this goal by assigning a cost to each potential plan and choosing the plan with the lowest cost. The cost of a plan depends on several different factors, but the most important factor is the number of rows processed by each stage in the plan. The process of getting the number of rows to be processed is referred to as “Cardinality Estimation” or “CE”.
Why was it rewritten? There were some core assumptions that were outdated based on how users write queries and what the data looks like, plus there were some CE issues that needed to be addressed.
How to Enable the new CE
1.Enable at server level: Trace flag 2312. Note that if you enable trace flag 2312, all queries will be forced to use new CE. Every database will use new CE.
2.Enable at database level: set the database compatibility level to 120. This enables the new CE to be used at the database level. No other databases will use the new CE until their compatibility level is changed to 120.
3.Enable at query level: option (QUERYTRACEON 2312)
How to Disable the new CE and revert to old CE
1.Disable at server level: Trace flag 9481. Note that if you enable trace flag 9481, all queries will be forced to use old CE. Every database will use old CE.*
2.Disable at database level: set the database compatibility level to < 120. This enables the old CE to be used at the database level.
3.Disable at query level: option (QUERYTRACEON 9481)
*Note: If you enable 9841 at server level, then all queries will be forced to use old CE unless you have QUERYTRACEON for a particular query.
What happens when you enable both trace flag 9481 and 2312?
If you enable both trace flags, neither will be used to determine or decide which CE will be used. Instead, database compatibility level will be used to decide.
New CE has not made any improvement in the following known constructs:
1. Table variable (estimates 1 row)
2. Multi-statement table valued function (TVF) (estimates 1 row)
3. Table valued parameter (TVP) (rows won’t change upon first compile)
4. Local variable (estimates as ‘unknown’)
5. Recursive CTE (generally very low estimate)
6. Parameter sniffing