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)