Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Introduction:

Microsoft re-written its legacy Cardinality Estimator in SQL server 2014 to help provide more accurate data to optimizer to prepare more efficient query plan thus helps execution much faster than version less than SQL server 2014.

But there is a more story to tell about it in practical scenario for some of the client’s and some of the application environment.

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.

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.

and that’s the exactly happened for one of our client environment where we migrated dbs. from SQL server 2014 to SQL server 2017 in a hope that database compatibility will also get up and optimizer will use the new CE to prepare best plan and expedite query execution as per the Microsoft theory.

But this never happened and we had to force SQL server to use legacy CE for their application where it started performing well enough. Legacy CE is nothing but either DB compatibility less that 120 or use enable trace flag 9481 on server level.

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.

How to Enable:

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.

Thank you!