How to see the execution plan of a multi-statement table valued function in SQL Server?

Question

I have a view in SQL Server that calls a function. When I show the actual execution plan for querying the view, what happens inside the function is completely opaque though.

As a small example, I made a function called MyFunction:

CREATE OR ALTER FUNCTION MyFunction 
    (@lower int, @upper int)
RETURNS @ReturnTable TABLE 
                     (
                         [Id] int,
                         [Value] int
                     )
AS 
BEGIN
    DECLARE @SomeTable TABLE 
                       (
                           [Id] int,
                           [Value] int
                       )

    INSERT INTO @SomeTable
        SELECT EE.Id, M.[ID, Bolag1]
        FROM DataModel.EconomicEstate AS EE
        LEFT OUTER JOIN DataModel.Metadata AS M ON M.[ID, Kstn] = EE.Id
        LEFT OUTER JOIN DataModel.Metadata AS M2 ON M2.[Period] = M.[Period]

    INSERT INTO @ReturnTable
        SELECT [Id], [Value]
        FROM @SomeTable
        WHERE [Value] >= @lower AND [Value] <= @upper

    RETURN
END

And a view called MyView that calls MyFunction:

CREATE OR ALTER VIEW MyView
AS
    SELECT *
    FROM MyFunction(200000, 400000)
GO

Next, I query the view:

SELECT 
    [Id], [Value]
FROM 
    [Test].[dbo].[MyView]

But in the actual execution plan, in SQL Server Management Studio, this is all I see:

Is it possible to dig deeper into MyFunction to see the performance it uses for joining the tables together? Can I somehow analyze the performance of a multi-statement table valued function?

Answer 1

A tool such as SQL Sentry Plan Explorer can show more details than SSMS. It is free. (I do not work for SQL Sentry / SolarWinds, I just use their Plan Explorer a lot).

I used SQL Server 2017 Developer Edition for these tests and the latest Plan Explorer.

I have a table Numbers in my database with 1M rows with numbers from 1 to 1M. I used this test function, just to have something that takes some time to run:

CREATE OR ALTER FUNCTION MyFunction
    (@lower int, @upper int)
RETURNS @ReturnTable TABLE 
    ([Id] int,
    [Value] int)
AS
BEGIN
    DECLARE @SomeTable TABLE 
    ([Id] int,
    [Value] int)
    ;

    INSERT INTO @SomeTable
    SELECT N1.Number, N2.Number
    FROM
        dbo.Numbers AS N1
        CROSS JOIN dbo.Numbers AS N2
    WHERE
        N1.Number < 1000
        AND N2.Number < 100000
    ;

    INSERT INTO @ReturnTable
    SELECT [Id], [Value]
    FROM
        @SomeTable
        CROSS JOIN dbo.Numbers
    WHERE
        [Value] >= @lower AND [Value] <= @upper
        AND dbo.Numbers.Number < 100
    ;

    RETURN
END

And this query:

SELECT COUNT(*) FROM dbo.MyFunction(20, 50) AS T;

I used "Get Actual Plan With Live Query Profile" option:

It showed the plan only for the main query, but it determined what was inside the function and showed durations and reads / writes / rows stats for each of the queries inside the function. This is already very useful.

When I select a query inside the function it sadly does not show its plan:

But, it is showing it during the query execution and when I click the "replay" button it shows it again. We can pause the replay and examine the plan as usual: