In yesterday’s post, we looked at we looked at simple scalar function plan caching.
Today, we’ll look at MSTVFs. If you’re not sure what that means, look at the title of the post real quick.
Yeah, up there.
On we go.
The function will do the same thing as before, just rewritten to be a MSVTF.
CREATE OR ALTER FUNCTION dbo.CommentsAreHorribleMulti(@Id INT)
RETURNS @Tally TABLE(Tally BIGINT)
INSERT @Tally ( Tally )
SELECT (SELECT SUM(Score) FROM dbo.Posts AS p WHERE p.OwnerUserId <= @Id)
(SELECT COUNT_BIG(*) FROM dbo.Comments AS c WHERE c.UserId <= @Id)
Now, where these differ immediately from SVFs (scalar valued functions), is that they don’t show up in the plan cache by name.
Note that these are both “statements”.
Also, unlike SVFs, they don’t show up in dm_exec_function_stats. This is documented behavior, but whatever.
And even though they’re called a “Proc” in dm_exec_cached_plans, they only show up in dm_exec_query_stats, not dm_exec_procedure_stats (which is why BlitzCache calls them a Statement).
Unlike SVFs, which don’t have a restriction on the function body using parallelism, all table variable modifications are forced to run serially (unless you’re sneaky).
That means both insert queries will be serialized, with the main difference being index access.
Like before, if we cache either plan, it will get reused. And just like before, the clustered index scan plan is significantly slower.
SELECT TOP (5) u.DisplayName,
(SELECT * FROM dbo.CommentsAreHorribleMulti(u.Id))
FROM dbo.Users AS u
Just like scalar functions, these can have different plans cached and reused, and may fall victim to parameter sniffing.
Again, this depends a lot on how the function is called and used. It’s just something to be aware of when tuning queries that call functions.
Execution times may vary greatly depending on… well…
Thanks for reading!