How To Turn Off the New 2014 Cardinality Estimator
Published: Dec 04, 2016
Before I start, I want to point out that I like the new estimator and it does have its advantages over the old one, BUT because it does produce different estimates in certain circumstances it can cause you to get completely different execution plans and therefore, as part of upgrade testing, you will likely need to turn it on and off, hence this post.

Therefore we’re back to “Fun with Flags” as the main method I use happens to be a Trace Flag. There is another method which also produces the same outcome and I’ll discuss that too… but I do like my Trace Flags, so it’s going under that heading.

Firstly we’ll need a query that performs differently between SQL 2012 and SQL 2014… I’ve chosen this one…

use AdventureWorks2012
go

select *
from sales.SalesOrderHeader
where orderDate between '2005-01-01' and '2005-12-31'
and subTotal between 10000 and 20000


Firstly though, remember to ensure that you’re in SQL Server 2014 compatibility mode and therefore using the new CE:

use master
go

alter database AdventureWorks2012 set compatibility_level = 120
go


I’ll explain why in a later post, but for the moment here are the two plans:

Old CE:

2014 CE:

As you can see, there is a clear difference in the estimated outcome of the procedure.

As it turns out, the new CE is actually pretty close too:

I’ll explain why in a later post, but for the moment we just need to know that they provide different results and therefore this type of estimate variance could mean we get vastly different plans in our production environments.

So how can we see the differences? We can use TF 9481 (I’m doing so at the query level) and that will tell SQL Server to use the old CE for the query:

use AdventureWorks2012
go

-- Old CE
select *
from sales.SalesOrderHeader
where orderDate between '2005-01-01' and '2005-12-31'
and subTotal between 10000 and 20000
option (querytraceon 9481)

-- New CE
select *
from sales.SalesOrderHeader
where orderDate between '2005-01-01' and '2005-12-31'
and subTotal between 10000 and 20000


And that’s it… you can now do a direct and simple comparison between the plans from the old and new CE.

The other method of doing this is a little more brute force (as it’s database level) and is harder to make comparisons in the output… but you just have to change the compatibility of the database to something below 120:

use master
go

alter database AdventureWorks2012 set compatibility_level = 110
go


However, this does have a major disadvantage in that this will wipe the entire plan cache for your database which may not be ideal if you just want to check one query whilst allowing the rest of the test server to perform as normal.

You can check this using the following query (changed for your database, of course):

select count(*)
from sys.dm_exec_cached_plans c
cross apply sys.dm_exec_query_plan(c.plan_handle) p
where p.dbid = db_id('AdventureWorks2012')


Therefore, personally, I would much rather use the TF in my testing as it’s much kinder to the system as a whole and produces the same result.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron