What is a Cardinality Estimator anyway?
The Cardinality Estimator (CE) predicts how many rows your query will likely return. The cardinality prediction is used by the query optimizer to generate an acceptable query plan. The more accurate the CE, the more optimal the query plan, at least that is the intent.
There has been a cardinality optimizer in SQL Server since Version 7 but we didn’t hear much about it until Microsoft made major changes to the CE in SQL 2014.
What’s the Problem?
When users upgraded to SQL 2014 or 2016 they found that some specific queries were running much slower than they ran in the previous version. The differences are not trivial. It is not uncommon to see a query that runs in 2 seconds in the earlier version taking 10 minutes in the upgraded version. If you are having problems of this kind, read on. Help is on the way.
Microsoft In Their Own Words
Microsoft has acknowledged the problem but not the severity of the problem for users that are affected. Here is what they said when the problem was first recognized:
“Cardinality Estimator updates for compatibility levels 120 and 130 incorporate assumptions and algorithms that work well on modern data warehousing workloads and on OLTP workloads.”
[However]
“Your application system could possibly have an important query [or queries] whose plan is changed to a slower plan due to the new CE. Such a query might be like one of the following:
- An OLTP query that runs so frequently that multiple instances of it often run concurrently.
- A SELECT with substantial aggregation that runs during your OLTP business hours.
With some help from Microsoft, users found a workaround by changing the database compatibility level to a level before the CE changes were made. (SQL 2012) The workaround might fix t the initial problem but changing the compatibility level also means that you would be unable to use any of the new features in SQL 2014 and subsequent versions. But that is a very high price to pay.
Better Late Than Never
Finally Microsoft stepped in with a fix that is much simpler and still allows you to access the new features in the upgraded version. The name of this fix is Legacy Cardinality Estimation. It gives you the option to use the new CE or use the legacy CE as it existed before the changes were made in 2014. Here is the syntax for enabling or disabling Legacy Cardinality Estimation:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
The downside of this fix is that you have to upgrade to SQL 2016 to use it if you are not already there.
If you are not having problems of the kind described here, leave the new CE alone. It is much better than the legacy version if it does not cause the problems we mentioned.
Here is a URL to a page with more detail on Legacy Cardinality Estimation.