Using the SQL Server 2014 Database Compatibility Level without the New CE

Consider the following scenario:

You want to migrate all databases on a specific SQL Server instance to a new SQL Server 2014 instance
You want to leverage new SQL Server 2014 functionality and move to the latest database compatibility level
You don’t want to enable the new Cardinality Estimator right away (various reasons why this might be – but perhaps you didn’t have time to fully test key workloads)
You don’t want to have to manually add QUERYTRACEON hints for specific queries

To accommodate this scenario, you can do the following:

Change the migrated databases to COMPATIBILITY_LEVEL = 120 in order to leverage the SQL Server 2014 database compatibility level
Enable trace flag 9481 at the server-level as a startup trace flag (or via DBCC TRACEON – but remembering this doesn’t persist on restarts unless you re-execute)

Trace flag 9481, for using the legacy CE behavior and trace flag 2312, for using the new CE behavior are both fully supported and documented by Microsoft here:

Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level

That KB article focuses mostly on QUERYTRACEON – but the CE trace flags can apply at the server-level scope as well.

There are other combinations of CE enabling/disabling that you can use, depending on your requirements, but I just wanted to point out what I think will be a more common scenario.

Other articles

New Course: “SQL Server: Common Query Tuning Problems and Solutions – Part 2″

Today, Pluralsight published my latest course, “SQL Server: Common Query Tuning Problems and Solutions – Part 2“. Part two is 2 hours and 17 minutes long

New Course: “SQL Server: Common Query Tuning Problems and Solutions – Part 1”

Pluralsight published my latest course, “SQL Server: Common Query Tuning Problems and Solutions – Part 1“. Part 1 is 2 hours and 31 minutes and

SQLIntersection Post-Conference Session

I am really happy to announce that Kimberly Tripp and I will be delivering a post-conference in November’s SQLIntersection conference at the MGM Grand. The

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

I’ve been working on writing a new Microsoft white paper since January covering the main changes made in the SQL Server 2014 Cardinality Estimator, and

Finding Timeout-Prone Code with max_elapsed_time

Consider a scenario where your application timeout setting is 30 seconds and you’ve been asked to proactively start tuning workloads that are at risk of

Deck from “Practical SQL Server Cardinality Estimation”

I uploaded the deck from my session for SQLSaturday #287 Madison 2014 and you can download it here. It was an excellent event! Great organizers,

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.