sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

Big Performance Problems with the Cardinality Estimator

Issue 7 Number 1 January 2017

 

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.

Afterward by the Author

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.

Return to Top

Go to SQL Consulting Home

On this page:

Big Performance Problems with the Cardinality Estimator

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator