0

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?

  • 1
    Are you having real problems related to your plan cache, caused by this? – LowlyDBA Feb 28 at 17:26
  • @LowlyDBA yes, the comment by jean on this question: dba.stackexchange.com/questions/260669/… led me to asking this question about forced parameterization – DForck42 Feb 29 at 4:20
2

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: enter image description here

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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