In SQL Server 2014 CTP2 in the AdventureWorks2012 database, execute the following batch:

 
USE [AdventureWorks2012];
GO

SELECT [PersonID], [FirstName], [LastName], [JobTitle], [BusinessEntityType]
FROM [dbo].[ufnGetContactInformation](2)
OPTION (QUERYTRACEON 9481);  -- Legacy CE

SELECT [PersonID], [FirstName], [LastName], [JobTitle], [BusinessEntityType]
FROM [dbo].[ufnGetContactInformation](2)
OPTION (QUERYTRACEON 2312); -- New CE

The first query uses the legacy cardinality estimator and the second query uses the new cardinality estimator.   Both queries are referencing a multi-statement table valued function.

Looking at the plan tree view in SQL Sentry Plan Explorer for the legacy CE plan, you’ll see the following (estimating 1 row for the function operators):

Looking at the new CE version of the plan tree, we see the following (estimating 100 rows for the function operators):

SQL Server 2014 uses a new default fixed cardinality for multi-statement table valued functions.

A few thoughts:

• Whether 1 row or 100 rows, we’re still using a fixed guess that may or may not reflect reality
• I’m very wary of using MSTVFs in scenarios where the estimate is critical for plan quality (and oftentimes it is)

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.

Testing on SQL Server 2014, CTP2, version 12.0.1524, imagine you have two databases – one called T1 and one called T2 configured with database compatibility levels as follows:

USE [master];
GO

ALTER DATABASE [T1] SET COMPATIBILITY_LEVEL = 120;
GO

ALTER DATABASE [T2] SET COMPATIBILITY_LEVEL = 110;
GO
 

Now with database T1, we know that using a compatibility level of 120 means we’ll be using the new cardinality estimator (CE) – assuming we’re in the database session context of a new CE database and don’t have a trace flag disabling the new CE behavior.

Executing the following query in the context of T1 does indeed mean we use the new CE:

USE [T1];
GO

SELECT    [member_no],
[provider_no],
[category_no]
FROM [dbo].[charge]
WHERE [charge_no] = 422;
 

<StmtSimple StatementCompId=”1″ StatementEstRows=”1″ StatementId=”1″ StatementOptmLevel=”TRIVIAL” CardinalityEstimationModelVersion=”120″ StatementSubTreeCost=”0.0032831″ StatementText=”SELECT [member_no],[provider_no],[category_no] FROM [dbo].[charge] WHERE [charge_no]=@1″ StatementType=”SELECT” QueryHash=”0x274BD0D496403EEE” QueryPlanHash=”0x6B5F27FE55FE8A5C” RetrievedFromCache=”true”>

But what if we change the query to be in the context of the T2 database (legacy CE) – but still access data from the T1 database?

USE [T2];
GO

SELECT    [member_no],
[provider_no],
[category_no]
FROM [T1].[dbo].[charge]
WHERE [charge_no] = 422;
 

Now we see the query used a legacy CE:

<StmtSimple StatementCompId=”1″ StatementEstRows=”1″ StatementId=”1″ StatementOptmLevel=”TRIVIAL” CardinalityEstimationModelVersion=”70″ StatementSubTreeCost=”0.0032831″ StatementText=”SELECT [member_no],[provider_no],[category_no] FROM [T1].[dbo].[charge] WHERE [charge_no]=@1″ StatementType=”SELECT” QueryHash=”0x274BD0D496403EEE” QueryPlanHash=”0x6B5F27FE55FE8A5C” RetrievedFromCache=”true”>

What if the cross-database query is executed from a new CE session context – but the destination is the legacy CE?

USE [T1];
GO

SELECT    [member_no],
[provider_no],
[category_no]
FROM [T2].[dbo].[charge]
WHERE [charge_no] = 422;
 

In this scenario, the query uses the new CE – based on the database session context – even though the destination database is set to compatibility level 110.

What about accessing data from two databases (rather than my previous example of just accessing data from one database)?  The following example results in a legacy CE plan:

USE [T2];
GO

SELECT    [member_no],
[provider_no],
[category_no]
FROM [T1].[dbo].[charge]
WHERE [charge_no] = 422
UNION
SELECT    [member_no],
[provider_no],
[category_no]
FROM [T2].[dbo].[charge]
WHERE [charge_no] = 422;
GO
 

And this query results in a new CE plan:

USE [T1];
GO

SELECT    [member_no],
[provider_no],
[category_no]
FROM [T1].[dbo].[charge]
WHERE [charge_no] = 422
UNION
SELECT    [member_no],
[provider_no],
[category_no]
FROM [T2].[dbo].[charge]
WHERE [charge_no] = 422;
GO
 

So – bottom line – using the new CE doesn’t mean just changing the database compatibility level.  Database session context also matters.