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

image

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

image

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)

Related Posts