sqlsql-servert-sqlssmssql-execution-plan

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


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:

Actual Execution Plan in SQL Server

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?


Solution

  • 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:

    plan of the whole query

    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:

    no plan for a query inside the function

    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:

    actual plan 1 of a query inside the function

    actual plan 2 of a query inside the function