Takeaway: SQL Server 2014 will make use of its newly re-written Cardinality Estimator when the database’s compatibility mode is at least 120. But there’s more to the story.
Say you’ve been hired to phone everyone on a particular list. If it’s a list of all Americans taller than seven feet, you might manage quite well on your own. But if it’s a list of all Americans shorter than seven feet, you’ll probably need help from others. That’s not surprising because the sizes of the lists are wildly different. One list could have 300 people on it and the other could have 300 million. The expected sizes of the lists influence how you tackle this problem.
SQL Server does the same thing. It uses statistics to find the best ways to execute queries. To find a good query plan, the SQL Server often needs to make many choices (which join type, join order, parallelism etc…) It needs to estimate the cost of each choice and it uses educated guesses to evaluate these costs. That’s what the CE was built to do. It provides educated guesses about the number of rows a query plan has to process. That’s why it’s called the cardinality estimator. The accuracy of these estimates will influence the quality of query plans, and consequently, the performance of queries.
With SQL Server 2014, Microsoft released a rewritten version of SQL Server’s CE. I can’t wait to take advantage of it. I’m looking forward to tuning fewer poorly performing queries. Queries that seem to be written well, but are vulnerable to bad query plans.
The CE is part of the query optimizer, so the rewrite represents a significant change to the database engine. And with any pervasive change, there’s always a risk of regressions. While rare, some workloads are expected to perform worse with the new CE. Joe Sack’s excellent white paper Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator has some essential tips and suggestions on how to assess and deal with these potential regressions.
Some users may want to continue using the legacy CE. And some users may want to decouple the adoption of the new CE with the adoption of SQL Server 2014. Microsoft anticipated this and so they give DBAs a choice. DBAs have the option to either use the new CE or to stick with the legacy CE.
Simply put, CE behavior can be controlled using the compatibility mode and/or trace flags:
Just those two things allow you to influence the CE behavior depending on the granularity you require:
Again, Joe Sack’s white paper explains this in more detail. He provides syntax examples and methods to determine which CE was used based on a query plan.
This leads to some surprising behaviors:
Connect to a System Database to Avoid Compatibility Mode issues
For example, this works:
use master -- in SQL Server 2014, master will always be at compatibility mode 120
-- any query (regardless of participating tables) will now use the new CE. e.g.:
But it’s just a trick and not a technique I would recommend. Besides, this trick doesn’t work when calling stored procedures from other databases.
Using a Trace Flag to Cancel Another One
Trace flags 2312 and 9481 don’t play together well. There is no scenario where one takes precedence over the other. If they’re both enabled, then they cancel each other out:
use Adventureworks2012 -- at compatibility mode 110
DBCC TRACEON( 9481 );
OPTION( QUERYTRACEON 2312 ); -- 2312 normally enables the new CE
-- the 2312 hint is canceled by the 9481 trace flag, the legacy CE is still used.
Again, I avoid this scenario so that I don’t need to worry.
I’d like to begin using the new CE as soon as I upgrade to 2014.
But if I wanted to, I would feel comfortable using compatibility mode as a feature toggle for the new CE. There are other behavior differences between compatibility modes 110 and 120. But I don’t use them and won’t encounter them. They’re obscure and easy to review. So for me, I can ignore those other features and use compatibility mode 120 as the CE feature toggle.
The trace flags 2312 and 9481 are new in SQL Server 2014. So if SQL Server is not at version 2014, it will ignore those trace flags. I intend to do the same no matter what version I’m using. I don’t expect to see many queries showing serious regressions with the new CE, but if I encounter any I’m not going to manage them with these trace flags. Instead, I plan to: