Scalar Functions Causing Plan Cache Bloat

Question

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:

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?

Answer 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.

I've written before about this:

• Function Plan Caching In SQL Server: Scalar Valued Functions

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