The Cardinality Estimator in SQL Server 2014 – Going Forward and Backward

I saw a talk from Joe Sack (b | t) on the cardinality estimator (CE) in SQL Server 2014 and found it very interesting. To be fair, some of the "how it works" isn’t something I care about much, but I did like Joe sharing some places in which you might find problems with your queries and how the cardinality estimator might affect you. The talk is worth seeing if you get the chance.

However one of the really interesting things, and an item I appreciate Microsoft building, is a switch to turn off the new CE. Actually, it’s not turned off, but you can set it to pre-SQL Server 2014 behavior (essentially 2005-2012) or to SQL Server 2014 behavior. What’s even better is that you can set this in a number of ways.

Setting Database Behavior for all Queries

Turning on the new CE is as simple as setting the compatibility level to 120. This will turn on the new CE for your queries in this database.

The flip side is setting your compatibility level to something below 120 (110, 100, etc.) and your queries will use the old CE behavior in the query optimizer.

Setting Behavior for Queries

You can also specifically test queries with either the new or old CE. The QUERYTRACEON option can be used with these two flags.

• 9481 – Uses the 2012 (pre-2014) CE with queries. This is used when the database is in SQL Server 2014 (compatibility mode 120) mode.
• 2312 – Uses the new 2014 CE when the database, or defaults, are set to use the 2012 CE.

This is documented in KB 2801413 from Microsoft.

Setting the Server Level

There is a trace flag that you can use at Server startup that globally sets the CE behavior. Set 9481 at startup and your SQL Server 2014 databases will use the old CE by default. Joe documented this on his blog.

Usage

I think this is great because if you are concerned about workloads being effected by the CE changes, then you can set the old CE as the default and test on your real production server by executing specific queries with the new CE and the query plan.

Alternatively, if you upgrade and find problems, you can duplicate the old CE by using a query hint and see if the query performs better.

I’d like to see this upgrade/downgrade granularity in more features that can potentially affect performance and I would say this is a fantastic architectural win by the SQL Server development team.