Scalar Functions Causing Plan Cache Bloat

0 votes

1 answer

107 views

stored-procedures sql-server-2019 functions plan-cache recompile

                          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?
                        

Asked by SE1986 (2182 rep)

Oct 4, 2023, 01:52 PM
Last activity: Oct 4, 2023, 02:44 PM

Scalar Functions Causing Plan Cache Bloat

Related Questions