0

Our plan cache is showing a large number of duplicate entries for a scalar function that is called as per the code below:

SELECT dbo.fnSomeFunction('12345678')

I have done some testing and found that it appears a new plan is compiled for each call, rather than a single plan with sniffed parameters similar to a stored procedure.

Create an equivalent stored proc and scalar function:

USE StackOverflow2010
GO

CREATE OR ALTER PROC dbo.sp_Test
(
    @id INT
)
AS

    SELECT  Body
    FROM    Posts
    WHERE   Id = @id
GO


CREATE OR ALTER FUNCTION dbo.sfn_Test
(
    @id INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @body NVARCHAR(MAX)

    SELECT  @body=Body
    FROM    Posts
    WHERE   Id = @id

    RETURN @body
END

clear the plan cache and call both with different parameters:

USE StackOverflow2010

DBCC FREEPROCCACHE

EXEC dbo.sp_test @id = 4
GO
EXEC dbo.sp_test @id = 5
GO
EXEC dbo.sp_test @id = 6
GO
SELECT dbo.sfn_Test(4)
GO
SELECT dbo.sfn_Test(5)
GO
SELECT dbo.sfn_Test(6)
GO

Run a query to view our plan cache:

SELECT  d.name,
        t.text AS TSQL_Text,
        s.creation_time, 
        s.execution_count,
        p.query_plan
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
        JOIN sys.databases d
            ON t.dbid = d.database_id
ORDER BY TSQL_Text;

The results:

enter image description here

We can see that SQL Server has reused the stored procedure plan but each scalar function call gets it's own plan.

Is there a way to reduce plan cache bloat as a result of this? The only option I can think of is to enable Optimize for Ad-Hoc Workloads?

As for why SQL Server behaves this way, I assume this is just yet another example of why not to use scalar functions?

1 Answer 1

1

eh

As much as I love to hate scalar UDFs, this is the result of each select being called with a literal value, which means each statement receives a different hash. The function body re-uses the plan for each of these calls.

NUTS

I've written before about this:

Forced parameterization doesn't fix this issue, but you may be able to fix the issue by sending parameterized calls to the function when it's called as it is in your example.

CREATE OR ALTER PROC dbo.sp_Test
(
    @id INT
)
AS
    SELECT dbo.sfn_Test(@id)
GO

EXEC dbo.sp_test @id = 4
GO
EXEC dbo.sp_test @id = 5
GO
EXEC dbo.sp_test @id = 6
GO 

NUTS

5
  • That makes sense - each call is a different hash. Am I right in saying the calls to the stored proc aren't saved in the plan cache then? Not sure why I am not seein the CREATE FUNCTION show in my cache query?
    – SE1986
    Oct 4, 2023 at 15:03
  • @SE1986 that's so wrong i'm not even sure how to respond to you. Oct 4, 2023 at 15:05
  • Each different "hash" eg SELECT<br />1 SELECT <br /><br />1, SELECT<br /><br /><br />1 (note spacing differences) will cause different compilations as they have a different hash. Isn't that what is happening with the scalar function call?
    – SE1986
    Oct 4, 2023 at 15:07
  • ugh, can't get spaces to show properly in comment formatting :(
    – SE1986
    Oct 4, 2023 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.