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:
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.