Would forced parametarization help?

Question

We have a process that creates a new sequence using dynamic sql. We then use dynamic sql to get the next value from the sequence. We have a lot of these dynamic sequences, but don't call any individual one a lot, but we call a lot of them.

In the plan cache it looks something like this:

(@new int OUTPUT)SELECT @new = Next Value for dbo.[UniqueSequenceName1]
(@new int OUTPUT)SELECT @new = Next Value for dbo.[UniqueSequenceName2]
--etc

These are eating up a lot of our plan cache, and tend to only be called once before they're pushed out. I have Optimize for Ad Hoc Workload turned on, but that hasn't changed the behavior. Would turning on forced parametarization help reduce the load on the plan cache? Is there another setting that might help?

Answer 1

This can be easily checked. Here's code which should imitate your scenario:

ALTER DATABASE [playground] SET PARAMETERIZATION FORCED WITH NO_WAIT
GO
CREATE SEQUENCE dbo.Seq1  
    START WITH 5  
    INCREMENT BY 5 ;  
GO  

CREATE SEQUENCE dbo.Seq2
    START WITH 5  
    INCREMENT BY 5 ;  
GO  

DBCC FREEPROCCACHE()
GO

SELECT NEXT VALUE FOR dbo.Seq1;  
GO
SELECT NEXT VALUE FOR dbo.Seq2;  
GO

Now if you check plan cache:

SELECT text, plan_handle, qs.execution_count FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) as qt

You can clearly see there are two distinct plans for two sequences:

This is what I expected. It works only for parameters and not for object names.