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)
2 thoughts on “MSTVF Fixed Cardinality Value in SQL Server 2014”
Hi – I am an Oracle DBA not a SQL Server DBA (yet), so please take it easy on me (smile)
Oracle collects stats explicitly via PL/SQL calls. In that call we can set the estimation% and other specifics for going-deep. We do this specifcally based on the DB and apps.
1) is there a 1-way-to-do-it method to collect stats for SQL Server
2) are any collected automatically
3) can you specify things like estimation% (ways to get around this estimation change)
4) can you save off stats, created new stats, find the new ones are bad, and reload the old stats?
5) can you query the stats’ age and completeness in SQL and then alert yourself that you need to gather new stats?
Thanks
Hi Earl,
Several good questions. Big topics, so I’ll recommend a few longer references.
First reference I would recommend is “Statistics Used by the Query Optimizer in Microsoft SQL Server 2008” for the full backstory on statistics (http://technet.microsoft.com/en-us/library/dd535534(v=sql.100).aspx). That paper covers the answers to most of your questions. Questions 1, 2, 4 and 5 are addressed in the paper.
Regarding question 3 – there isn’t a direct override (although there are many of us who would like that option). There are tricks for addressing issues and influencing CE, but with varying effectiveness. I talk about the main CE issues I commonly see in the following SQLperformance.com article (http://www.sqlperformance.com/2012/11/t-sql-queries/ten-common-threats-to-execution-plan-quality). If you have Pluralsight access, I go into more detail on them in the SQL Server: Troubleshooting Query Plan Quality Issues course (http://bit.ly/1ikr4mu).
Also – check out Kimberly Tripp’s statistics video (free MCM video) here -> http://bit.ly/1iXaOdn.
Hope this helps.
Comments are closed.