Allow TVFs (T-SQL and SQLCLR) to provide user-defined row estimates to query optimizer
Currently T-SQL multi-statement TVFs always have an estimated row count of 100, and SQLCLR TVFs always have an estimated row count of 1000. Sometimes these values are ok, but sometimes they are wildly off which can lead to non-optimal plans.
And while Interleaved Execution was introduced in SQL Server 2017 ( https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-interleaved-execution-for-multi-statement-table-valued-functions/ ) and greatly helps in providing accurate estimated row counts, it does not:
1) work in all scenarios for T-SQL multi-statement TVFs: "For the first version of interleaved execution, MSTVF referencing statements must be read-only and not part of a data modification operation. Also, the MSTVFs will not be eligible for interleaved execution if they are used on the inside of a CROSS APPLY."
2) seem likely that it will ever work for SQLCLR TVFs, especially when the rows to return are derived through an algorithm or external source instead of through a query.
Upvotes: 3<=-=Jun 9 2017 9:45AM=-=>
Thanks for the feedback.
It is more likely that we will follow and evolve the approach introduced with interleaved execution for multi-statement table valued functions (starting in SQL Server 2017). But we will keep this item open for future consideration.
Joe Sack, Principal PM, Microsoft<=-=Nov 28 2017 7:44AM=-=>
Hi Joe. Thanks for the reply. I have updated my request so that it fits more inline with the fact that interleaved execution exists but does not apply to all situations, and might never be able to apply to SQLCLR TVFs.<=-=Nov 28 2017 7:48AM=-=>
The following suggestion appears to be related:
Allow SQLCLR TVFs to provide information about sorting and distinctness to query optimizer ( https://connect.microsoft.com/SQLServer/feedback/details/3144175/ )
Solomon Rutzky commented
I originally submitted this suggestion via: