Michael J. Swart

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.

What’s a Cardinality Estimator (CE)?

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, 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.

Risk of Regressions

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.

Enabling the New CE – the Official Details

Simply put, CE behavior can be controlled using the compatibility mode and/or trace flags:

• The new CE is enabled when compatibility mode is 120 and disabled when it is less than that. The compatibility mode of a database is not modified automatically during an upgrade to 2014, so remember to adjust it accordingly.
• New trace flags are introduced. Trace flag 9481 can force SQL Server to use the legacy CE when it would otherwise use the new one. Conversely, trace flag 2312 can force SQL Server to use the new CE. And if flags 9481 and 2312 are ever both enabled (in any context), then neither flag takes effect. They cancel each other out and the CE behavior is determined only by the compatibility mode.

Just those two things allow you to influence the CE behavior depending on the granularity you require:

• For a single query – You could use the QUERYTRACEON hint but it’s not a tempting option. Sysadmin privileges or a forced plan are required.
• Based on your session – Use session trace flags (again, sysadmin privileges are required).
• Based on the database you’re connected to – Use compatibility mode.
• For the whole server – Use global trace flags.

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.

Corner Use Cases

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
GO
 
-- any query (regardless of participating tables) will now use the new CE. e.g.:
SELECT COUNT(*) 
FROM Adventureworks2012.Sales.SalesOrderHeader;

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
GO
DBCC TRACEON( 9481 );
 
SELECT COUNT(*) 
FROM Sales.SalesOrderHeader
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.

How I Plan To Adopt the New CE

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:

1. Use hints (whether that means index hints, join hints or query hints) to stabilize the plan temporarily.
2. Spend time tuning or rewriting the query so that it performs well without these hints.

Further Reading

• Joe Sack’s Whitepaper Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
• Joe Sack’s CE blog posts at SQLSkills Cardinality Estimation
• Kendra Little’s The SQL 2014 Cardinality Estimator Eats Bad TSQL For Breakfast
• Paul White’s Cardinality Estimation for Multiple Predicates