This comes up during Office Hours once a week
Which pretty much gives it a 100% hit rate. I’m writing this mostly to have something to reference when people ask.
Trace flags
If you restore a database on a SQL Server 2014 or greater instance, you have choices to make. You can either set the compatibility level to 120 (or 130, if 2016), and your queries will start using the new CE. If you leave it at a compatibility level below 120, it will continue to use the old one. You can change this at the query level by hinting with Trace Flags.
9481 will force the old CE in >= 120 database
2312 will force the new CE in a < 120 database
This gives you the ability to test queries, ALL OF YOUR QUERIES, with the new optimizer to check for regressions.
What’s the difference?
The new CE no longer assumes data independence. That means if you were searching a city and state combination, it would make no fuss about the relationship between the two. That doesn’t quite make sense, does it? The new CE aims to fix that, but it’s not always right either.
For more detailed write-ups on the inner workings, here are some links:
- MSDN’s coverage of the old & new CEs
- Joe Sack’s awesome white paper
- CSS blog post on the new CE, Part 1
- CSS blog post on the new CE, Part 2
Optimizer guarantees
Microsoft has also made guarantees that you’ll no longer face regressions when upgrading, because you won’t be using the newest CE unless your database compatibility level is set to the latest version. If you’re on 2016, you get some extra power over this with the Query Data Store.
Thanks for reading!
Brent says: this is one of the features we’ll be demonstrating during the Performance Overhead of Various Features session at Dell DBA Days 2016. Just by flipping one switch at the database level, you can get different query plans – so it’s important to know whether things are getting better or crazy better. (Okay, or also worse. But let’s think positive.)
13 Comments. Leave new
We are using SQL Server 2014 SP1 CU7 – does that contain the New 2014 CE?
Yep. This applies to all versions and editions of 2014.
My favorite feature in the new CE is how it handles the ascending key problem.
Yeah! Erin Stellato has a pretty cool blog with some more details on that over here.
What is the long term impact of this? Will we have SQL Server 2028 running with databases in 2012 compatibility mode? Don’t we just have to bite the bullet and live with the new CE plans?
Try putting a database on a 2016 instance in 2005 compatibility mode and let me know 😉
Yes, my point being that at some point, that option of the older compatibility mode won’t exist. So we will be stuck with the new CE anyway. So we might as well switch to it.
Oh, yeah. It’s worth preparing for. I think some (most) people will be rather less Gung-ho about switching all at once. Hopefully by the time SQL Server 2028 arrives the optimizer and CE will be at a point where regressions et al are rare. Perhaps something like Oracle’s adaptive plans? A guy can dream.
Any idea if SQL 2016 SP1 defaults to CE model version 130 for dbs in compatibility 100? I’ve been seeing that in some initial performance testing. DBCC TRACESTATUS() is not showing any flags as being active.
John – look at the execution plan and you’ll be able to see the CE model. It’s one of the properties.
Feel free ignore my last comment. Was running my query from the context of the master database. Sorry bout that.
An older post, but the information was there that I needed. Instance A is 2014, and a database is in compatibility mode 2008. Instance B is 2008 R2, with a database in compatibility mode 2000. A linked server call from the database on A to the database on B works fine. We migrated instance B to 2017 (finally) and the database was automatically upgraded to compatibility 2008.
The linked server call performance from A to B tanked. I implemented the OPTION (QUERYTRACEON 2312) hint in the code and performance when back to normal. Any insights into this scenario?
It’s beyond what I can troubleshoot quickly in a blog post comment, but if you need my personal help, feel free to hit Consulting at the top of the site. Thanks!