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.

Related Posts