1

I'm running on SQL Server 2019. Whenever I write a query of the form

SELECT * 
FROM [Multi-Statement-Table-Valued-Function]

the execution plan shows that it expected to get 100 rows from this process. Is this a consistent and documented pattern? Or is it just a quirk of my server?

I have a set of notes on cardinality estimation from SQL Server 2014 and I'm confident that the estimation methods haven't changed since 2014, but they make no mention of this magic number.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
J. Mini
  • 1,868
  • 1
  • 9
  • 38
  • You're on 2019 but what compat level are you in? See https://www.mssqltips.com/sqlservertip/5728/sql-server-multi-statement-table-value-function-mtvfs-performance-difference-between-versions/ for example. (Also, just rewrite your functions as inline.) – Aaron Bertrand Aug 30 '23 at 21:33
  • @AaronBertrand Whatever the default is. I'm certain that won't have been changed. – J. Mini Aug 30 '23 at 21:34
  • The "default" depends on whether you installed 2019 fresh or upgraded over time, and whether you created the database fresh or restored from some older source. – Aaron Bertrand Aug 30 '23 at 21:35
  • 1
    In that case you should be looking at actual execution plans rather than estimated anyway because it will be recompiled after the MSTVF table variable is populated – Martin Smith Aug 30 '23 at 21:35

1 Answers1

5

MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and “1” for earlier versions

Source

Later versions (compatibility level >= SQL Server 2017) have interleaved execution which is also discussed in that source.

Testing in 2022 the 100 estimate is still used as an initial estimate when getting an "estimated" plan - but the interleaved execution means that this plan will be replaced.

One other relevant passage from the above article is

How does interleaved execution work for consecutive executions?

Once an interleaved execution plan is cached, the plan with the revised estimates on the first execution is used for consecutive executions without re-instantiating interleaved execution.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I would have preferred official documentation to an official blog, but I can't do any better than you have. – J. Mini Aug 30 '23 at 21:43
  • It was definitely extensively trailed when the change was made - might be mentioned in the "new cardinality estimator" docs – Martin Smith Aug 30 '23 at 21:46
  • 1
    yep. "The default fixed cardinality estimate for multi-statement table-valued functions in the new CE is now 100 instead of 1" - in "Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator" white paper – Martin Smith Aug 30 '23 at 21:55