Give this article a Google +1!
Issue 7 Number 1 January 2017Tweet This Article.
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.
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 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."
"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:
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.
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:
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.
I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.
As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.
Kurt Survance, SQL Consulting, Inc.
On this page:
Elsewhere on this site: